Ignore #N/A in Array Formula

r1998

Board Regular
Joined
Sep 9, 2018
Messages
106
Dear Friends and Respected Seniors,
I have this data in A1-B10
128
220
380
370
265
140
288
133
341
258

<tbody>
</tbody>

I want to find the maximum value from column B wherever column A has value 1, so in C1 I am using
Code:
=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.
 
Last edited:

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
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

Board Regular
Joined
Sep 9, 2018
Messages
106
Thank you Marcelo Branco Sir,
your formula worked perfectly (y)
Henceforth, I will not use reference of entire columns (y)
Thank you. (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,633
Messages
5,523,995
Members
409,555
Latest member
TIPSAREA

This Week's Hot Topics

Top