Formula Increment

[bullcreel]

=IF(BatNum!$P3=0,2,IF(BatNum!$P3=1,1,IF(BatNum!$P3=2,1,IF(BatNum!$P3>=3,0))))
I have this formula in cell "H2". What i want it to do is when I copy it down i want it to check $P10 next, $P17, then $P24 and so on every 7 cells. Is this possible?

Thanks in advance

Jim

 

[awoohaw]

Instead of having to use the volatile INDIRECT function three times, I suggest using it once with the SWITCH function:

Book1
	D	E	F	G	H	I							P
1
2				$P3	0								-3
3				$P10	0								-3
4				$P17	0								-3
5				$P24	0								-3
6				$P31	2								-3
7				$P38	1								-3
8				$P45	0								-3
9													-2
10													-2
11													-2
12													-2
13													-2
14													-2
15													-2
16													-1
17													-1
18													-1
19													-1
20													-1
21													-1
22													-1
23													0
24													0
25													0
26													0
27													0
28													0
29													0
30													1
31													1
32													1
33													1
34													1
35													1
36													1
37													2
38													2
39													2
40													2
41													2
42													2
43													2
44													3
45													3
46													3
47													3
48													3
49													3
50													3
Sheet2

Cell Formulas
Range	Formula
G2:G8	G2	="$P"&3+(ROW(P2)-2)*7
H2:H8	H2	=SWITCH(INDIRECT("$P"&3+(ROW(P2)-2)*7),1,2,2,1,0)
P9:P50	P9	=P2+1

 

[bullcreel]

I am looking at it and not quite figuring out how they are used inside my formula.

 

[awoohaw]

what do you mean? "They are used inside my formula"?

 

[Cubist]

See if this works for you. Replace your current formula with.

=SWITCH(OFFSET(BatNum!$P$3,MAX((ROW(BatNum!P3)-ROW(BatNum!$P$3))*7,0),0),0,2,1,1,0)

 

[awoohaw]

I forgot I took out the sheet name. This is a correction to post in #2. Put this in cell H2, and drag down:

=SWITCH(INDIRECT("BatNum!$P"&3+(ROW(P2)-2)*7),1,2,2,1,0)

 

[Fluff]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[bullcreel]

See if this works for you. Replace your current formula with.

=SWITCH(OFFSET(BatNum!$P$3,MAX((ROW(BatNum!P3)-ROW(BatNum!$P$3))*7,0),0),0,2,1,1,0)

It worked for cell H2, but not changing to row P10 and then P17, etc. like i need it to for the next cells.

 

[bullcreel]

I forgot I took out the sheet name. This is a correction to post in #2. Put this in cell H2, and drag down:

=SWITCH(INDIRECT("BatNum!$P"&3+(ROW(P2)-2)*7),1,2,2,1,0)

Its not checking the values for the right row in BatNum. After checking the values on BatNum!P3, it should then check values on P10, then P17 and so on. Thanks for the help so far

 

[bullcreel]

What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I am using Excel 2021.