Last Edited By **Krjb Donovan** **Last Updated:** Mar 05, 2014 09:15 PM GMT

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

©2021 eLuminary LLC. All rights reserved.