Welcome to Excel Jet Consult blog post. In this Excel tutorial, we will learn how to combine TAKE and Drop with Multiple Functions to Create Classic Single Cell Dynamic Report👌 Let’s get started
Video
Sample Data
The screenshot below shows the sample dataset that is formatted as an Excel Table
In the screenshot below, we’ve got a drop-down in cell K1 that houses the extracted sorted unique list of the Order Priority in cells N1 to N5
Formula to Combine TAKE & DROP with other functions
The goal of this exercise is to learn how we can combine the TAKE and DROP functions couple with other functions to create classic single cell dynamic report with totals at the bottom.
- In cell A2, execute this giant formula: =LET(
UniqueSortedListofAcctManager,VSTACK(SORT(UNIQUE(Account_Manager)),”Totals”),
UniqueOrderPriority,K1,
CountifsofAcctManbyOrderPriority,DROP(COUNTIFS(Account_Manager,UniqueSortedListofAcctManager,Order_Priority,UniqueOrderPriority),-1),
AverageifsofAcctManbyOrderPriority,DROP(AVERAGEIFS(Total,Account_Manager,UniqueSortedListofAcctManager,Order_Priority,UniqueOrderPriority),-1),
MaxifsofAcctManbyOrderPriority,DROP(MAXIFS(Total,Account_Manager,UniqueSortedListofAcctManager,Order_Priority,UniqueOrderPriority),-1),
MinifsofAcctManbyOrderPriority,DROP(MINIFS(Total,Account_Manager,UniqueSortedListofAcctManager,Order_Priority,UniqueOrderPriority),-1),
SumifsofAcctManbyOrderPriority,DROP(SUMIFS(Total,Account_Manager,UniqueSortedListofAcctManager,Order_Priority,UniqueOrderPriority),-1),
resultofCountifsofAcctManbyOrderPriority,VSTACK(CountifsofAcctManbyOrderPriority,SUM(CountifsofAcctManbyOrderPriority)),
resultofAverageifsofAcctManbyOrderPriority,VSTACK(AverageifsofAcctManbyOrderPriority,SUM(AverageifsofAcctManbyOrderPriority)),
resultofMaxifsofAcctManbyOrderPriority,VSTACK(MaxifsofAcctManbyOrderPriority,SUM(MaxifsofAcctManbyOrderPriority)),
resultofMinifsofAcctManbyOrderPriority,VSTACK(MinifsofAcctManbyOrderPriority,SUM(MinifsofAcctManbyOrderPriority)),
resultofSumifsofAcctManbyOrderPriority,VSTACK(SumifsofAcctManbyOrderPriority,SUM(SumifsofAcctManbyOrderPriority)),
percentageoftotal,resultofSumifsofAcctManbyOrderPriority/TAKE(resultofSumifsofAcctManbyOrderPriority,-1),
finalcalculation,HSTACK(UniqueSortedListofAcctManager,resultofCountifsofAcctManbyOrderPriority,resultofAverageifsofAcctManbyOrderPriority,resultofMaxifsofAcctManbyOrderPriority,resultofMinifsofAcctManbyOrderPriority,resultofSumifsofAcctManbyOrderPriority,percentageoftotal),finalcalculation) - Click OK.
Giant Formula Explanation:
Let’s break down the formula step by step to understand its purpose:
- LET: The LET function allows you to define variables within a formula, making it easier to manage and understand complex calculations.
- UniqueSortedListofAcctManager: This variable stores the unique and sorted list of account managers.
- UniqueOrderPriority: This variable seems to be related to the priority of orders (denoted by cell K1).
- CountifsofAcctManbyOrderPriority: This variable counts the occurrences of each account manager based on their order priority.
- AverageifsofAcctManbyOrderPriority: This variable calculates the average value of the “Total” column for each account manager based on their order priority.
- MaxifsofAcctManbyOrderPriority: This variable finds the maximum value from the “Total” column for each account manager based on their order priority.
- MinifsofAcctManbyOrderPriority: This variable finds the minimum value from the “Total” column for each account manager based on their order priority.
- SumifsofAcctManbyOrderPriority: This variable calculates the sum of the “Total” column for each account manager based on their order priority.
- resultofCountifsofAcctManbyOrderPriority: This variable stores the result of the count operation for each account manager and their order priority.
- resultofAverageifsofAcctManbyOrderPriority: This variable stores the result of the average operation for each account manager and their order priority.
- resultofMaxifsofAcctManbyOrderPriority: This variable stores the result of the maximum operation for each account manager and their order priority.
- resultofMinifsofAcctManbyOrderPriority: This variable stores the result of the minimum operation for each account manager and their order priority.
- resultofSumifsofAcctManbyOrderPriority: This variable stores the result of the sum operation for each account manager and their order priority.
- percentageoftotal: This variable calculates the percentage of the total value represented by each account manager based on their order priority.
- finalcalculation: This variable combines all the previously calculated results (e.g., counts, averages, max, min, sum, percentage) into a single array using the HSTACK function.
- The formula finally returns the finalcalculation variable as the result of the entire expression.
Voila! From the screenshot below, we’ve got the unique sorted list of Account Manager, Count by Account Manager, Average by Account Manager, Max by Account Manager, Min by Account Manager, Total Sales by Account Manager and % of Total Sales
See you in the next video