95 Percentile Calculation

Jessintha Nathan

Hello,

I need help nesting/modifying formula listed below to calculate (i) Average and (ii) Median (iii) P95

Currently to calculate average, I pull the count in a different cell and divide the SUMIF with total count. Would be helpful if there was an easier way.

=SUMIFS({Weekly Update Range 105}, {Weekly Shop Visit Update 51}, <=[Start Date]74, { Weekly Update 64}, >=[End Date]74, {Weekly Update Range 88}, OR(@cell = "HEAVY/PRSV/LLP", @cell = "MEDIUM", @cell = "LIGHT>500"))

Thank you.

Paul Newcome

@Jessintha Nathan @L@123 They now have a MEDIAN function (link included). I just got an email about it yesterday evening, but it looks like it came out on the 18th of this month.

L_123

Smartsheet does have an average formula. =avg() it even has an averageif formula if you want to just find the average of items that fit a criteria. Median is more difficult... I'll think on that one and get back to this. Are the numbers in any sort of order?

Can you tell us a little more about the 95%? You want the number representing the 95% line? or you want the count of values over 95%? Something different?

L_123

As far as the 95% goes, that is equal to 2 standard deviations away from the mean, which means (pun intended) you can use the std dev formula in combination with the mean to determine the range in which 95% of your values lie if that is what you are looking for. You can also determine which/how many values lie outside the top and/or bottom of this range.

Jessintha Nathan

How can i construct this formula?

L_123

What do you want it to return?

Paul Newcome

For Median...

Maybe rank in a helper column then use

LARGE(Rank:Rank, ROUND(COUNT(Rank:Rank) / 2))

ROUND(COUNT(Rank:Rank) / 2) will give you half of the count of entries, and the LARGE function will pull that rank. Then maybe an INDEX/MATCH with the MATCH pulling from the LARGE function?

L_123

The large formula doesn't even need a helper column. What you've given Paul will calculate the median off the raw dataset.

=LARGE(Rank:Rank, ROUND(COUNT(Rank:Rank) / 2))

They might need to put in a collect instead of rank:rank to filter categories, but +1 man. I wouldn't have thought of using large. Didn't even realize that it takes into account duplicates.

Paul Newcome

You're right. Duh. Using the LARGE function means we don't need to rank anything. Over thought that one a little bit. The same could be done with the SMALL function as well.

If we did a LARGE(COLLECT(...... wouldn't we also want to do a COUNTIFS with the same range/criteria sets to make sure that is pulling the accurate count for the LARGE?

=LARGE(COLLECT(Numbers:Numbers, Category:Category, "Category A"), ROUND(COUNTIFS(Category:Category, "Category A") / 2))

But yeah, the LARGE function works pretty well with duplicate numbers depending on your needs (sometimes it can actually be more of a hindrance though).

L_123

Yeah, but with the distinct formula it opens large up to all the potential of not having duplicates as well.

Good catch on the countifs

Paul Newcome

I forgot about the DISTINCT function. That one definitely comes in handy in quite a few different situations.

So to get the average I am thinking we would end up using an AVERAGE/COLLECT. The median can be found using the LARGE/COLLECT/COUNTIFS.

@Jessintha Nathan Can you expand on the "P95"? What exactly are you looking for?

Jessintha Nathan

Hi Paul,

i have a series of days to collect data in lets call in Column K. i want to calculate the median days to collect from the series.

how do i modify my sumif construct to calculate Median?

=SUMIFS({Weekly Update Range 105}, {Weekly Update 51}, <=[Start Date]74, { Weekly Update 64}, >=[End Date]74, {Weekly Update Range 88}, OR(@cell = "HEAVY/PRSV/LLP", @cell = "MEDIUM", @cell = "LIGHT>500"))

Paul Newcome

For the Median we can use the LARGE/COLLECT. It would end up looking something along the lines of this...

=LARGE(COLLECT({Weekly Update Range 105}, {Weekly Update 51}, <=[Start Date]74, { Weekly Update 64}, >=[End Date]74, {Weekly Update Range 88}, OR(@cell = "HEAVY/PRSV/LLP", @cell = "MEDIUM", @cell = "LIGHT>500")), ROUND(COUNTIFS({Weekly Update 51}, <=[Start Date]74, { Weekly Update 64}, >=[End Date]74, {Weekly Update Range 88}, OR(@cell = "HEAVY/PRSV/LLP", @cell = "MEDIUM", @cell = "LIGHT>500")) / 2))

Paul Newcome

@Jessintha Nathan @L@123 They now have a MEDIAN function (link included). I just got an email about it yesterday evening, but it looks like it came out on the 18th of this month.

L_123

Great!

Just curious, how do you get email alerts for new functionality? Is there an opt in?

Paul Newcome

@L@123 I'll have to dig through my emails and see what I can find. I honestly don't remember exactly, but I do remember there was some sort of opt-in.

Paul Newcome

@L@123

https://offers.smartsheet.com/product-release-notes-subscribe.html?_ga=2.139569395.1707435100.1600707613-347735874.1587400244

Take a look here...