Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Simple? array formula (2000)

    I have values in cells A1 to A3 and want to create an array formula in B1 to B6 that will copy the contents of A1 through A3, with a blank cell in between each. Any ideas?! Andy.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple? array formula (2000)

    =IF(MOD(ROW(),2)=0,OFFSET($A$1,ROW()/2-1,0,1,1),"")
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple? array formula (2000)

    You can't do that with an array formula, but the formula below, pasted in B1 and copied down should do what you want.

    <pre>=IF(MOD(ROW(),2),INDIRECT("A"&(INT(ROW()/2)+1)),"")
    </pre>

    Legare Coleman

  4. #4
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple? array formula (2000)

    That's great, thank you. Can you try and explain how this works? I'm particularly interested in how Row() works without any arguments. Andy.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Simple? array formula (2000)

    =IF(MOD(ROW(),2),INDIRECT("A"&(INT(ROW()/2)+1)),"")

    ROW() returns the row number for the cell that contains the formula.

    MOD returns the remainder of the first argument after division by the second argument, so MOD(ROW(),2) returns 0 for even-numbered rows and 1 for odd-numbered rows. Since FALSE = 0 and TRUE = 1, you can use this in an IF function.

    On an odd-numbered row, the formula returns INDIRECT("A"&(INT(ROW()/2)+1)). The row number is divided by 2, the fractional part is discarded and then 1 is added, so

    1 -> 0 -> 1
    3 -> 1 -> 2
    5 -> 2 -> 3

    The INDIRECT function returns the value of cell A1 in row 1, A2 in row 3 and A3 in row 5.

    On an even-numbered row, the formula returns "", i.e. an empty string.

  6. #6
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple? array formula (2000)

    Excellent.

Posting Permissions

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