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