Array Formulas help?

[Toddcan]

I have rows that contain a bunch of data, say 5 columns wide. All data are numbers. I have an array formula to calculate a certain number based on certain conditions. Now, what I want to do, it pick data from the same row, but a different column, based on which row met the criteria in the array. Sound confusing, but here is an example:

Cell B is a number, lets say 5.
Cell range (C..F) all contain numbers, say 3,6,7 and 9

I have an array formula as follows:
=Min(if(b1-(c1..f1)>0,B1-(c1:f1))) which basically picks the miniumum non-negative value of 5-3, 5-6, 5-7 and 5-9. In this case, it's 2.

Now, what I want to do is this...
The array picked the row with the 3 in it. Let's say that the row that contains the number 3 also contains 4 other columns. I want to identify the number in a different column but the same row as the 3. How do I do this?

I'll try and do this graphically.
A B C D E

1 1.1 0.5 4.1 3 6.2

2 2.6 0.4 5.2 6 5.9

3 0.9 0.1 6.4 7 8.1

4 1.2 0.8 5.5 9 7.7

5

6 5


The array would be:

=Min(if(a6-(d1:d4)>0,a6-(d1:d4)))

it takes 5-3, 5-6, 5-7 and 5-9, and returns the lowest non-neg number (2 in this case)

Now.. say I want to know which of the values in column A correspond to the value used from column d (in this case, it would be 1.1). How do I do that?

Tough one to me... thx for help in advance!!

[Toddcan]

Sorry, the graphics turned out crappy! I hope you can make sense of it.

[Aladin Akyurek]

Didn't you see:

http://www.mrexcel.com/board/viewtop...c=5175&forum=2

Aladin

[Toddcan]

yes, I saw it.. thanks for the help. But what I want to now carry it one step further. I want to grab another piece of data from the row that was selected by the array.

[Aladin Akyurek]

On 2002-04-16 11:20, Toddcan wrote:
I have rows that contain a bunch of data, say 5 columns wide. All data are numbers. I have an array formula to calculate a certain number based on certain conditions. Now, what I want to do, it pick data from the same row, but a different column, based on which row met the criteria in the array. Sound confusing, but here is an example:

Cell B is a number, lets say 5.
Cell range (C..F) all contain numbers, say 3,6,7 and 9

I have an array formula as follows:
=Min(if(b1-(c1..f1)>0,B1-(c1:f1))) which basically picks the miniumum non-negative value of 5-3, 5-6, 5-7 and 5-9. In this case, it's 2.

Now, what I want to do is this...
The array picked the row with the 3 in it. Let's say that the row that contains the number 3 also contains 4 other columns. I want to identify the number in a different column but the same row as the 3. How do I do this?

I'll try and do this graphically.
A B C D E

1 1.1 0.5 4.1 3 6.2

2 2.6 0.4 5.2 6 5.9

3 0.9 0.1 6.4 7 8.1

4 1.2 0.8 5.5 9 7.7

5

6 5


The array would be:

=Min(if(a6-(d1:d4)>0,a6-(d1:d4)))

it takes 5-3, 5-6, 5-7 and 5-9, and returns the lowest non-neg number (2 in this case)

Now.. say I want to know which of the values in column A correspond to the value used from column d (in this case, it would be 1.1). How do I do that?

Tough one to me... thx for help in advance!!

Now looking closely, I notice that you indeed saw my reply .

In B6 array-enter:

=(MIN(IF(A6-(D1:D4)>0,A6-(D1:D4)))>0)*(A1:A4)

Aladin