Array Formulas help?
Array Formulas help?
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Array Formulas help?

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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. #2
    New Member
    Join Date
    Apr 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,748
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,748
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com