Arithmetic operators in Google Sheets - Dashboard Guide

Dashboard Mode - Arithmetic operators

Dashboard Goal

Understand how to use basic arithmetic operators in Google Sheets to calculate totals, differences, products, and averages from sales data.

Sample Data

Product	Price	Quantity Sold
Apples	2	30
Bananas	1	50
Cherries	3	20
Dates	4	15
Elderberries	5	10

Dashboard Components

• Total Revenue per Product (Column D): Formula: =B2*C2 (copy down for each row). Calculates revenue by multiplying price by quantity sold.
• Total Revenue (KPI Card): Formula: =SUM(D2:D6). Adds all product revenues to show total sales revenue.
• Average Price (KPI Card): Formula: =AVERAGE(B2:B6). Calculates average price of all products.
• Difference in Quantity Sold (KPI Card): Formula: =MAX(C2:C6)-MIN(C2:C6). Shows the difference between highest and lowest quantity sold.
• Product with Highest Revenue (KPI Card): Formula: =INDEX(A2:A6,MATCH(MAX(D2:D6),D2:D6,0)). Finds product name with highest revenue.

Dashboard Layout

+----------------------+----------------------+----------------------+
| Total Revenue        | Average Price        | Quantity Sold Diff   |
| (KPI Card)          | (KPI Card)           | (KPI Card)           |
+----------------------+----------------------+----------------------+
| Product Revenue Table (Product, Price, Quantity, Revenue)           |
| (Table with formulas)                                             |
+-------------------------------------------------------------------+
| Product with Highest Revenue (KPI Card)                           |
+-------------------------------------------------------------------+

Interactivity

Add a filter for Product name to select one or multiple products. When a product filter is applied:

• The Product Revenue Table updates to show only selected products.
• Total Revenue, Average Price, and Quantity Sold Difference cards recalculate based on filtered data.
• Product with Highest Revenue updates to show the top product from the filtered list.

Self Check

If you add a filter to show only "Bananas" and "Dates", which components update and what are their new values?

• Product Revenue Table: Shows only Bananas and Dates rows with revenue calculated.
• Total Revenue: SUM of Bananas and Dates revenue = (1*50)+(4*15) = 50 + 60 = 110.
• Average Price: Average of prices 1 and 4 = (1+4)/2 = 2.5.
• Quantity Sold Difference: MAX(50,15)-MIN(50,15) = 50 - 15 = 35.
• Product with Highest Revenue: Dates (revenue 60) vs Bananas (revenue 50), so Dates.

Key Result

Dashboard showing sales revenue calculations using arithmetic operators in Google Sheets.

Practice

1. Which arithmetic operator in Google Sheets is used to multiply two numbers?

easy

A. +

B. *

C. /

D. ^

Solution

1. Step 1: Recall basic arithmetic operators

   The multiplication operator in spreadsheets is the asterisk symbol *.

2. Step 2: Match operator to multiplication

   Among the options, only * represents multiplication.

3. Final Answer:

   * -> Option B

4. Quick Check:

   Multiplication = * [OK]

Hint: Remember * means multiply in formulas [OK]

Common Mistakes:

• Confusing + with multiplication
• Using ^ for multiplication
• Using / instead of *

2. Which of the following is the correct formula to add the values in cells A1 and B1 in Google Sheets?

easy

A. =A1+B1

B. A1+B1

C. =SUM(A1 B1)

D. =A1*B1

Solution

1. Step 1: Understand formula syntax

   Formulas must start with an equals sign = to calculate values.

2. Step 2: Identify correct addition syntax

   The correct way to add two cells is =A1+B1. =A1*B1 multiplies instead, A1+B1 misses the equals sign, and =SUM(A1 B1) has wrong syntax.

3. Final Answer:

   =A1+B1 -> Option A

4. Quick Check:

   Formula starts with = and uses + for addition [OK]

Hint: Always start formulas with = and use + for addition [OK]

Common Mistakes:

• Omitting = at start
• Using * instead of +
• Wrong SUM syntax without comma

3. What is the result of the formula =10 + 5 * 2 in Google Sheets?

medium

A. 20

B. 15

C. 30

D. 25

Solution

1. Step 1: Apply order of operations

   Multiplication (*) is done before addition (+), so calculate 5 * 2 = 10 first.

2. Step 2: Add the results

   Then add 10 + 10 = 20.

3. Final Answer:

   20 -> Option A

4. Quick Check:

   Multiplication before addition = 20 [OK]

Hint: Multiply before adding in formulas [OK]

Common Mistakes:

• Adding before multiplying
• Ignoring operator precedence
• Calculating left to right only

4. The formula =A1 + * B1 gives an error in Google Sheets. What is the correct fix?

medium

A. =A1 + (B1

B. =A1 * B1

C. =A1 + B1 *

D. =A1 + B1

Solution

1. Step 1: Identify syntax error

   The formula has an invalid operator sequence + * which is not allowed.

2. Step 2: Correct the formula

   Remove the extra operator to get =A1 + B1, which adds the two cells correctly.

3. Final Answer:

   =A1 + B1 -> Option D

4. Quick Check:

   Only one operator between operands [OK]

Hint: Use only one operator between values [OK]

Common Mistakes:

• Using two operators together
• Leaving trailing operators
• Missing equals sign

5. You want to calculate the total cost in cell C1 by multiplying quantity in A1 by price in B1, then adding a fixed shipping fee of 5. Which formula correctly does this?

hard

A. =A1 * (B1 + 5)

B. A1 * B1 + 5

C. =(A1 * B1) + 5

D. =A1 + B1 * 5

Solution

1. Step 1: Understand calculation order

   You must multiply quantity and price first, then add shipping fee.

2. Step 2: Use parentheses to ensure correct order

   Formula =(A1 * B1) + 5 ensures multiplication happens before addition. A1 * B1 + 5 misses the equals sign; C adds shipping to price before multiplying (wrong); D multiplies price by 5 then adds quantity (wrong).

3. Final Answer:

   =(A1 * B1) + 5 -> Option C

4. Quick Check:

   Multiply first, then add shipping with parentheses [OK]

Hint: Use parentheses to control calculation order [OK]

Common Mistakes:

• Adding before multiplying
• Wrong operator placement
• Missing parentheses causing wrong results