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

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

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

.

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

.

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

.



.

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

.



.

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

.



.



.

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

.



.



.

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

.



.



.



.

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

.



.



.



.