SOLVED

Help with formula results

Copper Contributor

Hi Guys

Please find attached a spread sheet where the results do not seem to make mathematical sense. I asked for the average of the top ten highest scores but the results do not make sense. Players  with averages of over 30 points and from the results  is impossible. Any help very much appreciated

=SUMIF(L3:AJ3,">="&LARGE(L3:AJ3,10)) - this does not give the sum of the top ten scores out of 15 games played

8 Replies
I do not see any attachment.

@ianshaw2  wrote:  ``Please find attached a spread sheet``

 

Missing!  Click "browse files to attach" near the Post button.  If you cannot use that (why not?), upload an example Excel file that demonstrates the problem to a file-sharing website, and post a download URL that does not require that we log in. I like box.net/files; others like dropbox.com. You might like onedrive.live.com because it uses the same login as this forum.  If the forum does not permit you to post URLs yet, edit the URL manually so that the forum does not recognize it.  For example, the URL for this thread is techcommunity dot microsoft dot com /t5/excel/help-with-formula-results/m-p/3752868#M182660.

 

Awesome thanks.....I would love to send/load the file in question but can't seem to find how to do that - any thoughts

@Joe User 

Here we go file attached please look at 

52,58,119,145

Cheers and thank for the help

@Harun24HR here we go so sorry about that

@ianshaw2 The formula works to my Excel. What problem are you facing with it? What is your excel version? What would be your desired result?

best response confirmed by Hans Vogelaar (MVP)
Solution

@ianshaw2  wrote:  ``SUMIF(L3:AJ3,">="&LARGE(L3:AJ3,10)) - this does not give the sum of the top ten``

 

 

You are correct.  But because you do not identify any cells in question and explain the expected results, I am not sure I have identified the problem of interest.

 

ERRATA....  I had not seen your PM, in which you do indeed identify some of the rows that I flag as erroreous below.

 

The SUMIF formula works in most cases only by coincidence either because the 10th largest value is zero, or because there are indeed no more than 10 values >= the 10th largest value.

 

However, the SUMIF produces the wrong result when there are multiple cells that match the 10th largest value.

 

This is demonstrated below.  See the attached file for details.

 

JoeUser_0-1677402810258.png

 

 

In row 158, for example, the 10 largest values are {24,21,20,29,32,24,25,27,25,24}.  But since 20 is the 10th largest, SUMIF includes the 3 duplicate 20s as well.

 

However, the average in I158 always divides by 10, not 13.

 

The correct formula is demonstrated in AL158, to wit:

 

=SUM(LARGE(L158:AJ158, {1,2,3,4,5,6,7,8,9,10}))

 

In Office 365 Excel and some later versions of Excel, the array constant {1,...,10} can be replaced with the expression SEQUENCE(1,10).

 

-----

 

The formula in J3 is completely wrong, to wit:  =A60:AC60.

 

It appears to return the value in J60, due to the implicit intersection feature.

 

 

Just Awesome thank you so much for your help -The golfing population of New Zealand owe you a free game if you are ever over in NZ.
Thank you
Again
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@ianshaw2  wrote:  ``SUMIF(L3:AJ3,">="&LARGE(L3:AJ3,10)) - this does not give the sum of the top ten``

 

 

You are correct.  But because you do not identify any cells in question and explain the expected results, I am not sure I have identified the problem of interest.

 

ERRATA....  I had not seen your PM, in which you do indeed identify some of the rows that I flag as erroreous below.

 

The SUMIF formula works in most cases only by coincidence either because the 10th largest value is zero, or because there are indeed no more than 10 values >= the 10th largest value.

 

However, the SUMIF produces the wrong result when there are multiple cells that match the 10th largest value.

 

This is demonstrated below.  See the attached file for details.

 

JoeUser_0-1677402810258.png

 

 

In row 158, for example, the 10 largest values are {24,21,20,29,32,24,25,27,25,24}.  But since 20 is the 10th largest, SUMIF includes the 3 duplicate 20s as well.

 

However, the average in I158 always divides by 10, not 13.

 

The correct formula is demonstrated in AL158, to wit:

 

=SUM(LARGE(L158:AJ158, {1,2,3,4,5,6,7,8,9,10}))

 

In Office 365 Excel and some later versions of Excel, the array constant {1,...,10} can be replaced with the expression SEQUENCE(1,10).

 

-----

 

The formula in J3 is completely wrong, to wit:  =A60:AC60.

 

It appears to return the value in J60, due to the implicit intersection feature.

 

 

View solution in original post