Formula Increment

bullcreel

Board Regular
Joined
Aug 25, 2016
Messages
157
Office Version
  1. 2021
Platform
  1. Windows
=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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Instead of having to use the volatile INDIRECT function three times, I suggest using it once with the SWITCH function:
Book1
DEFGHIP
1
2$P30-3
3$P100-3
4$P170-3
5$P240-3
6$P312-3
7$P381-3
8$P450-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
230
240
250
260
270
280
290
301
311
321
331
341
351
361
372
382
392
402
412
422
432
443
453
463
473
483
493
503
Sheet2
Cell Formulas
RangeFormula
G2:G8G2="$P"&3+(ROW(P2)-2)*7
H2:H8H2=SWITCH(INDIRECT("$P"&3+(ROW(P2)-2)*7),1,2,2,1,0)
P9:P50P9=P2+1
 
Upvote 0
I am looking at it and not quite figuring out how they are used inside my formula.
 
Upvote 0
what do you mean? "They are used inside my formula"?
 
Upvote 0
See if this works for you. Replace your current formula with.
Excel Formula:
=SWITCH(OFFSET(BatNum!$P$3,MAX((ROW(BatNum!P3)-ROW(BatNum!$P$3))*7,0),0),0,2,1,1,0)
 
Upvote 0
I forgot I took out the sheet name. This is a correction to post in #2. Put this in cell H2, and drag down:

Excel Formula:
=SWITCH(INDIRECT("BatNum!$P"&3+(ROW(P2)-2)*7),1,2,2,1,0)
 
Upvote 0
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’)
 
Upvote 0
See if this works for you. Replace your current formula with.
Excel Formula:
=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.
 
Upvote 0
I forgot I took out the sheet name. This is a correction to post in #2. Put this in cell H2, and drag down:

Excel Formula:
=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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,015
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top