LAMBDA for custom functions in Excel - Dashboard Guide

Dashboard Mode - LAMBDA for custom functions

Dashboard Goal

This dashboard helps you calculate custom metrics easily by creating your own reusable formulas using Excel's LAMBDA function. It shows how to define a custom function to calculate the average sales per product and then use it across the data.

Sample Data

Product	Sales Q1	Sales Q2	Sales Q3
Apples	100	150	130
Bananas	80	90	100
Cherries	120	110	140
Dates	90	95	85
Elderberries	70	60	75

Dashboard Components

• Custom Function Definition (Named Formula): AvgSales = LAMBDA(q1, q2, q3, (q1 + q2 + q3) / 3)
  Defines a reusable function to calculate average sales from three quarters.
• Average Sales per Product: Formula in cell E2 and down:
  =AvgSales(B2, C2, D2)
  Calculates average sales for each product using the custom function.
• Total Average Sales: Formula in cell E8:
  =AVERAGE(E2:E6)
  Shows the overall average sales across all products.

Dashboard Layout

+-----------------------------+
|        Sales Data           |
| +-------------------------+ |
| | Product | Q1 | Q2 | Q3  | |
| |---------|----|----|-----| |
| | Apples  |100 |150 |130  | |
| | Bananas | 80 | 90 |100  | |
| | Cherries|120 |110 |140  | |
| | Dates   | 90 | 95 | 85  | |
| | Elderb. | 70 | 60 | 75  | |
| +-------------------------+ |
| Avg Sales per Product (Col E)|
| E2: =AvgSales(B2,C2,D2)      |
| ...                          |
| E8: Total Avg Sales: =AVERAGE(E2:E6) |
+-----------------------------+

Interactivity

You can change any sales number in columns B, C, or D. The average sales per product and total average sales update automatically because the custom function AvgSales recalculates with new inputs.

Adding filters or slicers on Product names can help focus on specific products, updating the total average accordingly if you adjust the formula to consider filtered data.

Self Check

If you add a filter to show only products with Sales Q1 greater than 90, which products remain and what is the new total average sales?

Answer: Products remaining are Apples (100) and Cherries (120). Dates (90) is not greater than 90, so it is excluded. The average sales recalculated for these two products using the AvgSales function will update the total average sales accordingly.

Key Result

Dashboard demonstrating how to create and use a custom average sales function with Excel's LAMBDA.