Archery & Excel: Large, Offset, Sum = Missed

[Shaz_au]

Hi,
I'm hoping a guru here can help before my hairline recedes further! I've been trying to create an electronic score sheet for an Archery championship. At each event a competitor gets a score that consists of a total and the number of bullseyes they hit. Example: a total of 99 and 6 bullseyes. They have 20 events for the year, and the best 12 events' totals count towards your championship aggregate.

I've found I can return the sum for the best 12 event totals using the following formula:
SUM(LARGE(J4:J26,{1,2,3,4,5,6,7,8,9,10,11,12}))

What I can't work out is how to return the associated bullseye counts for those best 12 event totals!

I've tried using: Sum(large(offset(j4:j26,0,1),{1,2,3,4,5,6,7,8,9,10,11,12} but this returns the sum of the top 12 bullseye counts for the year rather than returning the sum of the bullseyes that relate to the events with the highest total score.

I have looked at some of the other posts here regarding the offset and large formulas but I couldn't find any that with my skills I could adapt to work. Any help would be greatly appreciated.

Thanks,
Shaz

 

[Andrew Poulsom]

Welcome to MrExcel.

Try:

=SUM(IF(J4:J26>=LARGE(J4:J26,12),K4:K26))

confirmed with Ctrl+Shift+Enter. It's an array formula that Excel will surround with curly braces {} if entered correctly.

 

[Shaz_au]

Hi Andrew,
Thank you for the welcome and the reply.
I used the shift+ctrl+enter for the formula and it has calculated a number but it doesn't appear to be right. with my sample data it is returning 172 when I was expecting to it to return 132.

The sample data:
column J:

99
100
96
93
98
98
0
95
94
99
98
100
100
97
98
0
100
100
99
99

<colgroup><col></colgroup><tbody>
</tbody>

column: K

12
11
7
7
7
11
0
6
5
10
7
13
12
8
9
0
13
13
10
11

<colgroup><col></colgroup><tbody>
</tbody>

Thanks!

 

[Andrew Poulsom]

For me the formula returns 139:

Excel 2010
	J	K	L	M
4	99	12	139	12
5	100	11		11
6	96	7		FALSE
7	93	7		FALSE
8	98	7		7
9				FALSE
10	98	11		11
11	0	0		FALSE
12	95	6		FALSE
13	94	5		FALSE
14	99	10		10
15				FALSE
16	98	7		7
17	100	13		13
18	100	12		12
19	97	8		FALSE
20	98	9		9
21				FALSE
22	0	0		FALSE
23	100	13		13
24	100	13		13
25	99	10		10
26	99	11		11
Sheet1

Cell Formulas
Range	Formula
M4	=IF(J4>=LARGE(J$4:J$26,12),K4)
L4	{=SUM(IF(J4:J26>=LARGE(J4:J26,12),K4:K26))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Shaz_au]

Thanks again for the reply! I apologise, your formula was returning 139 for me too, unfortunately I still think it should be 132. It looks to me like it has included 13 events above, probably because there was duplicate totals.

Also, where there is duplicate totals the formula needs include the total that has the highest bullseye count.

Confused yet?

 

[Andrew Poulsom]

So what should the result be for your sample data? Which specific numbers in column K should be summed?

 

[Shaz_au]

The top 12 events are:

100	11
100	13
100	12
100	13
100	13
99	12
99	10
99	10
99	11
98	7
98	11
98	9

<colgroup><col span="2"></colgroup><tbody>
</tbody>

The totals should be:

1190

132

<colgroup><col span="2" width="64"></colgroup><tbody>
</tbody>

I think the formula is picking up that there was two 98 with 7s and including both in the bullseye count.

 

[Andrew Poulsom]

How about?

=SUM(RIGHT(LARGE((J$4:J$26&TEXT(K$4:K$26,"00"))+0,ROW(A1:A12)),2)+0)

confirmed with Ctrl+Shift+Enter.

 

[Shaz_au]

Wow! that works, I will test it with some more data and confirm back! Thank you so much for your help

I have no idea how this formula works, any chance you can give me a brief explanation please?

Thanks again,
Shaz

 

[Andrew Poulsom]

To see how the formula works use the Evaluate Formula tool.