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!
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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!
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!