#### simone

##### New Member
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?

### 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
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

Replies
1
Views
129
Replies
11
Views
223
Replies
4
Views
246
Replies
0
Views
459
Replies
3
Views
227

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.

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.

### Which adblocker are you using?

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

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