# Thread: Long formula help (Excel xp)

1. ## Long formula help (Excel xp)

I have 2 column B and E. The possible values in these 2 columns can be numerals or a ^ or a #. I need to make a formula that will give me the difference between COL B and COL E

B E
7 7 =IF(B33-E33=0,"0",B33-E33) I'm doing it like this because i supress zeros using tools, options (yes, i have to)

How do i make one formula that will take the following into consideration

B E
# 7 # minus 7 returns a #VALUE
# # #VALUE error
7 # #VALUE
^ 7
# ^

Anyway, whenever these symbols occur my formula returns #VALUE. I want it to return a zero so i can make a chart using the difference column. I don't know how to nest the whole thing. Thank you for the help.

2. ## Re: Long formula help (Excel xp)

Try:

<pre>=IF(ISERROR(B33-E33),"0",IF((B33-E33)=0,"0",B33-E33))
</pre>

3. ## Re: Long formula help (Excel xp)

One of:

=IF(ISNUMBER(B33)*ISNUMBER(E33),B33-E33,0)

=IF(ISNUMBER(B33)*ISNUMBER(E33),B33-E33,#N/A)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•