Formula's

[Bensum]

Please help,

I need to know what formula's to enter to acheive the following results:-

1. I have 4 cells:-

Actual Expense
Budget
£ of budget remaining
% of budget remaining

Currently if my actual expense is greater than my budget, the '£ of budget
remaining' cell returns a minus figure. Rather than the cell showing a minus
figure I would prefer it to just return a zero balance in order that my end
of year figures are correct.

2. Also, another query I have is that to get my '% of budget remaining'
figure I do the following '£ of budget remaining' divide 'budget' and this
returns the answer however, if the budget is zero and therefore the
calculation is dividing by zero I get this messgae '#DIV/0!'. How can I tell
excel to just return a zero percentage?

Many thanks

 

[Mike H]

Hi,

To return zero instead of minus values use

=MAX(0,B2-A2)
Which assumes actual in A2 and budget in b2

and tp eliminate DIV0 use

=IF(B2<>0,C2/B2,"")

Mike