IF OR formula

J

JB_Scotland

Board Regular

Joined

Dec 21, 2008

Messages

147

• May 5, 2011

• #2

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

 

Upvote 0

A

audrey

Active Member

Joined

Jul 30, 2008

Messages

491

• May 5, 2011

• #3

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>

 

Upvote 0

J

JB_Scotland

Board Regular

Joined

Dec 21, 2008

Messages

147

• May 5, 2011

• #4

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

 

Upvote 0

A

audrey

Active Member

Joined

Jul 30, 2008

Messages

491

• May 5, 2011

• #5

it s "N/A" as a text

 

Upvote 0

J

JB_Scotland

Board Regular

Joined

Dec 21, 2008

Messages

147

• May 5, 2011

• #6

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

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

 

Upvote 0

A

audrey

Active Member

Joined

Jul 30, 2008

Messages

491

• May 5, 2011

• #7

you are right it works now. thanks!

 

Upvote 0

J

JB_Scotland

Board Regular

Joined

Dec 21, 2008

Messages

147

• May 5, 2011

• #8

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.

 

Upvote 0