Formula produces wrong result of top ten scores as it appears to dupicate the highest numb

Formula produces wrong result of top ten scores as it appears to duplicate the highest numbers. The formula is as follows and is used to give the 10 best scores from more than ten games played. =SUMIF(L3:AJ3,">="&LARGE(L3:AJ3,10)) This formula was created using Excel in Office Package 19.

10 means nothing. What Excel PRODUCT do you want this to work with (O365, Excel 2021, 2019, etc, etc)? Please amend your profile appropriately.

One guess, pending info on Excel product:

=IFERROR(SUM(TAKE(SORT(TOROW(L3:AJ3,1),,-1,TRUE),10)),"")

will work for O365.

With file!

Cut your sample down to 10 rows and manually calculate your expected results.

the product is Excel in an Office 19 Package.

Please update your forum profile NOW.

&LARGE(L161:AJ161,10)) gives you the tenth largest value. The SUMIF then totals all the values that are greater than or equal to that value.

Try, copied down:

Formula:

Please Login or Register  to view this content.

Edit: the tenth largest value in this row is 20. So, SUMIF gives you all the values greater than or equal to 20. There are three 20s. So the score is 40 more than it should be.

Regarding posts #17 and #18, guessing you need to Array-Enter (Commit) the formula with Ctrl-Shift-Enter rather than just Enter.

Thank you.

Now, in the case of row 145, you have 11 matching items:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
145	25.7	294	334	27	27	25	25	29	25	26	21	27	28	30	19	25

Sheet: Master

How do you want Excel to whittle this down? By value or by date?

Please now respond to post #20.

Because there are 11 items that meet the criterion, so it's adding 11 scores, not 10.

If you want it to add the most recent top 10, you can use this (but it won't ignore 0):

=SUM(LARGE(IF((L3:AJ3<>"")*COLUMN(L3:AJ3)>=LARGE((L3:AJ3<>"")*COLUMN(L3:AJ3),10),L3:AJ3),{1,2,3,4,5,6,7,8,9,10}))

This needs entering as an array formula. I have done this for you in the attachment.

To ignore 0 scores, use this:

=SUM(LARGE(IF((L3:AJ3<>0)*COLUMN(L3:AJ3)>=LARGE((L3:AJ3<>0)*COLUMN(L3:AJ3),10),L3:AJ3),{1,2,3,4,5,6,7,8,9,10}))

I've added this version on the right.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Please do as I asked (at Post 7). Cut the sample back to 10 rows and provide the expected answers, calculated manually. 23 Posts in and no-one fully understands what you are saying. Excel can do it... you just need to know what question to ask.

Currently, we don't.

Let's see if post #24 has solved it first, then, if necessary, we can look at a smaller set of sample data.

The problem is that the OP has not appreciated WHY the results are 'inflated' in some rows, so they won't have considered how they want to get round this. Hopefully one of the two suggestions above will suffice.

DJ provide a result that seemed to work but when I copied and dragged down it just showed the highest score of the rows not a total of the ten highest. Not sure why it did that?

You had dragged it down WITHOUT first entering it with CTRL+SHIFT+ENTER.

Simple Q.

Which of the 15 values here should be added together to give your expected result.

This question has already been asked in post #20, with slightly different wording. But I'll leave it to you now.

I think it may be, but it depends on how the OP wishes to decide which of the values to use if there are more than 10.

Anyway - let's wait and see.

Posts #15 and #16 solve the problem. They produce consistent/comparable results. Post #24 does not produce results consistent with the other two formulae, although the second version is close (perhaps data related?).

Please see the updated sample file for comparison.

The problem, Trevor, is that we don't yet know what the expected results are, so it's ALL guesswork!!!

But my formulae are date-related, so they focus on values from the most recent matches. If that's NOT how the OP wants to do it (and we don't know yet), then the other formulae will be the answer. My formulae ARE consistent based on what they are intended to do.

I have a feeling that the OP wants the sum of the highest 10 unique values... which is odd.

Then, someone with 15 25s and nothing else would score 25...

whereas someone else with 14 25s and a 26 would score 51.

32 posts and not even the most basic definition of what is required....

