PERCENTAGE MADNESS

[simone]

I am tallying up end numbers in a budget. How do I get Excel to show the proper percentage spent if someone has spent over budget?


For example,

There was no money forecasted for a purchase, so this has been set to $1 for budget calculations. There was actually money spent for that category- i.e $5597.50 =thus the Year to date spending percentage shows as -559650.00%. This is not helpful for future predictions since it is such a large percentage. Can anyone help?

 

[tactps]

I have had similar issues, and used the following formula

Assume that A1 is the actual ($5000) and B1 is the budget ($1)

=If(A1/B1>2,1,A1/B1-1)

Then if the percentage is greater than 200%, it will put in 100% increase. If it is less than 200%, it will calculate the increase.

You can adjust depending on what you want (ie instead of ",1," try "Too big").

Hope that this helps