IF OR formula

[audrey]

Hello,

I am trying to make a simple IF OR formula, but for some reason it doesnt seem to work....

=IF(OR(N60="N/A", M60="N/A", N60-M60<0,"",N60-M60)


so basically in those 3 cases I want the formula to leave a blank, if non of those cases are realized I want the formula to perfom a basic substraction.

I think I can also use IF(ISNUMBER formula??? If n60 or M60 is not an actual number leave blank or perfom a basic substraction.....


Thanks!

 

[JB_Scotland]

=if(or(isna(n60-m60),n60-m60<0),"",n60-m60)

 

[audrey]

hello JB Scotland I still get <table border="0" cellpadding="0" cellspacing="0" width="107"><col width="107"><tr height="20"> <td class="xl99" style="height: 15pt; width: 80pt;" width="107" height="20">#VALUE!</td> </tr></table>

 

[JB_Scotland]

Are your N/A values "N/A" as text or the Excel #N/A that is returned from a function?

 

[audrey]

it s "N/A" as a text

 

[JB_Scotland]

=IF(ISNUMBER(M60-N60),IF(M60-N60<0,"",M60-N60),"")

Should work for both "N/A" text and #N/A returned from a function.

 

[audrey]

you are right it works now. thanks!

 

[JB_Scotland]

Great.

The problem with your original formula was that part of your OR function the N60-M60<0 was calculating to #VALUE when it encountered a TEXT value in either N60 or M60. If you have cells that may be text or number you need to check if they are numbers with the ISNUMBER() function before using them in a mathematical function otherwise you will get the error.