Excel help please

kidloco · 18 Dec 2008 at 13:15

Hi

I'm a complete excel novice and could do with a little help if you can. as you can see pic its a really simple document.

The formula im after is to go in the 'Variance' field, I want it so that if there is no amount in the 'Actual' field, it displays 0. So in the pic above, the 1st 2 amounts are correct, bu tthe 3rd down should show £0 not -£2000

The formula in there at the moment is =SUM(E4)-D4

Royal Fleet · 18 Dec 2008 at 13:20

You could do it with an IF Statement

eg.. =IF(d4=0,0,(E4-D4))

What this does is say, if d4 is zero, put in a zero, if it isn't, do the formula.

Hope that helps

Pho · 18 Dec 2008 at 13:22

How about:
=IF(D2>0, C2-D2, 0)

Where C2 = amount field, D2 = actual field

edit: beat

kidloco · 18 Dec 2008 at 13:33

Cheers guys, 1st one didnt work for some reason, but second did.

=IF(E4>0, D4-E4, 0)

works a treat. thanks again

Royal Fleet · 18 Dec 2008 at 13:44

kidloco said:
Cheers guys, 1st one didnt work for some reason, but second did.

=IF(E4>0, D4-E4, 0)

works a treat. thanks again

Odd as I did test it. Maybe the Amount cell wasn't completely blank (e.g. you may have used space to clear the contents, and the cell then becomes a string)

Not to worry as the other solution did the job fine anyway.