Selected Answer
JoyhillDad
Here's a way to have the chart change colour as thresholds are passed (you'll need to do something similar on your own file).
In the attached file, there's some imaginary data in A2:E6 - actuals, budgets and % thresholds (which you can change). If you change B3 (Gas Actual) to 450 say, the column will grow and turn red since the 90% threshold (E2) has been exceeded.
What's behind this is "helper" columns D and E, There's a formula which gives a value when a test is met or nothing if it's not (and the chart has a data seres with a colur which appears if the test is met or doesn't if not). In E3 for example, the formula is:
=IF($B3/$C3>=E$2,$B3,"")
so if the actual in row 3 divided by the budget (in C3) exceeds the percentage threshold in E2, the actual value is the result. Otherwise it's nothing, "". Dollar signs fix columns and/or rows when the formula is copied (as you know hopefully).
Do to create this effect in that file, first delete the chart. Then select A3:B6 (press Ctrl) and D3:E6 then go file menu/ Insert then ribbon Recommended charts/ Clustered Column, Ok.
You'll get a chart with Electricity showing three columns. Click on the right (Series 3), right click then pick Fill and choose red (from Standard Colours). For the middle (series 2), do the same but pick an orange and for Series 3 pick a green but also right click and choose Add Data Labels... this will display the Actual value (whichever colour the column appears).
You now have some different coloured bars of the same height. Right click on one again and choose Format Data Series.... Under Series Options, change Series Overlap from -27% (probably) to 100%. This makes them overlap so you only see the colour for the rightmost figure.
Now if you vary the Actual figues in column A to above or below the thresholds, the columns will change size and colour to suit.
Hope this makes sense and you can use it to create a working file.
