Minimum nonzero value for non-adjacent columns

Catalin

unread,

May 21, 2010, 7:19:01 AM5/21/10

to

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

Steve Dunn

unread,

May 21, 2010, 8:14:56 AM5/21/10

to

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" <Cat...@discussions.microsoft.com> wrote in message
news:0C9087A4-F711-4474...@microsoft.com...

Catalin

unread,

May 21, 2010, 8:55:01 AM5/21/10

to

Hi Steve

I am talking about 5 columns, in this case regularly separated by 2 other
columns.

rgds,
Catalin

"Steve Dunn" wrote:

> .
>

T. Valko

unread,

May 21, 2010, 9:45:03 AM5/21/10

to

>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" <Cat...@discussions.microsoft.com> wrote in message

news:22F89E3B-1466-4797...@microsoft.com...

Catalin

unread,

May 22, 2010, 5:10:01 AM5/22/10

to

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:

> .
>

T. Valko

unread,

May 22, 2010, 10:46:47 AM5/22/10

to

>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:S5>0,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" <Cat...@discussions.microsoft.com> wrote in message

news:DA449E03-C3FE-47CC...@microsoft.com...

Catalin

unread,

May 22, 2010, 1:49:01 PM5/22/10

to

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:

> .
>

T. Valko

unread,

May 22, 2010, 9:32:29 PM5/22/10

to

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((B5:D5,K5,P5),FREQUENCY((B5:D5,K5,P5),0)+1)

--
Biff
Microsoft Excel MVP

"Catalin" <Cat...@discussions.microsoft.com> wrote in message

news:8B76600A-3288-4B5E...@microsoft.com...

Catalin

unread,

May 23, 2010, 5:45:01 AM5/23/10

to

Thanks a lot.
You're an Excel genius.

Catalin

"T. Valko" wrote:

> .
>

T. Valko

unread,

May 23, 2010, 9:34:35 AM5/23/10

to

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

"Catalin" <Cat...@discussions.microsoft.com> wrote in message

news:1A6BF075-5CEA-4F45...@microsoft.com...