Problem with alternate rows

[phil133]

Hi. I'm trying to make a formula that has the following results

A	B
5	5
12	5
7	12
93	12
56	7
20	7

<tbody>
</tbody>

B1 to be A1 but B2 also A1. B3 to be A2 but B4 also A2. B5 to be A3 but B6 also A3.

Thanks for any help!

 

[ukmikeb]

Try :-

=OFFSET($A$1,ROWS($B$1:$B1)-INT(ROWS($B$1:$B1)/2)-1,)

hth

 

[phil133]

Thanks! This works!

 

[FormR]

Just another option:

Excel 2012
	A	B
1	A	B
2	5	5
3	12	5
4	7	12
5	93	12
6	56	7
7	20	7
8		93
9		93
10		56
11		56
12		20
13		20
Sheet1

Cell Formulas
Range	Formula
B2	=INDEX($A$2:$A$7,ROUNDUP(ROWS($B$2:B2)/2,0))

 

[gaz_chops]

And another

=INDIRECT("A"&ROUNDUP(ROW()/2,0))

 

[FormR]

A word of warning for the OP -INDIRECT() and OFFSET() are both volatile functions.

Volatile Excel Functions -Decision Models

 

[phil133]

Amazing! Thank you all. I have another small problem but it's not very important so I don't want to open a new thread. In this formula, N241 is blank (has a formula that results blank) but the result of this comes up as false instead of blank. I don't care too much but just out of curiosity!

IF(N241<>"",IF(IFERROR(INDEX($F$4:$F$352,MATCH($N241,$A$4:$A$352,0)),"")=1,"-",IF(IFERROR(INDEX($F$4:$F$352,MATCH($N241,$A$4:$A$352,0)),"")=2,"+","")))

 

[gaz_chops]

Try

=IF(N241<>"",IF(IFERROR(INDEX($F$4:$F$352,MATCH($N241,$A$4:$A$352,0)),"")=1,"-",IF(IFERROR(INDEX($F$4:$F$352,MATCH($N241,$A$4:$A$352,0)),"")=2,"+","")),"")

 

[FormR]

You appear to missing the FALSE argument for the 1st IF() function:

=IF(N241<>"",IF(IFERROR(INDEX($F$4:$F$352,MATCH($N241,$A$4:$A$352,0)),"")=1,"-",IF(IFERROR(INDEX($F$4:$F$352,MATCH($N241,$A$4:$A$352,0)),"")=2,"+","")),"")

 

[phil133]

Thanks again!