nested AND and OR statements - help
All,
Take cells: A1, B1, C1 where cells B1 and C1 may either a number (such as 0, 20 or 99) or blank (as in a blank cell or "")
I would like to accomplish the following:
1) Write the string or text "Both" in A1 IF and ONLY IF the content of both B1 and C1 are NEITHER of the following: negative, 0, blank ("").
If the above fails (meaning either B1 or C1 is either < 0 or blank), verify if B1 >0 and not blank ("") - if so, write "Giver" in A1
If B1 fails the test (which means, C1 is positive and not blank), write "Taker" in A1
2) If both B1 and C1 are found to be EITHER negative (<0) or blank space or anything else other than a non-zero, non-negative number, write "trade-in" in A1
The point here to make sure that every possible case is tested and appropriate error produce to ensure that user do not enter something funky in either B1 or C1.
Does this make sense? Can anyone help? I don't care what functions are used as long as it accomplished the task. Much thanks! :)
Either of the following
Either of the following should work:
=CHOOSE((B1<>"")*(B1>0)+(C1<>"")*(C1>0)*2+1,"trade-in","Giver","Taker","Both")
=IF(AND(B1<>"",B1>0),IF(AND(C1<>"",C1>0),"Both","Giver"),IF(AND(C1<>"",C1>0),"Taker","trade-in"))
Yamor, The first one was a
Yamor,
The first one was a bit difficult to understand but both work brilliantly.
Thanks!
Hi, The first formula just
Hi,
The first formula just takes advantage of the fact that when forced into a calculation then TRUE=1 and FALSE=0.