Ignore #N/A in Array Formula

[r1998]

Dear Friends and Respected Seniors,
I have this data in A1-B10
[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]28[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]88[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]33[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]41[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]58[/TD]
[/TR]
</tbody>[/TABLE]

I want to find the maximum value from column B wherever column A has value 1, so in C1 I am using

=MAX(IF(A:A=A1,B:B))

with control+shift+enter
I am getting the correct answer as 40.
But if column B has any #N/A values, I get #N/A in C1, that is, if I change B8 from 33 to #N/A, I get #N/A in C1.
How can I ignore #N/A values in above formula.
Can anyone please kindly help me.
Awaiting your replies.
Thank you.

 

[Marcelo Branco]

First of all, for the sake of performance avoid references to entire columns in array formulas, so try something like this

=MAX(IF(A1:A100=A1,IF(ISNUMBER(B1:B100),B1:B100)))
Ctrl+Shift+Enter

M.

 

[r1998]

Thank you Marcelo Branco Sir,
your formula worked perfectly
Henceforth, I will not use reference of entire columns
Thank you.

 

[Marcelo Branco]

You are welcome. Glad to help


M