Excel for business: core formulas
Excel is the world's most used analysis tool, and the first thing data interviews ask about. Here's a slice of Nakhla's sales. Every cell has an address: column (a letter) and row (a number), such as F5:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Month | Region | Product | Quantity | Sales |
| 2 | 1 | Jan | Riyadh | Sukkari dates | 20 | 1,200 |
| 3 | 2 | Jan | Jeddah | Arabic coffee | 10 | 450 |
| 4 | 3 | Feb | Riyadh | Arabic coffee | 15 | 675 |
| 5 | 4 | Feb | Dammam | Sukkari dates | 12 | 720 |
| 6 | 5 | Mar | Jeddah | Sukkari dates | 25 | 1,500 |
| 7 | 6 | Mar | Riyadh | Cardamom | 8 | 720 |
| 8 | 7 | Mar | Dammam | Arabic coffee | 18 | 810 |
| 9 | 8 | Mar | Jeddah | Cardamom | 5 | 450 |
References: we want the VAT on each sale, and the 15% rate sits in cell H1. If you write =F2*H1 and copy it down, Excel shifts both references: it becomes =F3*H2, and H2 is empty, so the result is zero! Shifting like this is a relative reference, which is what we want for F. The VAT cell we lock with dollar signs: =F2*$H$1. That's an absolute reference and it never moves, however far you copy.
| Function | What it does | Example |
|---|---|---|
| SUM · AVERAGE | Sum or average of a range. | =SUM(F2:F9) |
| COUNT · MAX · MIN | Count of numbers, largest, smallest. | =MAX(F2:F9) |
| SUMIFS | Sum of rows meeting one or more conditions. | =SUMIFS(F2:F9, C2:C9, "الرياض") |
| COUNTIFS | Count of rows meeting the conditions. | =COUNTIFS(B2:B9, "مارس") |
| AVERAGEIFS | Average of rows meeting the conditions. | =AVERAGEIFS(E2:E9, D2:D9, "هيل") |
| IF | A value depending on a condition. | =IF(F2>1000, "كبير", "عادي") |
Try it in the lab: choose the function and condition, and see the formula, the result and which rows were counted:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order | Month | Region | Product | Quantity | Sales |
| 2 | 1 | Jan | Riyadh | Sukkari dates | 20 | 1,200 |
| 3 | 2 | Jan | Jeddah | Arabic coffee | 10 | 450 |
| 4 | 3 | Feb | Riyadh | Arabic coffee | 15 | 675 |
| 5 | 4 | Feb | Dammam | Sukkari dates | 12 | 720 |
| 6 | 5 | Mar | Jeddah | Sukkari dates | 25 | 1,500 |
| 7 | 6 | Mar | Riyadh | Cardamom | 8 | 720 |
| 8 | 7 | Mar | Dammam | Arabic coffee | 18 | 810 |
| 9 | 8 | Mar | Jeddah | Cardamom | 5 | 450 |
Rows meeting the condition are highlighted
; instead of a comma, so if a formula errors, try switching it.