# TRIMMEAN in Smartsheet

Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• Options

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

• Employee
Options

Wow, thanks for sharing this!

October 8 - 10, Seattle, WA | Register now

• Options

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

• ✭✭✭✭✭✭
edited 08/12/21
Options

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

• Employee
Options

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!