=MIN how do I take the zero out

[b_rianv]

Good morning everyone,

How would I have my formula not show a zero?

=MIN($C$35:$C$55,$G$35:$G$55,$K$35:$K$55,$C$58:$C$78,$G$58:$G$78,$K$58:$K$78,$C$81:$C$102,$G$81:$G$85)

Some of the cells will have a number but as one cell has a zero my min formula always show a "0" instead of the lowest number.

Can someone help?

Thank you,
Brian

[azumi]

Have you try remove null with custom formats? Select cell and right click and you'll find "Format Cells....." and click, and select from "Category" is "Custom" and in "Type" select word "General" and erase it replace with 0;-0;;@, click "Ok", last step is copy the custom formats using "Format Painter"

hope it helps

cheers

[mangesh.mehendale]

Require Ctrl + Shift + Enter ...

Please Login or Register  to view this content.

[José Augusto]

Try this (Sorry for such this large formula)
Formula: Select Code copy to clipboard

Please Login or Register  to view this content.

[popipipo]

Take a look at this:
http://stackoverflow.com/questions/1...-formula-excel

[sanram]

I have achieved your result with a huge formula.

Please Login or Register  to view this content.

[sanram]

With the suggestion of Popipino's provided link you can use this also. But still a huge formula.

Please Login or Register  to view this content.

[popipipo]

Take a look at these formulas

[b_rianv]

Thank all of you guys for the help. While I have not had time to test these formulas. I am sure they will work.
Now is there any one formula that you guys recommend as the best one too use?

[sanram]

As I don't have much idea about FREQUENCY function, so I am unable to compare which one will be best. But in my sense I think that may be the solution with FREQUENCY will be better. But I am not sure about it.

[José Augusto]

Hi
No doubt that @popipipo formula is the best.

[XOR LX]

Yes, the solution with FREQUENCY is the best of the bunch, here. Of course, it should however be pointed out that that set-up would fail if there were any negative numbers within the ranges being queried.

Regards

[b_rianv]

Thank you guys for the help, yes The Frequency formula worked the best.

[Tony Valko]

With the suggestion of Popipino's provided link you can use this also. But still a huge formula.

=SMALL(($C$35:$C$55,$G$35:$G$55,$K$35:$K$55,$C$58:$C$78,$G$58:$G$78,
$K$58:$K$78,$C$81:$C$102,$G$81:$G$85),INDEX(FREQUENCY(($C$35:$C$55,
$G$35:$G$55,$K$35:$K$55,$C$58:$C$78,$G$58:$G$78,$K$58:$K$78,
$C$81:$C$102,
$G$81:$G$85),0),1)+1)

No need for the INDEX function:

=SMALL(($C$35:$C$55,$G$35:$G$55,$K$35:$K$55,$C$58:$C$78,
$G$58:$G$78,$K$58:$K$78,$C$81:$C$102,$G$81:$G$85),FREQUENCY(
($C$35:$C$55,$G$35:$G$55,$K$35:$K$55,$C$58:$C$78,$G$58:$G$78,
$K$58:$K$78,$C$81:$C$102,$G$81:$G$85),0)+1)

[b_rianv]

Does this Require..... Ctrl + Shift + Enter ...

=SMALL(($C$35:$C$55,$G$35:$G$55,$K$35:$K$55,$C$58:$C$78,
$G$58:$G$78,$K$58:$K$78,$C$81:$C$102,$G$81:$G$85),FREQUENCY(
($C$35:$C$55,$G$35:$G$55,$K$35:$K$55,$C$58:$C$78,$G$58:$G$78,
$K$58:$K$78,$C$81:$C$102,$G$81:$G$85),0)+1)

[Tony Valko]

Does this Require..... Ctrl + Shift + Enter ...

No, just normal Enter.

[b_rianv]

Thank you Tony

[Tony Valko]

You're welcome!