MIN CALC RETURNING BLANK

[LawNapier]

Below is the issue I am running into. It is basically a "Epic" min calc of other "User Story" min calcs. If one of the "User Story" min calcs in the range is blank, then I get a blank as well in the "Epic" min calc even though there are other "User Story" min calc dates present that can be assessed for the minimum date to return.

 

[jasonb75]

If those are the actual formulas that you're using then the cells are not blank , they contain zero values that have been hidden by formatting.
A hidden zero is still a zero, which MIN will see as a valid number that will be lower than the dates in the other cells, subsequently producing another zero which is again being hidden by formatting.

As you're using 365, you will have MINIFS which makes this a lot easier.

=MINIFS(D5:D11,B5:B11,"User Story",D5:D11,">0")

 

[LawNapier]

If those are the actual formulas that you're using then the cells are not blank , they contain zero values that have been hidden by formatting.
A hidden zero is still a zero, which MIN will see as a valid number that will be lower than the dates in the other cells, subsequently producing another zero which is again being hidden by formatting.

As you're using 365, you will have MINIFS which makes this a lot easier.

=MINIFS(D5:D11,B5:B11,"User Story",D5:D11,">0")

Jasonb - Thank you for your response. I plugged this into my spreadsheet and unfortunately it is not working. Maybe I set it up incorrectly? Here is a screen shot of the results.

 

[LawNapier]

Jasonb - Thank you for your response. I plugged this into my spreadsheet and unfortunately it is not working. Maybe I set it up incorrectly? Here is a screen shot of the results. View attachment 15370

The result I am getting is still a blank cell in E6

 

[jasonb75]

You've switched the columns in the formula, it should be

=MINIFS(E7:E38,C7:C38,"User Story",E7:E38,">0")

 

[LawNapier]

Thanks I tried your solution and it works now.

=MINIFS(E7:E38,C7:C38,"User Story",E7:E38,">0")

I also tried belwo and it seems to work as well. Do you have any preference if either has a con?

=SMALL(E7:E38,COUNTIF($E$E:$J$38,0)+1)

 

[jasonb75]

I think both should work fine. The only thing to take into consideration is that MINIFS was only added to excel in the 2019 and 365 versions so it will show a #NAME? error if you open your workbook in an older version of excel.

There is no right or wrong way as long as it works, although it is always preferable to use a more efficient method where possible. In the case of the 2 formulas that you have above, any difference in efficiency would be too small to measure.