Need median of bottom 20% of values with multiple criteria

[Carscampbell]

Hi all, Thanks everything you all have taught me so far.

I would like to find the median of the bottom 20% of values for each of my 8 columns of data. All the values on my spreadsheet fall into 6 main groups, with up to 3 content types. All of those combinations need to be filtered by sponsored or not sponsored. There are 2000+ rows of data.

I currently have it set up in a pivot table which breaks it down and gives me the medians all the values in each group - not the bottom 20%. I have also have another sheet with arrays to get me the medians of all values, but can't figure out how to get the medians of bottom 20% of values for each column of data. I would be happy if I could do it in an array or pivot table, rather than calculate the which rows are in the bottom 20% and then run a median of that for each column.

Thanks in advance

[hrlngrv]

The core of the problem is that you'd need to add another column to your original table of data to calculate the 20% for the given criteria. If a particular combination of group, content type, and sponsorship status had fewer than 5 records, the 20th percentile wouldn't really exist. Anyway, you'd be best off using an advanced filter to generate a separate table of distinct combination of the 3 category fields. Given your actual data, there'd be 21 distinct combinations of group, content type and sponsorship status, so I'll assume that's in Q3:S23.

T3: =COUNTIFS(A$3:A$61,Q3,B$3:B$61,R3,E$3:E$61,S3)

Fill T3 down into T4:T23. Then you need additional columns for each of the value columns in your original data. I'll use col U for data col C labeled OL. The same approach would be needed for the other columns. The following is an array formula.

U3: =IF(T3>4,PERCENTILE.INC(IF($A$3:$A$61=Q3,IF($B$3:$B$61=R3,IF($E$3:$E$61=S3,$C$3:$C$61))),0.2),#N/A)

Fill U3 down into U4:U23. Then the median you want would be given by the array formula

=MEDIAN(IF($A$3:$A$61=Q3,IF($B$3:$B$61=R3,IF($E$3:$E$61=S3,IF($C$3:$C$61<=U3,$C$3:$C$61)))))

for the combination of category field values in Q3:S3.

[Carscampbell]

Thanks! My actual data set has over 2100 rows, I cut it back based on the forum requirements. using other data sets, looking for the same calculations, I could have upwards of 5,000 rows. Does that change your response?

[hrlngrv]

My response would remain the same: use another table to list the distinct combinations of group, content type, and sponsorship status. Calculate the number of records for each of those distinct combinations using COUNTIFS. Use additional columns to calcuate the 20th percentile for each of the quantity columns. Finally, use array formulas with lots of nested IF calls to calculate the medians of records in the bottom quintile.