QUESTION: I have a formula that is not working because it keeps giving me a #VALUE! message. The formula is below
=IF(AND(ISERROR(+AB279<1),"Y","N"),OR(T279>1,"N","Y"))
If at least one of the cells in AB279 is #N/A or a number greater than 1 in T279 then I want the answer to be N if not then I want the answer to be Y.
ANSWER:
=IF(OR(ISERROR(AB279),ISERROR(T279)),"N",IF(OR(AB279>1,T279>1),"N","Y"))
worked for me.
You need to eliminate the error possibilities before trying to test >1
You haven't accounted for the entry of text values in the cells - but that may not be an issue.
---------- FOLLOW-UP ----------
QUESTION: I was able to get rid of the value error but the formula didn't work.
I changed your formula to =IF(OR(ISERROR(AB279),ISERROR(T279)),"Y",IF(OR(AB279>1,T279>0),"N","Y")) but it still didn't work for me. My spreadsheet column AB279 is #N/A and column T2 is 1 so my answer should have been N but it is Y.
If column AB is #N/A and column T is greater than 0 I need for my answer to be N.
it is obvious I don't understand what your rules are. I have attached a picture showing that the formula I gave you works exactly as I designed it to work. If there is a #N/A or a value >1 in any of the two cells T279 and AB279, then display a "N" else display a "Y". As you can see in the picture, my formula performs for that specification.
What you wanted was unclear to me. But the above is what I understood you to want. I at least showed you how to avoid the conflict with #N/A in a cell.
the formula you show now is going to show "Y" because your first test is if AB279 has a #N/A value or T279 has a #N/A value. Your formula says if that is true, then show a "Y". You say AB279 has a #N/A so the formula does show a "Y". So it is doing exactly what it is designed to do. If that isn't what you want, then you need to state explicitely what your rules are.
Try setting up a table T279 AB279 Results Expected
>1 any "N" any >1 "N" #N/A any "N" any #N/A "N" anything else "Y"
where the cell will contain either a number or #N/A
that is what my original formula was set up for but apparently those are not your rules. So what are your rules.
Advertisement