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
, andGreen
respectively.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...