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

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 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?
Answers

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?

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

If you want a smartsheet based z table you can copy mine below:
https://app.smartsheet.com/b/publish?EQBCT=d37bad54bb3045c2be5e9f0f78cc0db3

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

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

Wow, thanks for sharing this!
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
I can confirm! This works! Woohoo!! Thank you very very much for the help!  the Z table is mighty handy. :)

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

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!
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
Check out the Formula Handbook template!