AVERAGE function in Google Sheets - Dashboard Guide

Dashboard Mode - AVERAGE function

Goal

Find the average sales amount for each product and overall to understand typical sales performance.

Sample Data

Product	Sales Q1	Sales Q2	Sales Q3
Apples	100	120	130
Bananas	90	110	105
Cherries	80	95	100
Dates	70	85	90
Elderberries	60	75	80

Dashboard Components

• Average Sales per Product (Column E): Formula in E2: =AVERAGE(B2:D2) copied down to E6. This shows the average sales for each product across Q1 to Q3.
• Overall Average Sales (Cell B8): Formula: =AVERAGE(B2:D6). This calculates the average sales value across all products and quarters.
• Sales Data Table: Shows original sales data with the added average column.

Dashboard Layout

+-----------------------------+
| Sales Data Table            |
| +-------------------------+ |
| | Product | Q1 | Q2 | Q3 | Avg | |
| |---------|----|----|----|-----| |
| | Apples  |100 |120 |130 |116.7| |
| | Bananas | 90 |110 |105 |101.7| |
| | Cherries| 80 | 95 |100 | 91.7| |
| | Dates   | 70 | 85 | 90 | 81.7| |
| | Elderb. | 60 | 75 | 80 | 71.7| |
| +-------------------------+ |
+-----------------------------+
+-----------------------------+
| Overall Average Sales: 95.8  |
+-----------------------------+

Interactivity

Add a filter to select specific products. When a product is selected, the average sales per product and overall average update to reflect only the selected product(s).

Self Check

If you filter to show only "Bananas" and "Dates", what is the updated overall average sales value?

Answer: The average of sales for Bananas and Dates across Q1 to Q3 is ((90+110+105)+(70+85+90))/6 = (305 + 245)/6 = 550/6 = 91.7

Key Result

Dashboard shows average sales per product and overall average sales using AVERAGE function.