PERCENTAGE MADNESS

simone

New Member
Joined
Feb 2, 2004
Messages
1
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? :eek:
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,896
Messages
5,766,992
Members
425,392
Latest member
Booknerd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top