How to find max value with empty cells & #div/0!

Hello, I have attached an example chart that I use regularly for work. Is there a way to find the max value of this chart, or even the top 5 max values (would be ideal but will settle for just one max figure for now if it's not possible)? I have tried numerous things but it seems that the empty cells and/or the #DIV/0! cells are causing issues for me. Also, is there a way to have a separate cell next to the max number that references the actual cell that this max value is found (to save me time from having to look through each cell individually until I find that max number the formula spits out)?

Thank you in advance

Pls try , select Area A37:A42 array formula
{=LARGE(IF(--ISNUMBER($B$2:$AH$34),$B$2:$AH$34),ROW(1:5))}

Originally Posted by Pepe Le Mokko

No fancy array formulas needed

In both cases you can use the AGGREGATE function
Max of a range =AGGREGATE(4,6,B2:AH35)
5 largest =AGGREGATE(14,6,(B2:AH35),1) replace the last paramater with2 , 3 , 4 , 5 to get the other top 5 values

Thanks for your advice, because I don't have a new version MSO, so I can only solve the problem with a lower version method

Thank you for the replies.

Unfortunately the Aggregate suggestion did not work for me as it just returns a #NAME?. I'm guessing the fact that I'm running an older version of Excel (2007) may be causing this, and although I would rather not use an array formula, I'm beginning think that it's unavoidable at this point given the older version I'm running.

I tried the array formula and it worked! Thank you

A greeting

a solution could be
select B2; AH34
from the Find menu
# DIV / 0!
replace
0
Then I made a table in C36
=LARGE($B$2:$AH$34,ROW(A1))

to go down for the first 5 numbers
and alongside the cell address that contains this valu

=ADDRESS(SUMPRODUCT(($B$2:$AH$34=C36)*ROW($B$2:$AH$34)),SUMPRODUCT(($B$2:$AH$34=C36)*COLUMN($B$2:$AH$34)),4)

Thank you, that worked as well!