0

I'm trying to create a formula that meets the following conditions:

  • If any of the cells is 0, then just show 0
  • If none of the cells equals 0, then show the lowest number of those cells

So let's say A1 has a value of 2; A5 is 3 and A13 is 0, then I want the formula to show "0". On the other hand, if A1 is 2, A5 is 3 and A13 is 1, I want the formula to show "1".

I think the solution relies on conditional formatting and mincounts, but couldn't find a formula that works. Please keep in mind that I don't want the values to be summed, only to show the lowest value.

1

2 Answers 2

0

I think that this formula will solve your problem:

=If(A1:A<>0,min(A1:A),0)

min(A1:A)

p.s.: I don't understand why you brought up conditional formatting, but if instead you wanted to highlight the zero or the minimum of the various values ​​respectively, then you could apply this custom formula to the conditional formatting of the range A1:A

=and(A1<>"",A1=If(A1<>0,min($A$1:$A),0))

=and(A1<>"",A1=min($A$1:$A))
3
  • Neither of these formulas will work correctly because the if() will only evaluate A1 and ignore the rest of column A. Jul 30 at 8:33
  • you are right, I stand corrected
    – Daniele
    Jul 30 at 9:16
  • Thanks, this worked perfectly! Jul 31 at 13:01
0

The min() function automatically skips blank cells and text strings. Assuming that no number is negative, you can simply use this:

=min(A1:A)

1
  • Thanks, this worked, this was much simpler than I thought. Jul 31 at 13:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.