What is the alternative to the MIN function when you have to recognize a cell is blank?

Bill Golden
Bill Golden used Ask the Experts™
on
I have the following formula

=IF(N32>1, IF(MIN(H32:H33, J32:J33)>N32, N32,MIN(H32:H33, J32:J33)), " ")

Which works unless H32:H33 is blank, which occasionally is the case.  I know MIN ignores blank cells but I need someway to recognize the value is zero in H32:H33 when it is blank.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Technical Specialist
Awarded 2017
Distinguished Expert 2018
Commented:
Please try
=IF(OR(ISBLANK(H32),ISBLANK(H33)),"Value is blank",IF(N32>1, IF(MIN(H32:H33, J32:J33)>N32, N32,MIN(H32:H33, J32:J33)), " "))

Open in new window

Bill GoldenExecutive Managing Member

Author

Commented:
I modified your formula...
=IF(OR(ISBLANK(H32),ISBLANK(H33)),"Value is blank",IF(N32>1, IF(MIN(H32:H33, J32:J33)>N32, N32,MIN(H32:H33, J32:J33)), " "))
so that it would return 0 when H32:H33 is blank as follows:
=IF(OR(ISBLANK(H32),ISBLANK(H33)),0,IF(N32>1, IF(MIN(H32:H33, J32:J33)>N32, N32,MIN(H32:H33, J32:J33)), " "))
However, the cell is returning 0 no matter the contents of H23:H33 or J32:J33
Bill GoldenExecutive Managing Member

Author

Commented:
Eureka, I have it...

=IF(ISBLANK(H32:H33),0,IF(N32>0, IF(MIN(H32:H33, J32:J33)>N32, N32,MIN(H32:H33, J32:J33)), " "))

You put me on the right track. Thanks.
Bill GoldenExecutive Managing Member

Author

Commented:
You put me on the right track. Thanks.
Shaun VermaakTechnical Specialist
Awarded 2017
Distinguished Expert 2018

Commented:
Anytime :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial