Results 1 to 6 of 6
Thread: Simple? array formula (2000)

20031125, 13:54 #1
 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.

20031125, 14:06 #2
 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()/21,0,1,1),"")
Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20031125, 14:12 #3
 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

20031126, 08:47 #4
 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.

20031126, 09:36 #5
 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 evennumbered rows and 1 for oddnumbered rows. Since FALSE = 0 and TRUE = 1, you can use this in an IF function.
On an oddnumbered 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 evennumbered row, the formula returns "", i.e. an empty string.

20031126, 13:30 #6
 Join Date
 Mar 2001
 Posts
 989
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Simple? array formula (2000)
Excellent.