0
0
Google Sheetsspreadsheet~8 mins

Arithmetic operators in Google Sheets - Dashboard Guide

Choose your learning style9 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
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
ProductPriceQuantity Sold
Apples230
Bananas150
Cherries320
Dates415
Elderberries510
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.