Hi All,
I know the trimmean formula works by removing certain values from the bottom and top of a range.
What I need is a formula which only takes away from the top of the range. I have put an example below.
If the trimmean formula isnt capable of calculating is there an alternative solution
I want to average the bottom 90% of values.
1 Include
2 Include
5 Include
6 Include
8 Exclude
2 Include
3 Include
4 Include
2 Include
1 Include
A solution that uses a helper column assuming the numbers are in A1:A10 paste the following formula in B1 then double click down:Paste the following formula in C1:
=RANK.EQ(A1,A$1:A$10,1)<90%*COUNT(A$1:A$10)Let me know if you have any questions.=AVERAGEIFS(A1:A10,B1:B10,TRUE)
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
HI Jetemc
Sorry I should of said at the outset, I am using excel 2003 unfortunately that doesnt contain the Rank.EQ formula.
Do you know of another way around this?
Thanks for your time looking into this
Hi Jetemc sorry I was mistaken we are using excel 2007
I get the same results when testing with RANK so hopefully just changing the formula to read as follows in B1 and down will work for you:
EDIT: In addition I changed % in the formula in B1 and down so that it reads:Let me know if you have any questions.=RANK(E1,E$1:E$10,1)<90.00000000001%*COUNT(E$1:E$10)
Last edited by JeteMc; 02-25-2016 at 02:09 PM. Reason: Correcting an error in formula
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks