Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with these formulas
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),""))) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with these formulas
Try this array formula
=MIN(IF((MOD(ROW(D15:D537),18)=1)*(D15:D537<0)*(D 15:D537<""),D15:D537)) -- __________________________________ HTH Bob "Ayo" wrote in message ... 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),""))) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with these formulas
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 "Ayo" wrote: 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),""))) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with these formulas
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. "Bob Phillips" wrote: Try this array formula =MIN(IF((MOD(ROW(D15:D537),18)=1)*(D15:D537<0)*(D 15:D537<""),D15:D537)) -- __________________________________ HTH Bob "Ayo" wrote in message ... 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),""))) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with these formulas
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. "Francis" wrote: 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 "Ayo" wrote: 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),""))) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with these formulas
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 -- __________________________________ HTH Bob "Ayo" wrote in message ... 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. "Bob Phillips" wrote: Try this array formula =MIN(IF((MOD(ROW(D15:D537),18)=1)*(D15:D537<0)*(D 15:D537<""),D15:D537)) -- __________________________________ HTH Bob "Ayo" wrote in message ... 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),""))) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with these formulas
Now this formula result is all 1s. That's not what I am looking for.
"Bob Phillips" wrote: 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 -- __________________________________ HTH Bob "Ayo" wrote in message ... 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. "Bob Phillips" wrote: Try this array formula =MIN(IF((MOD(ROW(D15:D537),18)=1)*(D15:D537<0)*(D 15:D537<""),D15:D537)) -- __________________________________ HTH Bob "Ayo" wrote in message ... 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),""))) |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with these formulas
All 1s? It only returns a single value.
-- __________________________________ HTH Bob "Ayo" wrote in message ... Now this formula result is all 1s. That's not what I am looking for. "Bob Phillips" wrote: 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 -- __________________________________ HTH Bob "Ayo" wrote in message ... 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. "Bob Phillips" wrote: Try this array formula =MIN(IF((MOD(ROW(D15:D537),18)=1)*(D15:D537<0)*(D 15:D537<""),D15:D537)) -- __________________________________ HTH Bob "Ayo" wrote in message ... 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),""))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
lookup formulas dependent upon lookup formulas | Excel Worksheet Functions | |||
automatically copy formulas down columns or copy formulas all the | Excel Worksheet Functions | |||
Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) | |||
formulas for changing formulas? | Excel Discussion (Misc queries) |