Adding up a month of sales
A small school-supply shop wants to know how much it earned last month and which products brought in the most money.
Here is the data. Column D uses a formula to calculate each product's revenue.
| Row | Product (A) | Units sold (B) | Price (C) | Revenue (D) | Formula in D |
|---|---|---|---|---|---|
| 2 | Notebooks | 120 | $3.50 | $420.00 | =B2*C2 |
| 3 | Pens (10-pack) | 85 | $6.00 | $510.00 | =B3*C3 |
| 4 | Backpacks | 14 | $34.00 | $476.00 | =B4*C4 |
| 5 | Calculators | 22 | $18.00 | $396.00 | =B5*C5 |
| 6 | Total | 241 | $1,802.00 | =SUM(D2:D5) |
The formula in D2 was written once and copied down. Each copy adjusted itself to its own row. The total units in B6 use =SUM(B2:B5).
Asking more questions of the data
| Question | Formula | Result |
|---|---|---|
| Which product earned the most? | =MAX(D2:D5) | $510.00 (pens) |
| What did a product earn on average? | =AVERAGE(D2:D5) | $450.50 |
| Which product earned the least? | =MIN(D2:D5) | $396.00 (calculators) |
Labelling with IF
To label each product as above or below average, enter this in E2 and copy it down to E5:
=IF(D2>AVERAGE($D$2:$D$5),"Above average","Below average")
The dollar signs keep the average range fixed while D2 changes to D3, D4 and D5. Pens and backpacks come out above average; notebooks and calculators come out below.
Sorting to see the ranking
Select the whole table (A1:D5), choose Sort from the Data menu and sort by Revenue from largest to smallest. The order becomes pens, backpacks, notebooks, then calculators. Always select the whole table before sorting so each row stays together.
If the price of pens changes next month, only cell C3 needs updating. The revenue, total, average and labels all recalculate on their own.