+ Reply to Thread
Results 1 to 19 of 19

Dashboard without Pivot Tables (only Formula and Functions)

  1. #1
    Registered User
    Join Date
    06-12-2024
    Location
    Sao Paulo, Brazil
    MS-Off Ver
    O365 for Mac (Version 16.85)
    Posts
    11

    Exclamation 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.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Dashboard without Pivot Tables (only Formula and Functions)

    Welcome to the forum.

    Is your Excel product 365? If so, please add 365 for Mac to your forum profile. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    06-12-2024
    Location
    Sao Paulo, Brazil
    MS-Off Ver
    O365 for Mac (Version 16.85)
    Posts
    11

    Re: Dashboard without Pivot Tables (only Formula and Functions)

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

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,987

    Re: Dashboard without Pivot Tables (only Formula and Functions)

    The only thing that I see on the dashboard sheet are cells filled with #REF!
    On the auxiliary sheet there are numeric values but no explanation of how they are calculated.
    It would be helpful to know how the values are calculated so that we can attempt to write formulas that yield the expected results.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    06-12-2024
    Location
    Sao Paulo, Brazil
    MS-Off Ver
    O365 for Mac (Version 16.85)
    Posts
    11

    Re: Dashboard without Pivot Tables (only Formula and Functions)

    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.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,987

    Re: Dashboard without Pivot Tables (only Formula and Functions)

    I have a feeling that the 365 supported functions will be faster so this response is basically to give someone else a general idea.
    1. I added a column (Team) to the source_sv table and populated that column using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    2. On the dashboard sheet the Team column is populated using the array formula**:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    3. For the Answered column:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    4. On the auxiliary sheet, the Answered column:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Note: I think that since you are using the 365 version you can activate the formula by just pressing the Enter key.
    Let us know if you have any questions.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    06-12-2024
    Location
    Sao Paulo, Brazil
    MS-Off Ver
    O365 for Mac (Version 16.85)
    Posts
    11

    Re: Dashboard without Pivot Tables (only Formula and Functions)

    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.

  8. #8
    Registered User
    Join Date
    06-12-2024
    Location
    Sao Paulo, Brazil
    MS-Off Ver
    O365 for Mac (Version 16.85)
    Posts
    11

    Re: Dashboard without Pivot Tables (only Formula and Functions)

    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.

  9. #9
    Registered User
    Join Date
    06-12-2024
    Location
    Sao Paulo, Brazil
    MS-Off Ver
    O365 for Mac (Version 16.85)
    Posts
    11

    Re: Dashboard without Pivot Tables (only Formula and Functions)

    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?

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,987

    Re: Dashboard without Pivot Tables (only Formula and Functions)

    Not entirely sure what should be counted and no idea what should be summed or averaged.
    To get a count of records involving a particular agent:
    Add two columns to the data on the source_int sheet
    Column S to give an actual date as opposed to the text in column F: =DATEVALUE([Fiscal Day])
    Column T to give quarter: =ROUNDUP(MONTH([@[Fiscal Day]])/3,0)
    Month to date: =COUNTIFS(source_int[Agent],$B29,source_int[Date_Value],">"&EOMONTH(TODAY(),-1),source_int[Date_Value],"<="&TODAY())
    Quarter to date: =COUNTIFS(source_int[Agent],$B29,source_int[Date_Value],">"&DATE(YEAR(TODAY())-1,12,31),source_int[Date_Value],"<="&TODAY(),source_int[Quarter],J$27)
    Year to date: =COUNTIFS(source_int[Agent],$B29,source_int[Date_Value],">"&DATE(YEAR(TODAY())-1,12,31),source_int[Date_Value],"<="&TODAY())
    Let us know if you have any questions.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    06-12-2024
    Location
    Sao Paulo, Brazil
    MS-Off Ver
    O365 for Mac (Version 16.85)
    Posts
    11

    Re: Dashboard without Pivot Tables (only Formula and Functions)

    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.

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,987

    Re: Dashboard without Pivot Tables (only Formula and Functions)

    Modification of the MTD count formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    MTD average formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that the average formula substitutes AVERAGEIFS for COUNTIFS. Similar substitutions are made in the QTD and YTD formulas. Furthermore to get a sum just substitute SUMIFS for AVERAGEIFS.
    Let us know if you have any questions.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    06-12-2024
    Location
    Sao Paulo, Brazil
    MS-Off Ver
    O365 for Mac (Version 16.85)
    Posts
    11

    Re: Dashboard without Pivot Tables (only Formula and Functions)

    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?

  14. #14
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,987

    Re: Dashboard without Pivot Tables (only Formula and Functions)

    Selecting Attachment 872715 results in the following error message: "Invalid Attachment specified. If you followed a valid link, please notify the administrator".
    Moreover, the formula in cells B15:B25 uses the Unique function and the formula in cells E15:F1788 uses the Sort function, neither of which are supported by my version of Excel.

  15. #15
    Registered User
    Join Date
    06-12-2024
    Location
    Sao Paulo, Brazil
    MS-Off Ver
    O365 for Mac (Version 16.85)
    Posts
    11

    Re: Dashboard without Pivot Tables (only Formula and Functions)

    I'm using this as a solution

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    It's working based on the selected criteria, however, I'm not sure how to implement the criteria source_int[Case Issue] along with the source_int[Case Component], there's a dropdown dedicated to Case Issue Or Case Component.
    Last edited by Sakram3ntum; 06-25-2024 at 03:53 PM. Reason: Formula

  16. #16
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,987

    Re: Dashboard without Pivot Tables (only Formula and Functions)

    You could prefix the IFS formula with =IF($B$5="Case Component",...
    The IFS formula in post #15 would be the value_if_true argument.
    You could then repeat the entire IFS formula substituting criteria source_int[Case Issue] for source_int[Case Component] for the value_if_false argument.
    The final formula should look like:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I can't test as cell B15 uses the Unique function.

  17. #17
    Registered User
    Join Date
    06-12-2024
    Location
    Sao Paulo, Brazil
    MS-Off Ver
    O365 for Mac (Version 16.85)
    Posts
    11

    Re: Dashboard without Pivot Tables (only Formula and Functions)

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

    The solution ended up looking like this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Thank you for helping me out since the first post.

  18. #18
    Registered User
    Join Date
    06-12-2024
    Location
    Sao Paulo, Brazil
    MS-Off Ver
    O365 for Mac (Version 16.85)
    Posts
    11

    Re: Dashboard without Pivot Tables (only Formula and Functions)

    Is there a way to sort this?
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  19. #19
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,987

    Re: Dashboard without Pivot Tables (only Formula and Functions)

    Please tell us where in the file the formula is being used and what the expected outcome/s is/are.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. What are the uses of the cube functions in pivot tables?
    By kvemperor in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-12-2023, 05:23 AM
  2. Can I set up a dashboard, using pivot tables?
    By ZM003 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-08-2022, 01:20 PM
  3. [SOLVED] Dashboard for Pivot Tables and Pivot Charts - Question 01
    By phillyfilly in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-27-2017, 06:58 AM
  4. Refreshing Pivot Tables/Charts for Dashboard type report in VBA
    By Orada in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-26-2016, 02:21 PM
  5. Replies: 6
    Last Post: 10-14-2011, 12:34 PM
  6. Functions and Pivot Tables
    By mcooper1975 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-02-2009, 07:02 PM
  7. Non default functions in pivot tables
    By JoshuaSQ in forum Excel General
    Replies: 2
    Last Post: 02-29-2008, 02:24 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1