• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find average of highest values

Isaac Garcia

New Member
Hello,

I need to average the highest scores only of each name in the attached file.

For example, Joe Schmoe should be 100 since his highest value is 100.
Jane Doe should be 81.70 (Average of 84.34 and 79.07) and so on.

I have more names than this which is why I need an easier way to figure this out.

OR

If it's easier, is there a way to find the lowest value and have the whole row removed? Then I could use a pivot table to find the average of the remaining. Thanks!
 

Attachments

  • Sample File.xlsx
    8.1 KB · Views: 11
Do you mean you want the average of all results excluding the lowest score for each person?
 
upload_2017-7-20_11-4-58.png

As per the described result, it appear the Average of Top 2 score for each person and exclude the 0 result.

In E2 array formula copy down :

=AVERAGE(IFERROR(LARGE(IF((A$2:A$8=D2)*(B$2:B$8>0),B$2:B$8),{1,2}),""))

p.s. Array formula to be confirmed by pressing CTRL+SHIFT+ENTER 3 keystrokes together in stead of just ENTER

Regards
Bosco
 

Attachments

  • AveOfTop2Score.xlsx
    9.6 KB · Views: 7
Hi, Isaac Garcia!

Welcome to Chandoo forums!
Checked this yet?
http://chandoo.org/forum/forums/new-users-please-start-here.14/

About your question, please define "highest values". For Joe you only take 100 but for Jane you take 84.3373 and 79.0698 but not 66.3366. Which is the criteria?

Regards!

Thanks for the reply! I guess a better way to put it is I would like to find the average of all scores excluding the lowest score for each person
 
View attachment 43580

As per the described result, it appear the Average of Top 2 score for each person and exclude the 0 result.

In E2 array formula copy down :

=AVERAGE(IFERROR(LARGE(IF((A$2:A$8=D2)*(B$2:B$8>0),B$2:B$8),{1,2}),""))

p.s. Array formula to be confirmed by pressing CTRL+SHIFT+ENTER 3 keystrokes together in stead of just ENTER

Regards
Bosco

Thanks for the suggestion! My apologies if the question wasn't worded the way I intended. I would like to find the average of all scores excluding the lowest score for each person. So if 1 person only has 2 scores, I just need their highest score (Joe Schmoe has 100 and 0, exclude the lowest score which is 0, so he should just have 100. John Smith has 90.69 and 50, exclude the lowest score which is 50, so he should just have 90.69.) Thanks again!
 
Hi ,

What if there are multiple lowest scores , as for instance if in a set of 7 scores , there are 3 identical scores which are the lowest ?

Should the average be of 6 scores , excluding one lowest score , or should it be of 4 scores , excluding the 3 identical lowest scores ?

Narayan
 
Thanks for the suggestion! My apologies if the question wasn't worded the way I intended. I would like to find the average of all scores excluding the lowest score for each person. So if 1 person only has 2 scores, I just need their highest score (Joe Schmoe has 100 and 0, exclude the lowest score which is 0, so he should just have 100. John Smith has 90.69 and 50, exclude the lowest score which is 50, so he should just have 90.69.) Thanks again!

Average of scores and exclude the lowest score for each person .

In E2 array formula copy down :

=AVERAGE(LARGE((A$2:A$8=D2)*(B$2:B$8),ROW(INDIRECT("1:"&COUNTIF(A$2:A$8,D2)-1))))

p.s. Array formula to be confirmed by pressing CTRL+SHIFT+ENTER 3 keystrokes together in stead of just ENTER

Regards
Bosco
 

Attachments

  • AveScoreExclLowest.xlsx
    9.7 KB · Views: 3
Hi ,

What if there are multiple lowest scores , as for instance if in a set of 7 scores , there are 3 identical scores which are the lowest ?

Should the average be of 6 scores , excluding one lowest score , or should it be of 4 scores , excluding the 3 identical lowest scores ?

Narayan

That's a great question! It would be the average of the 6 scores, excluding the lowest one.
 
Average of scores and exclude the lowest score for each person .

In E2 array formula copy down :

=AVERAGE(LARGE((A$2:A$8=D2)*(B$2:B$8),ROW(INDIRECT("1:"&COUNTIF(A$2:A$8,D2)-1))))

p.s. Array formula to be confirmed by pressing CTRL+SHIFT+ENTER 3 keystrokes together in stead of just ENTER

Regards
Bosco

Thanks! This looks like what I need, but I end up getting the #REF! error and Can't figure out why. I added one name and get the error. Thanks for your time!
 

Attachments

  • AveScoreExclLowestWithAddedName.xlsx
    12.9 KB · Views: 6
Thanks! This looks like what I need, but I end up getting the #REF! error and Can't figure out why. I added one name and get the error. Thanks for your time!
1] The problem is fixed by extend the range to suit with the added names.

=IF(D2="","",AVERAGE(LARGE((A$2:A$100=D2)*(B$2:B$100),ROW(INDIRECT(("1:"&COUNTIF(A$2:A$100,D2)-1))))))

2] See attached revised file

Regards
Bosco
 

Attachments

  • AveScoreExclLowestWithAddedName(1).xlsx
    13.1 KB · Views: 7
You can also use in the #12 layout (CTRL+SHIFT+ENTER and not just ENTER):
=IF(D2="","",(SUMIF($A$2:$A$11,D2,$B$2:$B$11)-MIN(IF($A$2:$A$11=D2,$B$2:$B$11,9E+307)))/(COUNTIF($A$2:$A$11,D2)-1))
This formula would fail if there's single entry case in your data.
 
Back
Top