Given a set of numbers:
20 20 20 20 20 20 20 20 20 20 20 20 20 19 19 19 19 19 18 18 18 18 18 18 18

The first 13 are all 20. Surely, the top 10 largest are all then 20, giving a total of 200?

@Glenn

Yes - and there are at least THREE other ways of interpreting this. This is why sample workbooks should ALWAYS include expected results. Whatever it is would be far easier with Excel 365.

The first 13 are all 20. Surely, the top 10 largest are all then 20, giving a total of 200?

Yes, but what if we need to consider the most recent non-zero values, not the first 10? There's too much uncertainty here.

Yes, but what if we need to consider the most recent non-zero values, not the first 10? There's too much uncertainty here.

Fair comment

You still haven't defined/explained all the rules that govern the selection. Is it the ten largest from the most recent 18 scores?

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

Please try in J3 and copy down:

Formula:

Please Login or Register  to view this content.

@Hans: see post #15:

Formula:

Please Login or Register  to view this content.

@GameChanger: your solution isn't really different to post #15 . . . which seems to have been discounted along with DJ's solution.

And, to my mind, the OP has still not provided a definitive set of rules for the calculation or the manually calculated results.

Originally Posted by Golfer2024

Hi Ali
Not sure if you are awake in the UK but it has not worked at all and now the results are pretty much incorrect. Is there a solution to this or should I just manually do the scores? A big ask from my end as it will be some 3000 calculations......can we fix this?

Ian

Just do the manual calculation for the sample file and we can try to provide a solution that matches them.

"Doesn’t work" does not help anyone.

@Golfer2024: Thanks for the rep . Please help us to help you by manually calculating the results you expect for the sample file and explain your logic.

Originally Posted by TMS

@Hans: see post #15

Sry @Trevor, I overlooked post #15.

As others have said, it is time for you to provide a sample set with the following:

1. A manual calculation on each row showing the result you expect.
2. The cells highlighted that need to be included in the sum.

Provide 10-20 rows of data and make sure to include some of those awkward lines such as row 145.

"Doesn't work at all" is not going to help any of us to diagnose the issue and put it right.

You also need to EXPLAIN in WORDS the logic that you wish to apply where there are MORE THAN 10 cells in the range that meet the top 10 criterion: this is the most important thing, along with the manually calculated results and highlighted cells.

It's over to you now. Provide what we have asked for.

Golfer, please stop messing around with meaningless phraes like "it doesn't work" and provide some expected results, as requested MULTIPLE times by MULTIPLE helpers here.

Originally Posted by TMS

@GameChanger: your solution isn't really different to post #15 . . . which seems to have been discounted along with DJ's solution.

And, to my mind, the OP has still not provided a definitive set of rules for the calculation or the manually calculated results.

Well there is one BIG difference. In earlier versions, post # 15 must be entered with CSE, mine doesn't require that.

As he has already said post #15 did NOT work and I'm not confident what version he has, I thought that just may be the issue.

Guys - can we ALL just WAIT now until the OP has provided what was requested specifically in post #50. Arguing amongst ourselves is not going to help and is only going to cloud the issue further.

Thanks.

@GameChanger: not arguing But your profile says you are using 365 . . . so you won't ever need to CSE a formula. Nor do I. But the OP may have to. If you look at the file I uploaded, you'll probably see that none of the formulae are array-entered. And other than Ali's, they all produce the same results.

To be fair, I can’t test it because I am away from home and I don't have access to an old version of Excel.

There's no point in testing ANYTHING until we know what the OP really wants.

I have asked everybody please to refrain from responding here until we get the workbook we have asked for from the OP. Please don't ignore this request. Thank you.

Guessing we might have frightened the OP off .

If the OP has been frightened off by a polite and clear request in post #50 to provide annotated data and expected results, then so be it. If he has taken offence to some of the less polite and unnecessarily sarcastic comments in this thread, then that is a shame.

I've asked repeatedly for members not to comment again until the OP provides what we need. If ANY member continues to ignore my instruction, then I shall remove their posts until that information from the OP has been forthcoming.

What is so hard about summing the top ten scores?

Because that solution was rejected as wrong by the OP early in the thread. Please stop the finger pointing. Everybody here is doing their best to work out EXACTLY how the OP wants this to work. That still is not clear.