Ignoring "#DIV/0!" cells in a formula that has a MAX formula embedded

[Rnkhch]

Hello,

I have a column of scores some of which are #DIV/0!. In the adjacent column, I want to have each score as a percent of the maximum score in the original column. So in the second column, I use the following formula:

B2=($A2/MAX($A$2:$A$433))*100

If there are no cells with #DIV/0!, the formula works beautifully. I tried the ISERROR, IF, etc tricks that I found here or in other forums, but they all seem to only work with standalone MAX formulas, but not when the MAX is embedded in another formula/operation.

I would highly appreciate any help.

Thank you!

 

[PaddyD]

done longhand so brackets might be off but the below should work:

=($A2/MAX(if(isnumber($A$2:$A$433),$A$2:$A$433)))*100

...entered wit control + shift + enter, not just enter

 

[Rnkhch]

Thanks a lot!

control + shift + enter is a miracle ​

 

[PaddyD]

yep - search for 'array formulas' in excel help (and elsewhere) for more info.

https://support.office.com/en-us/ar...formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7

 

[Eric W]

If you have Excel 2010 or newer, this also works:

B2=($A2/AGGREGATE(4,6,$A$2:$A$433))*100

No CSE needed.

 

[PaddyD]

good addition - I'm always forgetting about aggregate()

 

[Rnkhch]

Thanks for the AGGREGATE function! It is so versatile and useful. Great to learn.