Max/Min IfError Problem

[Montoro22]

Hello,

I'm trying to put in a formula to return the highest number but the problem is that I have a vlookup function providing the values for the range im pulling the max from. Therefor every so often it will return #DIV/0! So I currently have a max and iferror combined formula that looks as follows:

{=MAX(IF(ISERROR(I2:I44), "", I2:I44))} and the same thing with min and average.

But the problem is that I want to drag the formula down so when i put new entries in it will calculate them too. But sense I have the iferror formula it is taking into account the blank cells and counting them as zeros I believe and throwing off the number. Any idea for a formula that still won't count blank cells as zeros but keep those with the error in them to be counted as zero?

Thanks it's much appreciated,

Rob

[singerj6]

instead of using IF and ISERROR, use IFERROR

=IFERROR(MAX(I2:I144),"")

[Montoro22]

Actually is there anyway I could have it ignore the error's in the calculations as if they were blanks?

---------- Post added at 01:57 PM ---------- Previous post was at 01:52 PM ----------

For the Min function that is

[icestationzbra]

how about the following array / CSE formulae?

=MAX(IFERROR(A1:A10,0))

and

=MIN(IFERROR(A1:A10,9^9))

[NBVC]

Do you mean a formula like:

=IF(COUNT(I2:I44),AVERAGE(I2:I144),"")

[daddylonglegs]

Perhaps investigate the AGGREGATE function (available in Excel 2010 only), e.g.

=AGGREGATE(4,6,I2:I44)

4 denotes MAX, 6 tells it to ignore errors, blanks will also be ignored (not treated as zeroes). Change the 4 to 5 for MIN and 1 for AVERAGE

[Montoro22]

NBVC, the formula is good but for the columns where there are errors in them like #DIV/0!, it returns that for the average

[NBVC]

Why are there Div/0 errors in those columns? You should get rid of them at source... adding error handler to formulas there....

[Montoro22]

^Daddylonglegs that works perfect, thanks so much!

NVBC, how do you do error handlers?

[NBVC]

using functions like IFERROR(), ISNA(), etc are error handlers....

[Montoro22]

Gotcha okay thanks!