Arithmetic operators in formulas in Excel - Dashboard Guide

Dashboard Mode - Arithmetic operators in formulas

Goal

Calculate total cost, average cost, and cost difference using basic arithmetic operators in Excel formulas.

Sample Data

Item	Quantity	Unit Price
Apples	10	0.5
Bananas	5	0.3
Cherries	20	0.2
Dates	7	1.0
Elderberries	3	2.0

Dashboard Components

• Total Cost per Item: Calculates cost by multiplying Quantity and Unit Price.
  Formula example in cell D2: =B2*C2
  Output for Apples: 5.0
• Grand Total Cost: Sum of all total costs.
  Formula in D7: =SUM(D2:D6)
  Output: 23.5
• Average Unit Price: Average of unit prices.
  Formula in C7: =AVERAGE(C2:C6)
  Output: 0.6
• Price Difference: Difference between highest and lowest unit price.
  Formula in C8: =MAX(C2:C6)-MIN(C2:C6)
  Output: 1.8

Dashboard Layout

+----------------------+---------------------+
| Total Cost per Item   | Grand Total Cost    |
| (Table with formula)  | (Single cell sum)   |
+----------------------+---------------------+
| Average Unit Price    | Price Difference    |
| (Single cell avg)     | (Single cell diff)  |
+----------------------+---------------------+

Interactivity

Adding a filter for Item name will update the Total Cost per Item table and the Grand Total Cost, Average Unit Price, and Price Difference calculations to reflect only the selected items.

Self Check

If you filter to show only "Apples" and "Bananas", what is the new Grand Total Cost?

Answer: Apples total cost = 10 * 0.5 = 5.0, Bananas total cost = 5 * 0.3 = 1.5, so Grand Total Cost = 5.0 + 1.5 = 6.5

Key Result

Dashboard showing total cost per item, grand total, average unit price, and price difference using arithmetic operators.