Now that you understand the concept of limits and green/yellow/red, we're going to do some setup along those lines to support a speedometer chart. We'll build the chart in the next chapter. A speedometer chart is surprisingly hard to create in Excel, so we're doing the pre-work here. This setup sets the green/yellow/red limits for that chart.
Select the Cash worksheet.
In cell D6 type
Cash.In cell E5 type
Actual.In cell F5 type
Meter use Only:
In cell E6, type the equal sign (
=), the negative sign (-), and select cell B5. This is the cash amount from the pivot table. You should see a formula that looks like this:=-GETPIVOTDATA("Amount",$A$4,"Account Category Number","Cash")In cell D9 type
Meter Level.In cell E9 type
Difference.In cells D10 through D12 type
Red,Yellow, andGreenrespectively.In cell E10 type
1000000.In cell E11 type
3000000.In cell E12 type
4000000. These are the red, yellow, and green values.In cell F10 type
=E10.In cell...