Excel formula question

887 Views | 13 Replies | Last: 5 yr ago by OnlyForNow
OnlyForNow
How long do you want to ignore this user?
AG
Trying to make a cell (H79) return a value of "X" (literally just an X), if g61:g67, g70:g76, g79:g85, g88:g99, g102:g106, is three (FAC, FACW, and/or OBL) of these 5 options (OBL, FACW, FAC, FACU, UPL).

Would the formula be, =IF($G61:G67:$G70:G76:$G79:G85:$G88:G99:$G102:G106="FAC""FACW""OBL","X","")

It's not working, and I don't know where my error is.
Slagathor
How long do you want to ignore this user?
AG
I could only get it to work for each group of consecutive cells and not all of them together. I also used the OR function to test for the three outcomes. Here is my formula for the first group of cells, G61:G67

=IF(OR($G$61:$G$67="FAC",$G$61:$G$67="FACW",$G$61:$G$67="OBL"),"X","")

Repeat for $G$70:$G$76, $G$79:$G$85, $G$88:$G$99, $G$102:$G$106 and then have another IF statement at the end that checks if they all output an X.

Another option would be to copy/paste all the cells you want to test into a new column to make them consecutive and test them all at once that way.
OnlyForNow
How long do you want to ignore this user?
AG
I am still getting a #Value error even with your formula.

It probably doesn't help that the OBL is being populated via a VLookup table.
MonkeyKnifeFighter
How long do you want to ignore this user?
bbattbq01
How long do you want to ignore this user?
AG
Don't understand your setup precisely...

What does g61:g67 represent? A range of 7 individual cells or 1 merged cell?
OnlyForNow
How long do you want to ignore this user?
AG
That is a range of cells in a single column to multiple different rows. The data lines for each row will return the value of FAC, OPL, if ACW or other within that call
CubbieAggie
How long do you want to ignore this user?
AG
You could check using a count of each range. The formulas below check if the count of cells with one of those three values matches the count of non-blank and blank cells in each of the ranges and return an "X". The last one then checks if all five values are Xs using IF and AND.


cell1=IF(COUNTIF(g61:g67,"FAC")+COUNTIF(g61:g67,"FACW")+COUNTIF(g61:g67,"OBL")=COUNTA(g61:g67)+COUNTBLANK(g61:g67),"X","")

cell2=IF(COUNTIF(g70:g76,"FAC")+COUNTIF(g70:g76,"FACW")+COUNTIF(g70:g76,"OBL")=COUNTA(g70:g76)+COUNTBLANK(g70:g76),"X","")

cell3=IF(COUNTIF(g79:g85,"FAC")+COUNTIF(g79:g85,"FACW")+COUNTIF(g79:g85,"OBL")=COUNTA(g79:g85)+COUNTBLANK(g79:g85),"X","")

cell4=IF(COUNTIF(g88:g99,"FAC")+COUNTIF(g88:g99,"FACW")+COUNTIF(g88:g99,"OBL")=COUNTA(g88:g99)+COUNTBLANK(g88:g99),"X","")

cell5=IF(COUNTIF(g102:g106,"FAC")+COUNTIF(g102:g106,"FACW")+COUNTIF(g102:g106,"OBL")=COUNTA(g102:g106)+COUNTBLANK(g102:g106),"X","")

resultCell =IF(AND(cell1="X", cell2="X", cell3="X", cell4="X", cell5="X"),"X","")
OnlyForNow
How long do you want to ignore this user?
AG
Anyway to combine this?
AgLiving06
How long do you want to ignore this user?
What's in between the ranges that's causing you to need to break it up this way?
OnlyForNow
How long do you want to ignore this user?
AG
Other lines of text, I just don't want it to get confused.

But I figured out my issue, I needed to press crtl,shift, enter when finishing the formula
GoAgs92
How long do you want to ignore this user?
AG
so if any of the first 3 text strings (FAC, FACW, and/or OBL) show up at least 3 times within all of those ranges combined, then put an X?
OnlyForNow
How long do you want to ignore this user?
AG
No, if only that is what is there it should display an X. My issue was with the way I called out the range search
Esteban du Plantier
How long do you want to ignore this user?
AG
Do you need it to be a formula?

Because I think that would be much easier to do in VBA.

I'm in bed on a phone, otherwise I would try the code out. I'll take a look at it in the morning from the desktop.
NoHo Hank
How long do you want to ignore this user?
AG
Instead of If, can't you use IF(AND(OR(g1:g6="FAC",g1:g6="FACL",g1:g6="OBL),OR(h1....) to build out your function? Then you have an OR function to determine FAC, FACL, and OBL for each range nested inside the AND function, which would compile all the ranges. Maybe I'm missing your ultimate goal, but that would ultimately result in the X if any 1 cell within each of those ranges came back. So, if 2 of the 3 ranges had FAC but the third didn't have it, it'd come back negative. But if all 3 ranges each had at least one cell with one of them, you'd get an X.
OnlyForNow
How long do you want to ignore this user?
AG
I figured it out already, but to answer your question.

The goal is to return an "X" in the other cell if ONLY FAC, FACW, or OBL are in that range. Anything else and the X should not show up.

It's a "rapid test" If all things are X, Y, and/or Z then it's a positive. But if there is even one A or B then it fails.
Refresh
Page 1 of 1
 
×
subscribe Verify your student status
See Subscription Benefits
Trial only available to users who have never subscribed or participated in a previous trial.