[ms office - 2010] Stop the error message!

[kennysarmy]

I've got the following date in excel:

 

Week 1 5 3 15

Week 2 17 1 21 20

Week 3 18 2

Week 4 12 3

Week 5 15 4 13

Week 6 17 5

Week 7 16 6

Week 8 20 7

Week 9 13 8

Week 10 12 9 14

Week 11 9 10

Week 12 17 11

Week 13 18 12

Week 14 14 13

Week 15 14

Week 16 15

Week 17 17

Week 18 18

Week 19 13 6

Week 20 14

Week 21 16

Week 22 15

Week 23 15

Week 24 17

LOWEST 15 1 #NUM! #NUM!

 

I'm trying to find out the 13th lowest score or put n/a if there is yet to be 13 entries:

 

I get a result from my formula =LARGE(AK8:AK31,13) when there is 13 entries but as you can see it gives #NUM! when there are less.

 

How can I amend the formula to include an error check?

 

Thanks.

[Steve21]

=IF(COUNTA(E1:E24)>=13,LARGE(E1:E24,13),"Not enough")

 

etc

 

Steve

[Seb1780]

iferror(LARGE(AK8:AK31,13),"There are not 13 entries")

[kennysarmy]

iferror(LARGE(AK8:AK31,13),"There are not 13 entries")

 

Thanks, I just got there myself

 

The next problem!

 

I need the total of the top 13 values in each column, if there are less than 13 values in the column I need the total of that.

 

Again, the formula =IFERROR(SUMPRODUCT(LARGE(AK8:AK31,{1,2,3,4,5,6,7,8,9,10,11,12,13})),"") works, but it gives N/A rather than adding up say 7 values if that's all there are in the column...

 

Is there another way to SUM the top n values in columns where some columns don't have all the data filled in?

[Seb1780]

I need the total of the top 13 values in each column, if there are less than 13 values in the column I need the total of that.

 

Replace the "" in your formula to make it sum the range you want e.g.

 

=IFERROR(SUMPRODUCT(LARGE(AK8:AK31,{1,2,3,4,5,6,7,8,9,10,11,12,13})),sum(range:range))

[kennysarmy]

Replace the "" in your formula to make it sum the range you want e.g.

 

=IFERROR(SUMPRODUCT(LARGE(AK8:AK31,{1,2,3,4,5,6,7,8,9,10,11,12,13})),sum(range:range))

 

So simple....

 

Thanks.