+ Reply to Thread
Results 1 to 3 of 3

Dynamic column reference in INDEX array function.

  1. #1
    Registered User
    Join Date
    02-17-2015
    Location
    Florida
    MS-Off Ver
    2010
    Posts
    1

    Dynamic column reference in INDEX array function.

    I have rows of data with 300 columns, where every three columns is a set of data. The first column in the set is data validated to either be a + or -, the second is a numerical value (represents a weight), and the third is data validated to either be "lb" or "gm". I want to sum the weights as pounds. So, if the third column is "gm", then I need to divide the value in the weight column by 453.592. If the first column in the set is a "-", then I need to preface the numeric value with a negative.

    I'm getting caught up on determining if the third column in the set is "gm". Here is the formula I have so far:

    {=SUM(IF(MOD(COLUMN($H6:$R6)-2,3)=0,IF(INDEX($H6:$R6,1,COLUMN($H6:$R6)+1)="gm",$H6:$R6/453.592,$H6:$R6),0))}

    NOTE: I realize that this is only looking at 11 columns, but I'm troubleshooting with a subset of the data. Also, I haven't included any formula(s) to check for the + or - yet.

    MOD(COLUMN($H6:$R6)-2,3)=0 - determines which columns to include in the sum

    INDEX($H6:$R6,1,COLUMN($H6:$R6)+1)="gm" - I assumed that this would look at the next sequential column for each column qualified in the MOD function and see if it equaled "gm".

    I'm guessing that the issue is that the column_num parameter of the INDEX function cannot be an array. When I evaluate the formula, when it evaluates COLUMN($H6:$R6), the result is 8. I would have expected it to show {8,9,10,11,12,13,14,15,16,17,18}, as it does when evaluating all of the other references to the array in the rest of the formula(s).

    I have tried searching around and haven't found anything regarding the limitations of the column_num parameter in the INDEX function. If anyone has any knowledge of this or if there is another solution I'm missing, any input would be much appreciated.

    Thank you.

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Dynamic column reference in INDEX array function.

    Hi and welcome to the forum!

    Perhaps this will be of assistance:

    http://excelxor.com/2014/09/05/index...ray-of-values/

    though the technique of using INDEX in this way is often unnecessary and can be replaced with alternative constructions.

    However, in order to help you further, it would be very useful to see an actual workbook with an example or two together with your desired results.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Dynamic column reference in INDEX array function.

    In this case, the range H6:R6 should be splitted into 3 ranges multiplied together : (positive/negative)*(converted into lb)*number

    1) positive/negative:
    ($H$6:$Q$6="+")-($H$6:$Q$6="-") = {1,0,0,-1,0,0,-1,0,0,1}

    2) converted into lb:
    IF($J$6:$S$6="gm",1/453.592,1) = {1,1,1,0.00220462442018378,1,1,1,1,1,0.00220462442018378}

    3) numbers:
    $I$6:$R$6 = {5,"lb","-",2,"gm","-",6,"lb","+",4}

    then SUM:

    =SUM(IFERROR((($H$6:$Q$6="+")-($H$6:$Q$6="-"))*IF($J$6:$S$6="gm",1/453.592,1)*$I$6:$R$6,0))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Attached Files Attached Files
    Quang PT

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Modify an IFERROR INDEX array function to LINK a cell to ROW reference
    By paulmacro in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-28-2015, 09:43 AM
  2. Dynamic Index/Match function when column location is unknown?
    By ac1987 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-10-2014, 09:47 PM
  3. Dynamic array in Index
    By nagaguru in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-01-2013, 09:01 PM
  4. Replies: 2
    Last Post: 11-05-2011, 03:26 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1