Why advanced formulas solve complex problems in Excel - Dashboard Impact

Dashboard Mode - Why advanced formulas solve complex problems

Dashboard Goal

This dashboard shows how advanced Excel formulas help solve complex problems by combining data analysis, conditional logic, and dynamic calculations in one view.

Sample Data

Order ID	Product	Category	Quantity	Unit Price	Discount %	Order Date
1001	Notebook	Stationery	10	2.5	0	2024-01-15
1002	Pen	Stationery	20	1.2	10	2024-01-20
1003	Desk Lamp	Electronics	5	15	5	2024-02-05
1004	Chair	Furniture	2	45	0	2024-02-10
1005	Stapler	Stationery	7	6	15	2024-03-01
1006	Monitor	Electronics	3	120	10	2024-03-15
1007	Desk	Furniture	1	150	20	2024-03-20

Dashboard Components

• Total Sales: Calculates total sales after discount.
  Formula: =SUMPRODUCT(D2:D8,E2:E8*(1-F2:F8/100))
  Result: (10*2.5*1)+(20*1.2*0.9)+(5*15*0.95)+(2*45*1)+(7*6*0.85)+(3*120*0.9)+(1*150*0.8) = 25 + 21.6 + 71.25 + 90 + 35.7 + 324 + 120 = 687.55
• Average Discount %: Shows average discount applied.
  Formula: =AVERAGE(F2:F8)
  Result: (0+10+5+0+15+10+20)/7 = 8.57%
• Highest Sale Order ID: Finds order with highest sale after discount.
  Formula: =INDEX(A2:A8,MATCH(MAX(D2:D8*E2:E8*(1-F2:F8/100)),D2:D8*E2:E8*(1-F2:F8/100),0))
  Result: Order ID 1006 (Monitor sale 3*120*0.9=324)
• Sales by Category: Table summarizing total sales per category.
  Formula for Stationery: =SUMPRODUCT((C2:C8="Stationery")*(D2:D8)*(E2:E8)*(1-F2:F8/100))
  Result: Stationery = (10*2.5*1)+(20*1.2*0.9)+(7*6*0.85) = 25 + 21.6 + 35.7 = 82.3
  Similarly for Electronics and Furniture.
• Recent Orders Count: Counts orders after 2024-02-01.
  Formula: =COUNTIF(G2:G8,">2024-02-01")
  Result: 5 (orders 1003, 1004, 1005, 1006, 1007)

Dashboard Layout

+----------------------+----------------------+----------------------+
| Total Sales          | Average Discount %   | Highest Sale Order ID |
|      687.55          |        8.57%         |        1006           |
+----------------------+----------------------+----------------------+
|                      Sales by Category Table                     |
|  Category   |   Sales                                           |
|-------------|--------------------------------------------------|
| Stationery  | 82.3                                             |
| Electronics | 395.25                                           |
| Furniture   | 210                                              |
+------------------------------------------------------------------+
| Recent Orders Count: 5                                            |
+------------------------------------------------------------------+

Interactivity

Add a filter by Category. When you select a category, all components update to show data only for that category. For example, selecting "Electronics" updates Total Sales, Highest Sale Order ID, Sales by Category table (only Electronics row), and Recent Orders Count to reflect only Electronics orders.

Self Check

If you add a filter for Order Date > 2024-02-01, which components update?

• Total Sales: Updates to sum sales only after 2024-02-01.
• Average Discount %: Updates to average discount of filtered orders.
• Highest Sale Order ID: Updates to highest sale in filtered data.
• Sales by Category: Updates to show sales only for filtered orders.
• Recent Orders Count: Updates to count orders after 2024-02-01 (likely same or fewer).

Key Result

Dashboard demonstrating how advanced Excel formulas calculate total sales, discounts, highest sales, and category summaries dynamically.