Average Excluding Min and MAX Values. TRIMMEAN function. 6 Formula Examples. EMT 1744
HTML-код
- Опубликовано: 3 июл 2024
- Download Excel File: excelisfun.net/files/EMT1744....
Learn how to calculate the average while excluding the min and max values. See how to use TRIMMEAN function to exclude a given percentage of high and low data points. Trimmed Mean Calculation.
1. (00:00) Introduction
2. (00:23) Formula #1: SUM, MIN, MAX and COUNT aggregate functions.
3. (01:03) Formula #2: Learn how to use TRIMMEAN function to exclude a given percentage of high and low data points.
4. (02:09) Formula #3: TRIMMEAN to exclude only max and min values.
5. (02:27) Formula #4: How to enter multiple arguments into one argument using parentheses.
6. (03:23) Formula #5: AGGREGATE to simultaneously get MIN, MAX and SUM. See array formula tricks.
7. (04:54) Formula #6: LARGE and SEQUENCE functions inside AVERAGE function.
8. (05:51) Summary, Closing and Video Links
All solution is amazing 💪, I would use a typical AVERAGEIFS function:
=AVERAGEIFS(B14:B17,B14:B17,">="&MIN(B14:B17),B14:B17,"
That is such a great solution!!! I will add it to the download workbook so the rest of the Team can see it too : ) Thanks, PedjaNBGD!!!!
@@excelisfun You can Pin my comment at the top for others to see.
Cool, but this formula does not work as TRIMMEAN alternative if there are duplicates for min or max values, and "percent" argument is not represented at all. An alternative formula that reproduces exactly trimmean behavior, with duplicates or not is:
=AVERAGE(INDEX(SORT(a),SEQUENCE(ROWS(a)-2*k,,1+k))) where a is the array with values and k is the nr. of values to be excluded from top and bottom, (considering the array in ascending order) exactly what percentage argument does for trimmean. If k=0 , no values will be excluded, if k=1, first value from top and last from the bottom will be excluded, if k=2, top 2 values and last 2 values will be excluded ..and so on. As we all now , percentage argument does this , rounds down to multiples of 2 sections of the array.
Sorry to bother , but since your comment was pinned it was easy to read.😉✌
@@Excelambda Thanks for the clarification. I have added your formula and notes to the download workbook. Go Team!!!!
Thanks, Mike, for this amazingly EXCELlent video.
You are welcome, Syed MM Felloe Teacher : ) : ): ) : )
Thanks so much for offering a learning platform
You are welcome, daniel!!!!
You are my go to person when I have an excel issue to excel! Thank you Mike!
You are welcome, Celia!!!!
Thanks alot Sir ji ! Respect from India !
You are welcome!!!!
Silliness? No way! That's awesome! 👍
Silly fun ; ) Nice to hear from you, MF Wong!!!
There's always value addition with Mike 🙏🙏
Lots of additional fun ; )
You are a magician.
Amazing Mike Thanks, learning the nuts and bolts. That was FUN!!!
You are welcome for the formula fun, Formula Guy John ; )
Great video Mike. Thanks.
You are welcome, Manesh!!!
Excellent Mike!!!!
Thanks, Chris : ) : ) : )
Boom!Never Used The TRIMMEAN FUNCTION So Just Learned Something New "HAPPY DAYS"...Thank You Mike :)
Glad this was happy days for you, darryl : ) : ) : )
Hi Mr. Mike you are as amazing as always actually I love those ridiculous formulas because they teach us something new about the excel engine and how it works.
Silly and ridiculous, but fun! I am glad that you like them. It is fun for me too : )
This Lesson Just did TRIMMEAN My Average :) , Amazing as usual.
Glad you liked it : )
Hi Mike. Totally awesome Tuesday TRIMMEAN and other function fun!! Also, love the silly formulas, as they are great for understanding how to assemble solutions in many different ways. The golden nugget for me is your mention of the double parenthesis possible in some functions to assemble non-continuous ranges into a single argument. I noticed MIRR on your list and I tried it with the standard IRR() function and it works. I've been looking for this for years!! As always, your tutorials are full of multiple layers of EXCEL goodness and learning :)) Many thanks and thumbs up!!
Here is comment from below:
@Wayne Edmondson I am glad you said something. I found the old workbook. It was XIRR and XNPV that I could not get to work. Here are some others that I tried that did not work:
XIRR
XNPV
TEXT
MONTH
AVERAGEIF
COVAR
Here is the list that I got to work:
LARGE
SMALL
FREQUENCY
TRIMMEAN
PERCENTRANK
QUARTILE
RANK
RANK.AVG
RANK.EQ
PERCENTILE
AREAS
MIRR
INDEX
IRR
PERCENTILE.EXC
PERCENTILE.INC
PERCENTRANK.EXC
PERCENTRANK.INC
QUARTILE.EXC
QUARTILE.INC
I added these to the download workbook : )
Go Team!!!!
@@excelisfun Awesome Mike! Thanks for the lists of functions above. This is a great find for me.. I use IRR often and being able to fragment the values list is a super find. Go Team!!
@@wayneedmondson1065 That is cool! So you are a finance, cash flow analysis guy? Me too, from way back in the day. The first big models I ever made were finance ones. Excel makes cash flow analysis THE best : ) Go Team!!!
@@excelisfun Thanks Mike! Yes.. I'm a consultant in commercial real estate investment, development, finance, valuation, etc. I do lots of property cash flow projections, investor returns, debt scenarios, lease comparisons, purchase vs. lease analysis, ground lease analysis, etc. Spent my early years as an employee and eventually a broker. Then EXCEL set me free to become a freelance consultant. That's why I'm so big on learning. The more you put into it, the more you get out of it. EXCEL is the tool that keeps on giving and giving :)) Go Team!!
@@wayneedmondson1065 That is my experience too: The more you put into it, the more you get out of it. And because it is nearly infinite, so is what we can do if we study hard and learn a lot. Go Team!!!!
That's absolute excel fun Mike ... thanks alot ... we missed you for a whole week
Glad you like it. I am spending most all my hours trying to finish my book. I am on page 641 out of about 850...
@@excelisfun Wow ... i'm sure the book gonna be mind blowing ...and full of bonuses too.
@@HusseinKorish I do not know how good it will be. But I do know that it will be epic and have a lot of topics all in one book. Not for people who want the quick and easy solution, but for those how want the hows and whys and comparisons between all the tools so that you can be a smart Excel master!
@@excelisfun Have you choosed a name for the book yet ?
@@HusseinKorish It is called The Only App That Matters : )
Nice Trimmean function.
: ) : )
Mike, one favor: MORE silliness please ! 🔥 😁👌amazing !!!
amazing array addition to the formula on minute: 4:18
(Mind-blowing 😵🤯😮😁 !!! ) .... #GOTEAM !!!
Go Silly!!!!!! : ) : ) : )
Wow u r so amazing
Glad you like the videos : )
Awesome 👍👍
Glad you like it : ) : )
Awesome formula, Can I get something similar in Power query or DAX. Thank you.
This was fun.
today, in my work,I was called:"Hey excel guy, is there a way.." Istoipped him right there and said "yes", and he just wanted to filter data without filter so he could see only partially some 5 columns out of 20 byspecific criteria.
Needles today it took filter() index() sequence and rows to do what he wanted, and he said that he thought it'll be some gigantic formula :D
Awesome, you smart Excel Guy ; )
@@excelisfun thank you, I learned from best of the best :) (and i still do learn a lot) - its just satisfying when u can help
@@ExcelInstructor I agree. It is real fun to help others with Excel! It is really great that you have worked so hard to become such a go to Excel Guy, Radoslaw!!!!
@@excelisfun Thank you for your kind words, but still I have long way to go :)
First to comment! Still watching!
First Place Trophy goes to: Cary!!!!! : ) : ) : )
That was a really nice string of Excel magic tricks!!
Felt like magic!
And whenever anyone starts vectorising arguments, I instantly like the formula! :-)
BTW: can we do that substitute “array” by “(a,b,c,d)” trick where ever it says “array” in the tool tip?
That is a really neat trick! :-)
Thanks for this high quality fun!
Most array arguments will handle it. Back in about 2013 I tried to hunt for all functions that could do this, and it was a bit hit and miss. Here is what I found:
LARGE
SMALL
FREQUENCY
TRIMMEAN
PERCENTRANK
QUARTILE
RANK
RANK.AVG
RANK.EQ
PERCENTILE
AREAS
MIRR
INDEX
Probably a few others also. The weird thing is that a finance function like MIRR can do it, but IRR can not...
@@excelisfun Hi Mike.. are you sure? I was curious.. put -100, 10, 10, 10, 150 in cells H6:L6 and then tried: =IRR(H6:L6), =IRR((H6,I6:L6)) and =IRR((H6,I6,J6,K6,L6)) and all produced a result of 17.70%. It seems like it does work with IRR(). Curious if you have found otherwise, as I can immediately use this technique if it is reliable and accurate.
@@wayneedmondson1065 I will have to dig up my original test workbook from back in 2013. But I thought IRR did not work. It looks like it does : ) Go Team!!!
@@wayneedmondson1065 I am glad you said something. I found the old workbook. It was XIRR and XNPV that I could not get to work. Here are some others that I tried that did not work:
XIRR
XNPV
TEXT
MONTH
AVERAGEIF
COVAR
Here is the list that I got to work:
LARGE
SMALL
FREQUENCY
TRIMMEAN
PERCENTRANK
QUARTILE
RANK
RANK.AVG
RANK.EQ
PERCENTILE
AREAS
MIRR
INDEX
IRR
PERCENTILE.EXC
PERCENTILE.INC
PERCENTRANK.EXC
PERCENTRANK.INC
QUARTILE.EXC
QUARTILE.INC
I added these to the download workbook : )
Go Team!!!!
@@excelisfun Thanks to Mike “The Machine” Girvin for all the hard work leading up to this long list.
Good stuff !!
(And I’m sure your book is even going to be better! Can’t wait. :-)
Sir, Will you kindly make a video on Partial Match Lookup through Power Query with Some Examples?
Hi sir... Plse make some videos on cost analysis...as In interview they are asking do we know that or not
Thanks in advance
how to highlight similar items in two different list ...can we cover that part ...Thanks
I am not sure what you are asking.
@@excelisfun List 1 (fruit, vegetables, seeds, flowers) List 2 ( coconut, milk, chocolate, fruit, salad) i want to highlight "fruit" as that is common, match function works well but also i want to convert text to other languages before using match function.
Sir hope now my question is clear
@@biting.scorpio I am sorry, I do not know how to do that : ( You can try this great Excel question site: mrexcel.com/board
in huge Data How can I filter links? I want to filter some special links which have the important symbol, In filter, I didn't find any options to filter. Which function should I use? Thanks,
I am not sure how to do that. Try posting to this great Excel question site: mrexcel.com/forum
sir why have you disabled the link to worksheets for your older videos ,i was able to access it a few days ago, but i checked today and it coudnt..
I did not disable links. What link? Please tell me what link so I can investigate? What older link: I have over 10,000 file links that I have posted over a 13 year period... Maybe there is a problem with server or something.
Can you tell me the link?
@@excelisfun I just checked again upon seeing this,and its working again HOORAY,and i thought it had to do with my laptop so i tested it with other laptops and it coudnt as well,so i guess it had to do with the server or something.But its working now so,all is good sir.
@@LYCANCLANTEAM Yes!!!!
Hii
could u plz share the formula for this
let's say I have 5 numbers 10,11,12,13,14 in this I don't want to calculate (Sum )1 Bigger number (14) and 1smaller (10) Here I want is the sum of the remaining numbers (11+12+13)=? on excel
What is that percentage?? Is it usual percentage calculations like I have 100 line items and 10 line items min and max which makes it 10%???
Here is the note I showed in the video and in the download workbook:
TRIMMEAN calculates the mean taken by excluding a percentage of data points from the top and bottom tails of a data set.
Percent = The fractional number of data points to exclude from the calculation.
For example, if percent = 10%, 4 points are trimmed from a data set of 40 points: 2 from the top and 2 from the bottom of the set.
Very nice Mike, Trimmean is used in some Olympic sports where a jury gives marks. The lowest and highest are not taken into account. It is also used in SPC where you remove the outliers.
@@barttitulaerexcelbart9400 Cool! Sports and Excel go together so well : )
Can you please help me out
3000-40000
50000-60000
400000-5000
How to avarage this value
If I go to manually is like
=3000+40000/2
So on
If any formula?
Please share
I want to speak with u