Formula ISRROR with a MIN function

Microsoft Excel 2003 (full product)
August 20, 2010 at 19:39:16
Specs: Windows 7
I need to incorporate the following array with an "iserror".

Thank you.

See More: Formula ISRROR with a MIN function

Report •

August 21, 2010 at 04:57:04

What is generating the error.

Is there an error value in one of the source cells in columns C, D or O.

Or is the error generated by the formula itself - unlikely because MIN is tolerant of empty cells and non-numeric values.

If your array formula returns an error what are you hoping to display instead.


Report •

August 21, 2010 at 18:21:18
If the "CANF" is not there (which can be the case, the MIN shows 01/01/1900.

Thank you

Report •

August 22, 2010 at 04:58:49

The formula you posted isn't returning an error, it's just not returning what you wanted !

When "CANF" is not present in the range in column D, the MIN function is, I think, returning zero.

I am assuming that the cell that contains this formula is formatted as a date.

You show the result as 01/01/1900 - but I suspect it may be 00/01/1900, which is zero formatted as a date.
01/01/1900 is 1 formatted as a date.
If the value shown is 01/01/1900 then your formula is returning 1 as the minimum. Without seeing the rest of your data I can't be sure whether the result is 0 or 1.

Here is a possible solution.
This uses your array formula approach and just tests your original formula for a result of zero and if the result is zero it returns an empty cell instead:
{=IF(MIN(IF(Today!$D$2:$D$15000="CANF",IF(Today!$O$2:$O$15000="Yes", Today!$C2:$C$15000)))=0,"", MIN(IF(Today!$D$2:$D$15000="CANF",IF(Today!$O$2:$O$15000="Yes", Today!$C2:$C$15000))))}

If you are getting a result of 1, then just use:
{=IF(MIN(IF(Today!$D$2:$D$15000="CANF",IF(Today!$O$2:$O$15000="Yes", Today!$C2:$C$15000)))<2,"", MIN(IF(Today!$D$2:$D$15000="CANF",IF(Today!$O$2:$O$15000="Yes", Today!$C2:$C$15000))))}


Report •

Related Solutions

August 22, 2010 at 05:25:20
You were right about the formatting. I was getting a zero. I added your addition to my formula and I am now getting a blank when the look up is not present.

Thank you for your time and knowledge. I can always count on nice and experienced people like yourself.

Till next time,

Report •

August 22, 2010 at 06:21:27
You're very welcome.



Report •

Ask Question