Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Array formulae question

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

    Default

    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 ]

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    North Alabama, USA
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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>

  3. #3
    New Member
    Join Date
    May 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]
    I find the Indirect function useful in cases like this. Try:

    =SUM(INDIRECT("D2:D"&(A2+1)))

    Regards,

    Pete.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,042
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    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 ]
    No need fo an array formula.

    =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

  5. #5
    New Member
    Join Date
    May 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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???

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    North Alabama, USA
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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>

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,042
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    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???
    Try what I suggested.

  8. #8
    New Member
    Join Date
    May 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    many thanks to you all. the offset function works a dream ;0)

    Cheers

    Peter

Some videos you may like

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
  •