Help with these formulas

A

Ayo

This is my situation: I have a buch of cells, not consistent, that have
values and I am trying to get the minimum value, not zero, in those bunch of
cells. for example this formula
=MIN(D15,D33,D51,D69,D87,D105,D123,D141,D159,D177,D195,D213,D231,D249,D267,D285,D303,D321,D339,D357,D375,D393,D411,D429,D447,D465,D483,D501,D519,D537)

If the above formula has zero in a few of the ranges, say D51, D141, D213,
D339, D411, D465 and D501. The above formula will result in a zero but I
don't want the zero value. I want the next minumum value that is not zero. Is
there a fuction that I can use for that? And also if there is a way to modify
and use the formula below to obtain the same result, that would be
appreciated too. Thanks
=IF(COUNTA(G307:CF307)=0,"",MIN(IF(G307:CF307<>"",VALUE($G$306:$CF$306),"")))
 
B

Bob Phillips

Try this array formula

=MIN(IF((MOD(ROW(D15:D537),18)=1)*(D15:D537<>0)*(D15:D537<>""),D15:D537))
 
F

Francis

try
=SMALL(your range,2)
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another
 
A

Ayo

It still evaluate to zero. let me see if I can explain better. I have these
range of cells: (D7,D37,D67,D97,D127,D157,D187,D217,D247,D277), and when I
use the MIN() function =MIN(D7,D37,D67,D97,D127,D157,D187,D217,D247,D277) I
get 0 because the values in the range are (1,5,0,5,0,0,1,3,1,2). But I want
my result to be 1 because 1 is the next higher number to zero in the range.
In the same instance, if the values in the range are
(14,14,0,14,0,0,14,0,14,14) I want my result to be 14, because 14 is the next
higher number to zero in the range. This is want I am trying to achieve.
 
A

Ayo

It still evaluate to zero. let me see if I can explain better. I have these
range of cells: (D7,D37,D67,D97,D127,D157,D187,D217,D247,D277), and when I
use the MIN() function =MIN(D7,D37,D67,D97,D127,D157,D187,D217,D247,D277) I
get 0 because the values in the range are (1,5,0,5,0,0,1,3,1,2). But I want
my result to be 1 because 1 is the next higher number to zero in the range.
In the same instance, if the values in the range are
(14,14,0,14,0,0,14,0,14,14) I want my result to be 14, because 14 is the next
higher number to zero in the range. This is want I am trying to achieve.
 
B

Bob Phillips

That is not what you said originally. Use

=MIN(IF((MOD(ROW(D7:D277),30)=7)*(D7:D277<>0)*(D7:D277<>""),D7:D277))

still array entered
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help with formulas 1

Top