Array Formulas help?

Toddcan

New Member
Joined
Apr 14, 2002
Messages
11
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!!
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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.
 
Upvote 0
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 :biggrin:.

In B6 array-enter:

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

Aladin
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top