Thanks:  0
Likes:  0

# Thread: Array Formulas help?

1. 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!!

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

3.

4. 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.

5. 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)

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•