TRIMMEAN in Smartsheet

Anabisdally Castro

How can I calculate the equivalent of excel's TRIMMEAN in Smartsheet? Anyone have any wisdom to share?

L_123

I would use standard deviations from the mean. You can use a z table to lookup the z score correlating to the desired percent, and replace both of the 2's in the formula below. You can also change the stdev formula to represent if you are working with a sample or population with ease, just change the letter afterwords. The 2 I selected represents 95% of the predicted data. Dataset represented by all data in column A.

=AVG(COLLECT(A:A, A:A, AND(@cell >= AVG(A:A) - (2 * STDEVP(A:A)), @cell <= AVG(A:A) + (2 * STDEVP(A:A)))))

Paul Newcome

@L@123 Yup. I've never used a z table (I had to google it) or standard deviation (only a vague idea on that is as well). I appreciate you chiming in as I would have never been able to figure it out quite like that. My solution would have been rather messy by comparison (if I was able to find one to begin with).

@Anabisdally Castro Take a look at the comment above. Are you able to get that working for you?

Paul Newcome

From my understanding of the TRIMMEAN function, we can use an AVG/COLLECT as the base of the formula. The tricky part is figuring out which ones to exclude based on the percentage you want.

=AVG(COLLECT({Range to Average}, {Range to Average}, AND(@cell >= low_end, @cell <= high_end))

We could use LARGE and SMALL functions to make it work, but that would assume that you don't have duplicate values. I will have to think on this one for a bit.

@L@123 @Mike Wilday @Andrée Starå @Genevieve P. Do any of you have any ideas or have a solution built already?

L_123

I would use standard deviations from the mean. You can use a z table to lookup the z score correlating to the desired percent, and replace both of the 2's in the formula below. You can also change the stdev formula to represent if you are working with a sample or population with ease, just change the letter afterwords. The 2 I selected represents 95% of the predicted data. Dataset represented by all data in column A.

=AVG(COLLECT(A:A, A:A, AND(@cell >= AVG(A:A) - (2 * STDEVP(A:A)), @cell <= AVG(A:A) + (2 * STDEVP(A:A)))))

L_123

If you want a smartsheet based z table you can copy mine below:

https://app.smartsheet.com/b/publish?EQBCT=d37bad54bb3045c2be5e9f0f78cc0db3

Paul Newcome

@L@123 Yup. I've never used a z table (I had to google it) or standard deviation (only a vague idea on that is as well). I appreciate you chiming in as I would have never been able to figure it out quite like that. My solution would have been rather messy by comparison (if I was able to find one to begin with).

@Anabisdally Castro Take a look at the comment above. Are you able to get that working for you?

Anabisdally Castro

Hi! Thank you so much for the help! I am trying to wrap my head around this one - I too have to go brush up on my stats training ;) - I shall return with news! :)

Genevieve P.

https://community.smartsheet.com/discussion/comment/300910#Comment_300910

Wow, thanks for sharing this!

Anabisdally Castro

I can confirm! This works! Woohoo!! Thank you very very much for the help! - the Z table is mighty handy. :)

L_123

I'm glad it helped. @Genevieve P. i'd recommend smartsheet make it's own verified g and t tables available to the public, as it was kind of a pain to build my own. oddly enough this isn't readily available online as a copy paste as far as my searches went. I had to find textbooks from my days in stats and manually translate it, so I had to take a lot of time to add it to smartsheet. i've used this to build live spc data, which is VERY valuable to high precision machining work, and might introduce smartsheet to new business.

@Anabisdally Castro I'm glad I was able to help. Let me know if you have any questions with this going forward

Genevieve P.

@L_123

That's a really good point; I'll pass this along to our Product team to see if they've thought of building something like this as a template set to download. Thank you!