Ignore #N/A in Array Formula

[r1998]

Dear Friends and Respected Seniors,
I have this data in A1-B10

1	28
2	20
3	80
3	70
2	65
1	40
2	88
1	33
3	41
2	58

<tbody>
</tbody>

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