LinkBack URL
About LinkBacks
Hello All,
I would appreciate anyone's help to write a Macro or formulas
how to Sum and Count 10% of the largest numbers, 10% of the Smallest and 60% of the middle
in an array of numbers,
Thank you.
Hello All,
I would appreciate anyone's help to write a Macro or formulas
how to Sum and Count 10% of the largest numbers, 10% of the Smallest and 60% of the middle
in an array of numbers,
Thank you.
Let's assume your numbers are in A column
Count is easy because it is 10%+10%+60% = 80% of all numbers
Or if used separately just 10% of all etc.
Formula:
Please Login or Register to view this content.
sum is a bit tougher.
for top 10% or bottom 10%
Formula:Please Login or Register to view this content.
for middle 60%:
(we drop 40%: 20 at small and 20 at large side)Formula:Please Login or Register to view this content.
if neededed just add all 3.
Best Regards,
Kaper
Thank you very much, Kaper.
Formulas are working, only I did not test them for accuracy-)
I appreciate your help!
Hello,
How could we calculate an average number of those formulas?
For example, there are 300 rows in 2 columns. So, we selected top 10% and now we need to find out a average number of those top 10%. The same with bottom 10%,
and with middle 60%.
=SUMIF(A:A,"<="&SMALL(A:A,COUNT(A:A)*10%))
=SUMIF(A:A,">="&LARGE(A:A,COUNT(A:A)*10%))
=SUMIFS(A:A,A:A,">="&SMALL(A:A,COUNT(A:A)*20%),A:A,"<="&LARGE(A:A,COUNT(A:A)*20%))
Thank you.
Using respective COUNTIF or countifS (as we have the sum, then divided by count return average).
It will be more accurate tnen using 10%*COUNTA(A:A) etc.
For instance:
Formula:Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
etc.
Thank you very much, Kaper-
Glad to hear that. If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Ok. Thanks again-
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks