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