Return largest values from data table using LARGE function

[ormy28]

Hi

I have a football stats spreadsheet which contains a data table called tbl_Fixtures. Within this there is a column called Goal Index.

On another worksheet, I want to create a list of the top 10 values within Goal Index. I am trying to achieve this by entering the below:

=LARGE(tbl_Fixtures[[#Data],[Goal Index]], 1)

The number 1 will be replaced by 2, 3, 4, etc in formulas further down.

However, this simply returns #DIV/0! and I have no idea why. Should I be using the item specifier #DATA in this case?

Maybe LARGE can't be used on a data table?

Thanks

 

[Fluff]

Make sure that you don't have #DIV/0! errors in the Goal Index column.

 

[jasonb75]

That formula will not return #DIV/0 alone. The only likely error would be #NUM! if there were less numeric values in the table range than the specified k value.

Any error other than #NUM would be caused by unresolved errors in the data table.

 

[Fluff]

Whilst it would be best to remove the errors from that column, another option would be

=INDEX(SORT(FILTER(tbl_Fixtures[Goal Index],NOT(ISERROR(tbl_Fixtures[Goal Index]))),,-1),SEQUENCE(10))

 

[jasonb75]

Whilst it would be best to remove the errors from that column, another option would be

=AGGREGATE(14,6,tbl_Fixtures[Goal Index],SEQUENCE(10))

 

[Fluff]

Definitely overthought that.

 

[jasonb75]

Maybe just a little @Fluff ?

 

[ormy28]

Oh dear, both of you were correct with there being an error in the original data. How annoying (and embarrassing)!

Thanks for your help.

 

[Fluff]

Glad we could help & thanks for the feedback.