Excel function to find quartiles based on sum of values

user_10210252 0
Viewed 32

I am looking for a function or at least a streamlined way to split my data into quartiles based on the sum of values grouped by size in Excel (Office 365) i.e. if my company earns $1,000,000.00 a year, I want to know which of the largest customers form the top $250,000.00 and which of the smallest customers form the bottom $250,000.00 of revenue. The challenge I am having is that I need this to be replicable across multiple columns within the same table, even though the values may be ordered differently.

Below is a simplified example of what I am trying to achieve. Given the list of customers and their annual spend for fiscal periods 2020 & 2021, I want to know which customers fall into each quartile of overall revenue for the fiscal period:

Table of unordered values

Presently, I have a very clunky way to achieve the outcome I desire, but I am convinced that there must be a more efficient way to do this.

Firstly, I calculate the "cumulative" value of each quartile =Table1[[#Totals],[2020]]*0.25, =Table1[[#Totals],[2020]]*0.5 & =Table1[[#Totals],[2020]]*0.75 resulting:

enter image description here

I then create an ordered array using =SORT(Table1[2020]) & =SORT(Table1[2021]) separate from the table itself. This produces:

Table of ordered values

Followed by adding in columns to calculate the cumulative values using =SUM($G$2:G2) and so on, resulting in:

Ordered table with cumulative values

I then add another column to assign each of the values to a quartile, based on their cumulative values based on the monstrosity - =IF(H2<$B$16,"Quartile 1",IF(H2<$B$17,"Quartile 2",IF(H2<$B$18,"Quartile 3",IF(H2>$B$18,"Quartile 4")))) for each of the cells in the quartile columns, which results in:

Ordered table showing quartiles

Then, just in case there wasn't enough convolution, I port the values back into the original table using =XLOOKUP([@2020],$G$2#,$I$2:$I$11) etc. resulting, finally, in what I actually set out to achieve:

Final result

Whilst the values in the original table are a randomised array, as you can imagine, customer spend can vary significantly from year to year, meaning that the quartile (of the sum value) will likely change, so I need to automate this as far as reasonably practicable, ideally linking to an OLAP model once I can get the basic logic ironed out.

I have been pulling my hair out for days trying to figure out if there was a way to manipulate =PERCENTILE.EXC(), =PERCENTRANK.EXC(), =QUARTILE.EXC() and their .INC() counterparts to do this for me, but all of these functions seem only capable of basing results on the counts of cells, not the sum value.

Apologies for the mammoth write-up, but I was struggling to verbalise what I am trying to achieve and thought it would help to see.

Any help will be gratefully received!

excel
1 Answers

Try this formula in your 2020 Q column:

=LET(sort,SORT([2020]),
rTot,SCAN(0,sort,LAMBDA(a,c,a+c)),
Quartiles,XLOOKUP(rTot,Table1[[#Totals],[2020]]*SEQUENCE(4,1,0)/4,SEQUENCE(4),,-1),
XLOOKUP([@2020],sort,Quartiles))

It goes through exactly the steps that you describe, but in one cell.

enter image description here

Note that these aren't quartiles but an ad hoc measure based on splitting the sum into four equal parts. It's interesting to see the results you would get by using the quartile function with a formula like

=XLOOKUP([@2020],QUARTILE.INC([2020],{0,1,2,3}),{1,2,3,4},,-1)

enter image description here

Here the median (second quartile) is 6757.5 so 7044 (for example) comes out in the third quartile. What is happening is that the fairly high value of 9694 'pushes down' the middle values into the second 'quartile' using the first formula.

user_38949170
Related