LET function for named calculations in Excel - Dashboard Guide

Dashboard Mode - LET function for named calculations

Goal

Calculate total sales, average sales, and sales growth using named calculations to make formulas easier to read and maintain.

Sample Data

Month	Sales
January	1000
February	1200
March	1500
April	1300
May	1600

Dashboard Components

• Total Sales: =LET(sales, B2:B6, SUM(sales)) - Adds all sales from January to May. Result: 6600
• Average Sales: =LET(sales, B2:B6, AVERAGE(sales)) - Calculates average monthly sales. Result: 1320
• Sales Growth (May vs April): =LET(sales, B2:B6, april, INDEX(sales, ROWS(sales)-1), may, INDEX(sales, ROWS(sales)), growth, (may - april) / april, growth) - Calculates growth rate from April to May. Result: 0.2308 (23.08%)

Dashboard Layout

+----------------+----------------+---------------------+
| Total Sales    | Average Sales  | Sales Growth (May)  |
|    6600        |     1320       |       23.08%        |
+----------------+----------------+---------------------+
|                Sales Data Table                 |
|  Month   | Sales                                  |
|  Jan-May | 1000,1200,1500,1300,1600              |
+-------------------------------------------------+

Interactivity

Currently, the dashboard uses fixed data. To add interactivity, you could add a filter to select months. When months are filtered, the named range sales in the LET formulas updates automatically, recalculating total, average, and growth for the selected months.

Self Check

If you add a filter to show only January to March, which components update?

• Total Sales: Updates to sum sales for Jan-Mar (1000+1200+1500=3700)
• Average Sales: Updates to average sales for Jan-Mar (1233.33)
• Sales Growth: Updates to growth from February to March ( (1500-1200)/1200 = 0.25 or 25%)

Key Result

Dashboard showing total sales, average sales, and sales growth using LET function for clear named calculations.