62 Excel
Using Calculated Values
You decide to reward service station managers whose daily gasoline sales
were higher than average. How would you determine which service stations
qualifi ed? You could calculate average gasoline sales and enter this number
explicitly into a fi lter (either an AutoFilter or an Advanced Filter). One prob-
lem with this approach however, is that every time you update your service
station data, you have to recalculate this number and rewrite the query.
However, Excel’s AutoFilter allows you to include this information in
your query automatically.To select stations with higher-than-average gas sales:1 Select the cell range A11:D11 again.
2 Click the Filter button from the Sort & Filter group on the Data tab to
display the AutoFilter drop-down arrows.
3 Click the Gas drop-down list arrow, click Number Filters, and then
click Above Average.
As shown in Figure 2-21, the data list is fi ltered again, showing only
the data from Service Stations 0, 3, 5, 7, and 8. Those are fi ve service
stations whose daily gas sales are higher than the average from all
stations in the data list.Figure 2-21
Filtered data
for higher-
than-average
gas sales4 Click the Filter button again from the Sort & Filter group on the Data
tab to turn off the fi lter of the service station data.