Return blanks instead of zeros if no data in column (MINIFS formula)

[lawi227]

I currently am using this formula:
=MINIFS(AH$71:AH$120,D$71:D$120,$D$4)

However, if there is no data in cells AH71:AH120, then it returns a zero. I want to formula to return blanks rather than a zero. How can I adjust my formula to do so?

Thanks in advance.

 

[Derek Brown]

Perhaps the easiest solution is to format the cells to not display zero. For example, something like:
#,##0;[Red]-#,##0;
For more information:
https://support.office.com/en-gb/ar...r-format-78f2a361-936b-4c03-8772-09fab54be7f4

 

[steve the fish]

I don't have 2016 to test but maybe:

=IF(COUNTIFS(D$71:D$120,$D$4,AH$71:AH$120,"<>"),MINIFS(AH$71:AH$120,D$71:D$120,$D$4),"")

 

[lawi227]

Thanks. The only issue I suppose I have with this is if I use the returned values from the calculation to do another MIN calculation, it will return a zero again. I suppose I can adjust that formula.

 

[lawi227]

This appears to work just fine!

I also have median, max, and average formulas I want to run. There are MAXIFS, AVERAGEIFS functions, so I would assume I can just substitute those functions? What about the median function though?

I don't have 2016 to test but maybe:

=IF(COUNTIFS(D$71:D$120,$D$4,AH$71:AH$120,"<>"),MINIFS(AH$71:AH$120,D$71:D$120,$D$4),"")

 

[peterhinton]

I don't use the newest version of excel so I don't get to use MINIFS,

however you can always use an array function for the MEDIAN if the new version doesn't have that,

{=MEDIAN(IF(L:L="yes",K:K,""))}

works well for min, max etc


Column L would be the criteria range, K would be your range to find the median, and in my example "yes" was the criteria,

 

[steve the fish]

Like this perhaps:

=IFERROR(MEDIAN(IF(ISNUMBER(AH$71:AH$120),IF(D$71:D$120=$D$4,AH$71:AH$120))),"")

Needs to be entered CNTL-SHIFT-ENTER not just entered.

 

[peterhinton]

I forgot the main point of eliminating the 0

{=IF(MEDIAN(IF(L:L="yes",K:K,""))=0,"",MEDIAN(IF(L:L="yes",K:K,"")))}

Would work, just change you columns etc to suit your data

 

[Derek Brown]

Just be aware that "" is not the same as zero, so may give a wrong result should you later check to see if the cell contains zero.

 

[lawi227]

HI Steve the fish,

I started to populate my table with more data in cells 71:120, and all of the sudden there are 0.00 that started populating. Any way to troubleshoot? I searched the table to see if I accidentally copy and pasted 0.00 into the table but there are none. Any help would be much appreciated!

After doing some more troubleshooting, I realized that the formula may not have actually worked. It was only showing blanks for columns where there was no data. But lets say there is data in H71:H120 but it does not meat the first criteria (where $D$4 is equal to a cell in $D$71:D$120), then it returns 0.00.

I don't have 2016 to test but maybe:

=IF(COUNTIFS(D$71:D$120,$D$4,AH$71:AH$120,"<>"),MINIFS(AH$71:AH$120,D$71:D$120,$D$4),"")