If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Minimum nonzero value for non-adjacent columns
Dear Friends
I use Xls 2003 and I would like to ask for help in the following problem: I have separate (non-adjacent) columns of data. I need to extract the mimimum value (higher than zero) on each row. I would like to have a solution without using helping ranges (to copy my non-contiguous range to a contiguous range) or named ranges because there are too many rows involved. Thanks in advance, Catalin |
#2
|
|||
|
|||
Minimum nonzero value for non-adjacent columns
Hi Catalin,
how many columns are we talking about, and are they regularly or randomly seperated? Knowing the columns involved may aid with the solution. "Catalin" wrote in message ... Dear Friends I use Xls 2003 and I would like to ask for help in the following problem: I have separate (non-adjacent) columns of data. I need to extract the mimimum value (higher than zero) on each row. I would like to have a solution without using helping ranges (to copy my non-contiguous range to a contiguous range) or named ranges because there are too many rows involved. Thanks in advance, Catalin |
#3
|
|||
|
|||
Minimum nonzero value for non-adjacent columns
Hi Steve
I am talking about 5 columns, in this case regularly separated by 2 other columns. rgds, Catalin "Steve Dunn" wrote: Hi Catalin, how many columns are we talking about, and are they regularly or randomly seperated? Knowing the columns involved may aid with the solution. "Catalin" wrote in message ... Dear Friends I use Xls 2003 and I would like to ask for help in the following problem: I have separate (non-adjacent) columns of data. I need to extract the mimimum value (higher than zero) on each row. I would like to have a solution without using helping ranges (to copy my non-contiguous range to a contiguous range) or named ranges because there are too many rows involved. Thanks in advance, Catalin . |
#4
|
|||
|
|||
Minimum nonzero value for non-adjacent columns
I am talking about 5 columns, in this case
regularly separated by 2 other columns. How about telling us the specific cells that you want to evaluate? Like: A2, D2, G2, J2, M2 Is there numeric data in the cells between the target cells? For example, what's in B2 and C2? -- Biff Microsoft Excel MVP "Catalin" wrote in message ... Hi Steve I am talking about 5 columns, in this case regularly separated by 2 other columns. rgds, Catalin "Steve Dunn" wrote: Hi Catalin, how many columns are we talking about, and are they regularly or randomly seperated? Knowing the columns involved may aid with the solution. "Catalin" wrote in message ... Dear Friends I use Xls 2003 and I would like to ask for help in the following problem: I have separate (non-adjacent) columns of data. I need to extract the mimimum value (higher than zero) on each row. I would like to have a solution without using helping ranges (to copy my non-contiguous range to a contiguous range) or named ranges because there are too many rows involved. Thanks in advance, Catalin . |
#5
|
|||
|
|||
Minimum nonzero value for non-adjacent columns
There are the following columns from which I would like to extract the
minimum values: G, J, M, P, S. Between those columns there are others with text or numer data. So I need to evaluate for example G5,J5,M5,P5 and S5 and so on... Thanks and regards, Catalin "T. Valko" wrote: I am talking about 5 columns, in this case regularly separated by 2 other columns. How about telling us the specific cells that you want to evaluate? Like: A2, D2, G2, J2, M2 Is there numeric data in the cells between the target cells? For example, what's in B2 and C2? -- Biff Microsoft Excel MVP "Catalin" wrote in message ... Hi Steve I am talking about 5 columns, in this case regularly separated by 2 other columns. rgds, Catalin "Steve Dunn" wrote: Hi Catalin, how many columns are we talking about, and are they regularly or randomly seperated? Knowing the columns involved may aid with the solution. "Catalin" wrote in message ... Dear Friends I use Xls 2003 and I would like to ask for help in the following problem: I have separate (non-adjacent) columns of data. I need to extract the mimimum value (higher than zero) on each row. I would like to have a solution without using helping ranges (to copy my non-contiguous range to a contiguous range) or named ranges because there are too many rows involved. Thanks in advance, Catalin . . |
#6
|
|||
|
|||
Minimum nonzero value for non-adjacent columns
I need to extract the mimimum value
(higher than zero) on each row. Try this array formula** : =MIN(IF(MOD(COLUMN(G5:S5),3)=1,IF(G5:S50,G5:S5))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Caveat: inserting new columns before or within the referenced range will cause the formula to "break" ! -- Biff Microsoft Excel MVP "Catalin" wrote in message news There are the following columns from which I would like to extract the minimum values: G, J, M, P, S. Between those columns there are others with text or numer data. So I need to evaluate for example G5,J5,M5,P5 and S5 and so on... Thanks and regards, Catalin "T. Valko" wrote: I am talking about 5 columns, in this case regularly separated by 2 other columns. How about telling us the specific cells that you want to evaluate? Like: A2, D2, G2, J2, M2 Is there numeric data in the cells between the target cells? For example, what's in B2 and C2? -- Biff Microsoft Excel MVP "Catalin" wrote in message ... Hi Steve I am talking about 5 columns, in this case regularly separated by 2 other columns. rgds, Catalin "Steve Dunn" wrote: Hi Catalin, how many columns are we talking about, and are they regularly or randomly seperated? Knowing the columns involved may aid with the solution. "Catalin" wrote in message ... Dear Friends I use Xls 2003 and I would like to ask for help in the following problem: I have separate (non-adjacent) columns of data. I need to extract the mimimum value (higher than zero) on each row. I would like to have a solution without using helping ranges (to copy my non-contiguous range to a contiguous range) or named ranges because there are too many rows involved. Thanks in advance, Catalin . . |
#7
|
|||
|
|||
Minimum nonzero value for non-adjacent columns
Thanks a lot.
It works perfect. I do have a question though. Is there any other "magic" you can do for the case where the columns are randomly separated by other columns (you do not have any pattern)? Say columns B, C, D, K, P a.s.o? This is just for my knowledge. Anyway, thank you once again for your time and for sharing that info. Best regards, Catalin "T. Valko" wrote: I need to extract the mimimum value (higher than zero) on each row. Try this array formula** : =MIN(IF(MOD(COLUMN(G5:S5),3)=1,IF(G5:S50,G5:S5))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Caveat: inserting new columns before or within the referenced range will cause the formula to "break" ! -- Biff Microsoft Excel MVP "Catalin" wrote in message news There are the following columns from which I would like to extract the minimum values: G, J, M, P, S. Between those columns there are others with text or numer data. So I need to evaluate for example G5,J5,M5,P5 and S5 and so on... Thanks and regards, Catalin "T. Valko" wrote: I am talking about 5 columns, in this case regularly separated by 2 other columns. How about telling us the specific cells that you want to evaluate? Like: A2, D2, G2, J2, M2 Is there numeric data in the cells between the target cells? For example, what's in B2 and C2? -- Biff Microsoft Excel MVP "Catalin" wrote in message ... Hi Steve I am talking about 5 columns, in this case regularly separated by 2 other columns. rgds, Catalin "Steve Dunn" wrote: Hi Catalin, how many columns are we talking about, and are they regularly or randomly seperated? Knowing the columns involved may aid with the solution. "Catalin" wrote in message ... Dear Friends I use Xls 2003 and I would like to ask for help in the following problem: I have separate (non-adjacent) columns of data. I need to extract the mimimum value (higher than zero) on each row. I would like to have a solution without using helping ranges (to copy my non-contiguous range to a contiguous range) or named ranges because there are too many rows involved. Thanks in advance, Catalin . . . |
#8
|
|||
|
|||
Minimum nonzero value for non-adjacent columns
If the numbers are always positive (or 0), something like this will work:
=SMALL((B5,C5,D5,K5,P5),FREQUENCY((B5,C5,D5,K5,P5) ,0)+1) Or, like this if all the cells are in the same row: =SMALL((B55,K5,P5),FREQUENCY((B55,K5,P5),0)+1) -- Biff Microsoft Excel MVP "Catalin" wrote in message ... Thanks a lot. It works perfect. I do have a question though. Is there any other "magic" you can do for the case where the columns are randomly separated by other columns (you do not have any pattern)? Say columns B, C, D, K, P a.s.o? This is just for my knowledge. Anyway, thank you once again for your time and for sharing that info. Best regards, Catalin "T. Valko" wrote: I need to extract the mimimum value (higher than zero) on each row. Try this array formula** : =MIN(IF(MOD(COLUMN(G5:S5),3)=1,IF(G5:S50,G5:S5))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Caveat: inserting new columns before or within the referenced range will cause the formula to "break" ! -- Biff Microsoft Excel MVP "Catalin" wrote in message news There are the following columns from which I would like to extract the minimum values: G, J, M, P, S. Between those columns there are others with text or numer data. So I need to evaluate for example G5,J5,M5,P5 and S5 and so on... Thanks and regards, Catalin "T. Valko" wrote: I am talking about 5 columns, in this case regularly separated by 2 other columns. How about telling us the specific cells that you want to evaluate? Like: A2, D2, G2, J2, M2 Is there numeric data in the cells between the target cells? For example, what's in B2 and C2? -- Biff Microsoft Excel MVP "Catalin" wrote in message ... Hi Steve I am talking about 5 columns, in this case regularly separated by 2 other columns. rgds, Catalin "Steve Dunn" wrote: Hi Catalin, how many columns are we talking about, and are they regularly or randomly seperated? Knowing the columns involved may aid with the solution. "Catalin" wrote in message ... Dear Friends I use Xls 2003 and I would like to ask for help in the following problem: I have separate (non-adjacent) columns of data. I need to extract the mimimum value (higher than zero) on each row. I would like to have a solution without using helping ranges (to copy my non-contiguous range to a contiguous range) or named ranges because there are too many rows involved. Thanks in advance, Catalin . . . |
#9
|
|||
|
|||
Minimum nonzero value for non-adjacent columns
Thanks a lot.
You're an Excel genius. Catalin "T. Valko" wrote: If the numbers are always positive (or 0), something like this will work: =SMALL((B5,C5,D5,K5,P5),FREQUENCY((B5,C5,D5,K5,P5) ,0)+1) Or, like this if all the cells are in the same row: =SMALL((B55,K5,P5),FREQUENCY((B55,K5,P5),0)+1) -- Biff Microsoft Excel MVP "Catalin" wrote in message ... Thanks a lot. It works perfect. I do have a question though. Is there any other "magic" you can do for the case where the columns are randomly separated by other columns (you do not have any pattern)? Say columns B, C, D, K, P a.s.o? This is just for my knowledge. Anyway, thank you once again for your time and for sharing that info. Best regards, Catalin "T. Valko" wrote: I need to extract the mimimum value (higher than zero) on each row. Try this array formula** : =MIN(IF(MOD(COLUMN(G5:S5),3)=1,IF(G5:S50,G5:S5))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Caveat: inserting new columns before or within the referenced range will cause the formula to "break" ! -- Biff Microsoft Excel MVP "Catalin" wrote in message news There are the following columns from which I would like to extract the minimum values: G, J, M, P, S. Between those columns there are others with text or numer data. So I need to evaluate for example G5,J5,M5,P5 and S5 and so on... Thanks and regards, Catalin "T. Valko" wrote: I am talking about 5 columns, in this case regularly separated by 2 other columns. How about telling us the specific cells that you want to evaluate? Like: A2, D2, G2, J2, M2 Is there numeric data in the cells between the target cells? For example, what's in B2 and C2? -- Biff Microsoft Excel MVP "Catalin" wrote in message ... Hi Steve I am talking about 5 columns, in this case regularly separated by 2 other columns. rgds, Catalin "Steve Dunn" wrote: Hi Catalin, how many columns are we talking about, and are they regularly or randomly seperated? Knowing the columns involved may aid with the solution. "Catalin" wrote in message ... Dear Friends I use Xls 2003 and I would like to ask for help in the following problem: I have separate (non-adjacent) columns of data. I need to extract the mimimum value (higher than zero) on each row. I would like to have a solution without using helping ranges (to copy my non-contiguous range to a contiguous range) or named ranges because there are too many rows involved. Thanks in advance, Catalin . . . . |
#10
|
|||
|
|||
Minimum nonzero value for non-adjacent columns
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Catalin" wrote in message ... Thanks a lot. You're an Excel genius. Catalin "T. Valko" wrote: If the numbers are always positive (or 0), something like this will work: =SMALL((B5,C5,D5,K5,P5),FREQUENCY((B5,C5,D5,K5,P5) ,0)+1) Or, like this if all the cells are in the same row: =SMALL((B55,K5,P5),FREQUENCY((B55,K5,P5),0)+1) -- Biff Microsoft Excel MVP "Catalin" wrote in message ... Thanks a lot. It works perfect. I do have a question though. Is there any other "magic" you can do for the case where the columns are randomly separated by other columns (you do not have any pattern)? Say columns B, C, D, K, P a.s.o? This is just for my knowledge. Anyway, thank you once again for your time and for sharing that info. Best regards, Catalin "T. Valko" wrote: I need to extract the mimimum value (higher than zero) on each row. Try this array formula** : =MIN(IF(MOD(COLUMN(G5:S5),3)=1,IF(G5:S50,G5:S5))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Caveat: inserting new columns before or within the referenced range will cause the formula to "break" ! -- Biff Microsoft Excel MVP "Catalin" wrote in message news There are the following columns from which I would like to extract the minimum values: G, J, M, P, S. Between those columns there are others with text or numer data. So I need to evaluate for example G5,J5,M5,P5 and S5 and so on... Thanks and regards, Catalin "T. Valko" wrote: I am talking about 5 columns, in this case regularly separated by 2 other columns. How about telling us the specific cells that you want to evaluate? Like: A2, D2, G2, J2, M2 Is there numeric data in the cells between the target cells? For example, what's in B2 and C2? -- Biff Microsoft Excel MVP "Catalin" wrote in message ... Hi Steve I am talking about 5 columns, in this case regularly separated by 2 other columns. rgds, Catalin "Steve Dunn" wrote: Hi Catalin, how many columns are we talking about, and are they regularly or randomly seperated? Knowing the columns involved may aid with the solution. "Catalin" wrote in message ... Dear Friends I use Xls 2003 and I would like to ask for help in the following problem: I have separate (non-adjacent) columns of data. I need to extract the mimimum value (higher than zero) on each row. I would like to have a solution without using helping ranges (to copy my non-contiguous range to a contiguous range) or named ranges because there are too many rows involved. Thanks in advance, Catalin . . . . |
Thread Tools | |
Display Modes | |
|
|