# Thread: Long formula needs help (excel xp)

1. ## Long formula needs help (excel xp)

I have 2 cells S70 and V70. The possible values are numerals or a pound sign (#). I have zeros turned off so if S70-V70=0, i need it to put in a zero. Also, if s70 or v70 has a pound # sign i need it to put in a double hyphen --.

=IF(OR(V70="#",S70="#"),"--",S70-V70)

=IF(S70-V70=0,"0",S70-V70)

How do i put these 2 formulas together? is it possible. thank you

2. ## Re: Long formula needs help (excel xp)

<pre>=IF(OR(V70="#",S70="#"),"--",IF(S70-V70=0,"0",S70-V70))
</pre>

3. ## Re: Long formula needs help (excel xp)

Sure. The best way to build and test long formulas is build the pieces and then cut and paste the components into the "monster" formua.

If you want text "0" only when S70-V70=0 (which will return negative values for S70-V70):

=IF(OR(V70="#",S70="#"),"--",IF(S70-V70,"0",S70-V70))

If you want text "0" when S70-V70<=0:

=IF(OR(V70="#",S70="#"),"--",IF((S70-V70)<=0,"0",S70-V70))

If you want a zero instead of text "0" when S70-V70=0 (which will return negative values for S70-V70):

=IF(OR(V70="#",S70="#"),"--",S70-V70)

If you want a zero instead of text "0" when S70-V70<=0:

=IF(OR(V70="#",S70="#"),"--",MAX(0,S70-V70)

#### Posting Permissions

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