The short answer is yes, but check out
http://www.cpearson.com/excel/array.htm
The only thing is the ranges must be the same.
Is it possible to use an array where the variable is a cell reference? This is what I have so far, which is using nested IF statements instead...
=IF(A2=1,D2,IF(A2=3,SUM(D2:F2),IF(A2=6,SUM(D2:I2),IF(A2=9,SUM(D2:L2),IF(A2=12,SUM(D2:O2),IF(A2=15,SUM(D2:R2),"X"))))))
Ideally I would like to refer to the number in A2 and sum that many cells, I have 60 columns of data!
Any ideas???
[ This Message was edited by: Peter Adey on 2002-05-08 05:20 ]
The short answer is yes, but check out
http://www.cpearson.com/excel/array.htm
The only thing is the ranges must be the same.
Hope this helps!
Rocky
<h6>"Be not the first by whom the New are try'd,
Nor yet the last to lay the Old aside."
Alexander Pope (1688-1744).</h6>
I find the Indirect function useful in cases like this. Try:On 2002-05-08 05:19, Peter Adey wrote:
Is it possible to use an array where the variable is a cell reference? This is what I have so far, which is using nested IF statements instead...
=IF(A2=1,D2,IF(A2=3,SUM(D2:F2),IF(A2=6,SUM(D2:I2),IF(A2=9,SUM(D2:L2),IF(A2=12,SUM(D2:O2),IF(A2=15,SUM(D2:R2),"X"))))))
Ideally I would like to refer to the number in A2 and sum that many cells, I have 60 columns of data!
Any ideas???
[ This Message was edited by: Peter Adey on 2002-05-08 05:20 ]
=SUM(INDIRECT("D2:D"&(A2+1)))
Regards,
Pete.
No need fo an array formula.On 2002-05-08 05:19, Peter Adey wrote:
Is it possible to use an array where the variable is a cell reference? This is what I have so far, which is using nested IF statements instead...
=IF(A2=1,D2,IF(A2=3,SUM(D2:F2),IF(A2=6,SUM(D2:I2),IF(A2=9,SUM(D2:L2),IF(A2=12,SUM(D2:O2),IF(A2=15,SUM(D2:R2),"X"))))))
Ideally I would like to refer to the number in A2 and sum that many cells, I have 60 columns of data!
Any ideas???
[ This Message was edited by: Peter Adey on 2002-05-08 05:20 ]
=SUM(OFFSET(D2,0,0,1,A2))
will suffice.
Or, with control on A2,
=IF(AND(A2>=1,A2<=15),SUM(OFFSET(D2,0,0,1,A2)),"X")
Aladin
Thanks for the quick reply Rocky
The problem is I don't want sum up the values = A2 . I want to total the number of cells = A2. So,
IF a2= 9 then sum 9 cells etc, etc
any ideas???
Hi Peter,
Well I for one am still a tad bit confused. I will try to restate your problem and you correct me if necessary.
A2 has a number from 1 to 10 and column B has a list of amounts from $1.00 to $10.00. If A2=3 then you want to return $6.00.
I think Aldin was right on track. Consider:
=SUM(B1:OFFSET(B1,A2,0))
Hope this helps!
Rocky
<h6>"Be not the first by whom the New are try'd,
Nor yet the last to lay the Old aside."
Alexander Pope (1688-1744).</h6>
Try what I suggested.On 2002-05-08 06:05, Peter Adey wrote:
Thanks for the quick reply Rocky
The problem is I don't want sum up the values = A2 . I want to total the number of cells = A2. So,
IF a2= 9 then sum 9 cells etc, etc
any ideas???
many thanks to you all. the offset function works a dream ;0)
Cheers
Peter
Like this thread? Share it with others