#NUM! make it zero

Question

I have a very simple spreadsheet where I pick out the top 5 scores in matrix. If there is no score (eg only 3 scores posted) then Score 4 comes up as #NUM!. Is there anyway to replace this with a Zero? Thanks

Answer 1

Wrap your formula in IFERROR:

=IFERROR(your formula here, 0)

Answer 2

Brilliant - just what I needed! Thanks.

Answer 3

This formula willy dynamically capture all numeric values in column A and always return up to the top 5 highest results. No need to drag formula or adjust as more/less values created. Also my solution will ignore blanks and text entries. If you want unique values change Sort to be (Unique(zCleanRange),1,-1)

=LET(fullRange,A:A,zCleanRange,FILTER(fullRange,ISNUMBER(fullRange)),TAKE(SORT(zCleanRange,1,-1),5))

Answer 4

If the scores are in column A, starting in A1, place In B1 and copy down:

=IF(A1="";"";LARGE(A$1:A$20;COUNT(A$1:A1)))

where 20 is the maximum number of scores that can be entered in column A; adjust this if necessary.