95 Percentile Calculation

Options
✭✭✭✭

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.

• ✭✭✭✭✭✭
Options

@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.

«1

• ✭✭✭✭✭✭
edited 09/01/20
Options

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?

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
Options

How can i construct this formula?

• ✭✭✭✭✭✭
Options

What do you want it to return?

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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).

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭
Options

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"))

• ✭✭✭✭✭✭
Options

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))

• ✭✭✭✭✭✭
Options

@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.

• ✭✭✭✭✭✭
Options

Great!

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

• ✭✭✭✭✭✭
Options

@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.