Dashboard without Pivot Tables (only Formula and Functions)

Hi everyone,

I'm planning to create a dashboard without pivot tables due to limitations of Excel's macOS version, therefore I won't be able to create a relationship between two different large datasets, which leads me to use formulas and function to perform counts, sums and average summaries for individuals and entire teams. The dashboard must have a dropdown that drills down the data based on Departments, Supervisors, Agents, Months, Fiscal Month and Fiscal Weeks.

On the auxiliary tab is where I want to apply all the calculations and use the Dashboard to pull the data using dropdown and populate the tables with filters, equals, xlookup, etc.

Upon changing the selector (dropdown), the sums, counts and averages on the auxiliary tab must change with it. I was thinking about using SUMPRODUCT, however when I leave on selector in blank, it doesn't return a value. I tried to fix it using IF and IFS but with no success. Could someone help me with this?

Also there's a table named 'progression' which I would like some help as well, for this one I'm planning to input the supervisor name on D10 and the agent list (D15:D38) would fill automatically and adjust dynamically based on the supervisor's team size of agents. I got stuck on this part and trying to figure out how to apply a conditional formula based on target value, for instance a AHT (average handle time) greater than 20 would be off target and should be colored as red, however we have different indicator which a high value is something good (on target), for example a satisfaction greater than 85% would be on target and colored in green. So this would be the conflict on this part.

Yes, its O365 for Mac. I will add to the profile, thank you!

I see the opportunity to explain it better on the file, thanks for bring it up. I uploaded another file with less information and I'll do my best to describe how the calculations should work.

Example.jpg

I don't exactly know the best approach for this calculation, if it would be Countifs, Sumsifs, averageifs or sumproducts.
Considering it will be a very large dataset (˜~50-100mb worth of data), I'm thinking in what would be the best approach to not make this dashboard something too slow.

What I'm not being able to wrap up my head around it is the conditional criteria that will change the calculation on the auxiliary tab, for example:

If I select:

Case Component
OS Lvl 1 Phone Staff Type
OS Lvl 1 Phone Queue Type
Rubie Gibson Team Manager
Brad Riley Agent Name
2023P08 Fiscal Month
2023P08W3 Fiscal Week

The calculation must be specific for this selection for the KPI's: Answered, Satisfaction %, Surveys, Positive Surveys, Negative Surveys, Average Handle Time.

Screenshot 2024-06-17 at 12.49.37.png

I hope this brings a clearer understanding on what I'm trying to do.

Thank you! It's seems way better than using SUMPRODUCT.

However, I think there's an issue considering the way this dashboard is intended to, the ideia behind the dashboard is to drill down the data based on the criteria selected on the dropdown.

Scenario 1: Let's say that I want to a High-Level view of a Team Manager's performance, for this I select only the Team Manager name on the dropdown (=dashboard!C14) and have the cells B21:H25 filled with data for this Team Manager.
Scenario 2: Now I need to understand the performance per Fiscal Month and Fiscal Week, for this I keep the same Team Manager and choose the Fiscal Month and Week to be analyzed. This would bring summaries from the Answered, Surveys and Average Handle Time from that period of time for that Team Manager.

One thing that I missed pointing it out is that the Cases (B21:B25) must be sorted on a descending order, is there a way where we can make this dashboard interactive like this? The analysis are going to rely mostly on Pareto principle.

190624_Example Data.xlsx

I got the sorting part of it and after some testing the formulas are calculating correctly (thanks JeteMc).

Now the only thing that I'm trying to solve is the drop-down menus, they should be limited by the availability of data based on the source_sv and source_int dataset.

I'm stuck at this dependent drop-down considering there is 14 fiscal months, +50 fiscal weeks, 10 Team managers and +180 Agents on the original dataset. Therefore, all the agents must be allocated to their respective Team Manager and I'm not sure how to set up this drop-down. The dataset with the survey responses and average handle time per case type has one row per survey details and case details such as handle time, case note time, hold, etc.

I've managed to implement a dependent drop-down based on a combination of TRANSPOSE, SORT, FILTER and UNIQUE functions.

Creating a unique list of values where the drop-down works, however there's a final touch to complete this dashboard.

How to apply COUNT, SUM and AVERAGE calculations based on Month-To-Date, Quarter-To-Date, Year-To-Date?

Sorry about that, I should've been more specific regarding what should be averaged. The Average Handle Time would be the one to be averaged across MTD, QTD and YTD.

I downloaded the spreadsheet and visualized the solution you provided, thank you for that! Is there a way to associate the MTD to the selected Fiscal Month on the drop-down?

Also, there's one thing in the formula where I'm still not being able to solve it. Anytime where the drop-down is left as blank the cells from B6:B11 returns a "0" in the auxiliary sheet which makes the calculations for the COUNTIFS and AVERAGEIFs to stop working. The ideia behind this would be to drill down the data as we change the drop-down, they should work as filters to analyze the data further.

On the dashboard sheet, if I select C8 as "2023P08" it should return only the COUNTIFs and AVERAGEIFs for that fiscal period. If I include the C11 as "2023P08W2", then it should consider the COUNTIFS and AVERAGEIFS for "2023P08" and "2023P08W2" which are both the fiscal month and its fiscal week, I hope that I explained clearly what is the goal here. It's kind like a AND, OR operation to include or remove the drop-down as filters which would reflect on the final calculation.

Thank you, Jete. The calculations for MTD, QTD and YTD are working. You are AWESOME!

There's one thing tho that it is not working as intended.

Attachment 872715

As you can see, when the fiscal week is not selected (or any other drop-down) that leaves a zero as value on the auxiliary sheet, from B6 to B10 (B6:B10) which breaks the calculation. What are the possible solutions for this?

It worked! I had to add an IF statement after the second IFS.

The solution ended up looking like this:

Formula:

Please Login or Register  to view this content.

Thank you for helping me out since the first post.

Is there a way to sort this?

Formula:

Please Login or Register  to view this content.

Hey Jete!

Thanks for the answer, it worked! However, there's something that I need to implement on this calculation.

Attachment 885535

As demonstrated on the screenshot above, the count must consider the selection from B5 all the way to B11. Drilling down the data based on the criteria on B5:11. This way it would be possible to visualize the data based on the selection per Area Manager, Wave, Team Manager, Agent, Fiscal Month and Fiscal Week.