# Thread: Simple? array formula (2000)

1. ## 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. ## Re: Simple? array formula (2000)

=IF(MOD(ROW(),2)=0,OFFSET(\$A\$1,ROW()/2-1,0,1,1),"")

3. ## 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>

4. ## 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. ## 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. ## 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
•