Minimum nonzero value for non-adjacent columns

[Catalin]

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]

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]

Hi Steve

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

rgds,
Catalin

 

[T. Valko]

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?

 

[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]

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" !

 

[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]

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)

 

[Catalin]

Thanks a lot.
You're an Excel genius.

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





.

 

[T. Valko]

You're welcome. Thanks for the feedback!