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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### 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
2
Views
187
Replies
1
Views
183
Replies
2
Views
343
Replies
1
Views
374
Replies
11
Views
529

1,186,372
Messages
5,957,476
Members
438,307
Latest member
bigmike1720

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