Copying repeating formulae down but only incrementing singly

[WayneSav]

I have a formula in one cell which is referencing a value in a single cell. The same formula is in the cell below (referencing the same cell). Formula is

=IF(H1>0,"I",IF(H1<0,"C",""))

and this is in cells A1 and A2. I want to copy it down for all values in column H but repeating over the 2 rows, i.e. formula in A3 and A4 are =IF(H2>0,"I",IF(H2<0,"C",""))
Is there an easy way to copy down for all values in H?

 

[Fluff]

Hi & welcome to MrExcel.
How about

=LET(a,INDEX(H:H,CEILING.MATH(ROW(A1)/2,1)),IF(a>0,"I",IF(a<0,"C","")))

 

[WayneSav]

=LET(a,INDEX(H:H,CEILING.MATH(ROW(A1)/2,1)),IF(a>0,"I",IF(a<0,"C","")))

Thanks Fluff, that worked!

 

[bebo021999]

The key issue is that you need to figure out a formula that returns 1 for rows 1 and 2, 2 for rows 3 and 4, and so on. Then, use the INDEX function to retrieve the corresponding value from column H.

At row 1, the INT function will help: cell A1
INT((ROWS($1:1)-1)/2)+1

Then, using INDEX:
INDEX(H:H, INT((ROWS($1:1)-1)/2)+1)
This will return the value from column H (let's call it value).

After that, you could use IF to determine the result. However, since value might be lengthy and make the formula cumbersome, there's a more concise way using LOOKUP:

=LOOKUP(INDEX(H:H, INT((ROWS($1:1)-1)/2)+1), {-1000, 0, 0.000001}, {"C", "", "I"})
This formula will:

• Return "C" if value is between -1000 and 0 (the number -1000 can be adjusted to cover all possible negative values in your data).
• Return "" (empty) if value is between 0 and 0.000001 (meaning it equals 0).
• Return "I" if value is greater than 0.

Book1
	A	B	C	D	E	F	G	H
1	I							12
2	I							0
3								-5
4								6
5	C
6	C
7	I
8	I
9
10
Sheet1

Cell Formulas
Range	Formula
A1:A8	A1	=LOOKUP(INDEX(H:H,INT((ROWS($1:1)-1)/2)+1),{-1000,0,0.000001},{"C","","I"})

 

[Fluff]

Thanks Fluff, that worked!

You're welcome & thanks for the feedback.

 

[WayneSav]

The key issue is that you need to figure out a formula that returns 1 for rows 1 and 2, 2 for rows 3 and 4, and so on. Then, use the INDEX function to retrieve the corresponding value from column H.

At row 1, the INT function will help: cell A1
INT((ROWS($1:1)-1)/2)+1

Then, using INDEX:
INDEX(H:H, INT((ROWS($1:1)-1)/2)+1)
This will return the value from column H (let's call it value).

After that, you could use IF to determine the result. However, since value might be lengthy and make the formula cumbersome, there's a more concise way using LOOKUP:

=LOOKUP(INDEX(H:H, INT((ROWS($1:1)-1)/2)+1), {-1000, 0, 0.000001}, {"C", "", "I"})
This formula will:

• Return "C" if value is between -1000 and 0 (the number -1000 can be adjusted to cover all possible negative values in your data).
• Return "" (empty) if value is between 0 and 0.000001 (meaning it equals 0).
• Return "I" if value is greater than 0.

Book1
	A	B	C	D	E	F	G	H
1	I							12
2	I							0
3								-5
4								6
5	C
6	C
7	I
8	I
9
10
Sheet1

Cell Formulas
Range	Formula
A1:A8	A1	=LOOKUP(INDEX(H:H,INT((ROWS($1:1)-1)/2)+1),{-1000,0,0.000001},{"C","","I"})

Thanks Bebo021999