1. ## If statement (97-02)

Hi,

Cells B and C may or maynot have values in them. In cell D I have the following formula. I have pasted the formula down 150 rows. In each row of "D" the last value appears. i.e. if the value of "D5" is 50, then from "D6" to "D150" has the value of 50, but I haven't put anything in "B or C6" yet. If B or C doesn't have a value, I would like for "D6" to show nothing.

I hope this is clear and someone can help me.

Roberta

=D2-B3+C3

2. ## Re: If statement (97-02)

=IF(B3*C3=0,"",D2-B3+C3)

3. ## Re: If statement (97-02)

Think I understand you.

Before dragging your formula down the column, put dollar signs (\$) before the references you don't want to change.

Set your formatting to show zeros as blanks.

Cheers,
Jim

4. ## Re: If statement (97-02)

I think that the formula that you want is:

<pre>=IF(AND(B3=0,C3=0),"",D2-B3+C3)
</pre>

5. ## Re: If statement (97-02)

Must be too close to holiday. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> She wants to show the result if either B or C is not empty.

6. ## Re: If statement (97-02)

Thanks for the responses. I will try to clarify it more.

B=Withdrawls
C=Deposits
D=Balance (=D2-B3+C3)

B has a value, but C doesn't, so how would I write the formula to evaluate if one of the two doesn't have a value and return nothing("")?

For instance, Monday my balance was 20 (d2)yesterday I made a deposit of 180(c3) making my new balance 200(d3). I didn't have any withdrawls, so B3 is null..no problem, however as of right now, every row between d4..d150 will show the balance of 200, I don't want that, I want d4..d150 to be null and only change when I put a value in either b4 or c4.

I think this is more clear and I apologize for the confusion.

I believe the suggested formula is using the "*" as an "and" but should really be an "or". Simply said ...evaluate b and c to determine if a value is there if both are null...then d would be null.

7. ## Re: If statement (97-02)

See the formula in my reply.

8. ## Re: If statement (97-02)

Thanks to all....Legare your solution worked perfectly.

Have a nice and safe 4th of July.

Roberta

9. ## Re: If statement (97-02)

I'm pretty sure my formula works just as well as Legare's...

10. ## Re: If statement (97-02)

For that matter ...

=IF(B3*C3,D2-B3+C3,"")

<img src=/S/grin.gif border=0 alt=grin width=15 height=15>

11. ## Re: If statement (97-02)

Jan: Your formula will not display a result if either of the cells is blank, she wants to not display a value if BOTH cells are blank. My formula will display the calculation if either of the cells contains a value, yours will only display the calculation if both contain a value.

12. ## Re: If statement (97-02)

=IF(COUNTBLANK(B3:C3),"",SUM(D2,-B3,C3))

or

=IF(COUNTBLANK(B3:C3)>1,"",SUM(D2,-B3,C3))

if that's what is wanted.

13. ## Re: If statement (97-02)

I might as well have a go. How about

=IF(B3+C3,D2-B3+C3,"") <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

14. ## Re: If statement (97-02)

That should work as long as one of the cells is not equal to the negative of the other, and that should be a safe assumption the way this is being used.

15. ## Re: If statement (97-02)

Legare, in the specific context of the original post, that would be the case when a withdrawal and a deposit are identical, which might happen with certain kinds of transaction, but the effect would not cause the running balance to be in error.

(I also interpreted the OP question the way Jan Karel did, but in context I was wrong.)

Page 1 of 2 12 Last

#### Posting Permissions

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