Forum Discussion
Solved
Help with formula results
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
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.
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.
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.
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
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
- I do not see any attachment.
