Noob Question: Stop =MIN formula from returning 0's when referenced cells are blank.

[Elihue]

Hi everyone! I have what I am assuming is a noob question. I have several =MIN formulas I want to stop from returning 0's when the reference cells are blank. At the same time I would like to maybe build the formulas to work a bit more efficiently.

See the attached image below. This is the part of the spreadsheet I am working on. Columns C through I, and columns P through U are all using the same basic =MIN formulas that reference multiple cells in another sheet in the workbook. For example the =MIN formula in column C4 is...

=MIN('Pole Data Collection'!BX5,'Pole Data Collection'!CD5,'Pole Data Collection'!IL5,'Pole Data Collection'!JH5,'Pole Data Collection'!KD5,'Pole Data Collection'!KZ5,'Pole Data Collection'!LV5,'Pole Data Collection'!MR5)

This doesn't seem like it is written efficiently. Plus it is returning 0's when the referenced cells are blank. I need it to return nothing. Is there a better why I can create these =MIN formulas so they are more efficient and don't return 0's? Any help would be greatly appreciated.

 

[Beyond_avarice]

Wrap you min formula with an "If statement" =IF(MIN()=0,"",MIN())

 

[Jonmo1]

The If Min=0 test is the simplest.
However it can't differentiate between a 0 returned because of blanks, and 0 returned because a cell in the range actually contains a 0. In which case 0 is a valid/correct return of the Min function.

If you need to make that distinction between blanks and real 0s in the range, you'll need to use an array type of formula
=MIN(IF(range<>"",range))

This of coarse doesn't work when your range is not contiguous. Making it even more complicated.

Is there anything in another row that indicates which cells you need to test for the minimum ? A specific word in a header ?

Then something like
=MIN(IF(A1:Z1="Keyword",IF(A4:Z4<>"",A4:Z4)))

 

[Elihue]

So what would that look like?

I tried this..

=IF(MIN('Pole Data Collection'!BX5,'Pole Data Collection'!CD5,'Pole Data Collection'!IL5,'Pole Data Collection'!JH5,'Pole Data Collection'!KD5,'Pole Data Collection'!KZ5,'Pole Data Collection'!LV5,'Pole Data Collection'!MR5)=0,"",MIN())

..and it didn't work for me.Forgive me if this is stupid, I have never wrapped a formula with another formula.

 

[Beyond_avarice]

Now include your criteria in the second MIN() function. You are asking the question: If Min value returned from all my arguments = 0(Zero), Then show the result as blank="", else show the result of my MIN given all the arguments I have listed.

 

[Elihue]

The If Min=0 test is the simplest.
However it can't differentiate between a 0 returned because of blanks, and 0 returned because a cell in the range actually contains a 0. In which case 0 is a valid/correct return of the Min function.

If you need to make that distinction between blanks and real 0s in the range, you'll need to use an array type of formula
=MIN(IF(range<>"",range))

This of coarse doesn't work when your range is not contiguous. Making it even more complicated.

Is there anything in another row that indicates which cells you need to test for the minimum ? A specific word in a header ?

Then something like
=MIN(IF(A1:Z1="Keyword",IF(A4:Z4<>"",A4:Z4)))

There are keywords I could use I guess. In the sheet(Pole Data Collection) All the columns I am referencing/need are going to have the word "Height" in it. For example it's usually (Name of object)(space)(Height). So that would look like "Primary Height".

 

[Jonmo1]

Excellent.

So you're looking for cells with the header containing the word Height. Which could really be "Primary Height" or "Secondary Height", both of which you want included in the MIN.
Right?

Which verison of Excel are you using?

 

[Jonmo1]

Try this, again assuming you need to differentiate between 0 becaue of blanks in the range, and 0 because of actual 0's in the range..

=IFERROR(SMALL(IF('Pole Data Collection'!BX5:MR5<>"",IF(ISNUMBER(SEARCH("Height",'Pole Data Collection'!BX1:MR1)),'Pole Data Collection'!BX5:MR5)),1),"")

This is an array formula that requires special keystroke to activate it.
After typing the formula, do not press enter
Instead, Press CTRL + SHIFT + ENTER
When entered correctly, the formula will be enclosed in {brackets}

 

[Elihue]

Now include your criteria in the second MIN() function. You are asking the question: If Min value returned from all my arguments = 0(Zero), Then show the result as blank="", else show the result of my MIN given all the arguments I have listed.

Thanks a lot Beyond! This is a nice quick solution to the 0's. I really appreciate it.

 

[Elihue]

Try this, again assuming you need to differentiate between 0 becaue of blanks in the range, and 0 because of actual 0's in the range..

=IFERROR(SMALL(IF('Pole Data Collection'!BX5:MR5<>"",IF(ISNUMBER(SEARCH("Height",'Pole Data Collection'!BX1:MR1)),'Pole Data Collection'!BX5:MR5)),1),"")

This is an array formula that requires special keystroke to activate it.
After typing the formula, do not press enter
Instead, Press CTRL + SHIFT + ENTER
When entered correctly, the formula will be enclosed in {brackets}

Thanks so much for the reply. I will give this a shot. It's so far above my understanding of the excel formula syntax I don't think I'll be able to get it to work. lol