Getting the minimum number in a range WITHOUT excluding blank cells

[mcomp72]

I was hoping to use the Excel function MIN to return the smallest number in a range. Unfortunately it's not working for me, because if one of the cells in the range is blank, I need MIN to return 0. Apparently MIN ignores any cells that are empty. Is there some other way to do this, without getting into writing a User Defined Function? I also tried the function SMALL, but that too seemed to ignore empty cells. I also Googled around for an answer, but wasn't able to find anything. This seems like something that should be pretty simple, but I haven't had any luck finding a solution. Any help would be appreciated!

 

[Rick Rothstein]

This array-entered** formula does what you want...

=MIN(0+(0&G9:G12))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[mcomp72]

Thanks!!

Another question: could I use this within VBA code? I was also going to use the MIN function in some VBA code, like so:

Application.WorksheetFunction.Min(rng)

where rng is a Range variable.

Could I use this type of VBA code, but have it use it like the array entered formula you posted?

 

[Rick Rothstein]

Try it this way...

MinVal = Evaluate("MIN(0+(0&" & Rng.Address & "))")

 

[mcomp72]

Try it this way...

MinVal = Evaluate("MIN(0+(0&" & Rng.Address & "))")

It works great. Thank you!!