Percent Help

[trent92]

I have a spending plan that Starts in Oct 1 and end Sept 30. I used a Authority availability on a quarterly basis, however they made not use all Funding (but, they could go over and request more money for that qtr - bad).

exp: Row (a) available funds
exp: Row (b) they spent
exp: Row (d) money that’s available.

My question - is there a way to show in a new column a percent of status per quarter? 1st Qtr 25%, 2nd Qtr 50%, 3rd Qtr 75%, 4th Qtr 100% of Spending plans funds but, gets a RED highlight if over that Qtr. Just need to know if a "District" is staying within budget (by the way I have 94 Districts). Also, can a District stand out (be flag)?

 

[Amy @ German Pearls]

Are you looking for help creating the formula or making the cells red if they exceed budget?

I can't tell the cell numbers but a percent formula would look something like this:
=(cell_that_contains_total_spend)/(cell_that_contains_budget)

You can then use conditional formatting to highlight the ones that are exceeding their budget. For example, for Q2, if the cell is >0.50 than highlight the cell red. This can be done in the Home Menu > Styles > Conditional Formatting. The same idea would work for the other quarters but you would use 0.75 and 1.0 as the conditions.

 

[trent92]

Yes, on the right path - So if my Quarterly budget is $8,000 and spent$2,000 should be 25% (good), but, if my budget is $8,000 and I spent $13,000 should be163% (bad); then I should be able to use conditional formatting that highlights the percent in red. I think I got it. Thank you Amy