# Variable conditional formatting

#### EduardS

##### New Member
Hi,

Cell A1 contains a variable number, let's say "4"
Row B: cellscontain periods (say 100 periods)
Row C: cells contains either "1" or "0"
Row D: Here I want to add a formula that returns "1" if the number for the corresponding period in row C is 1, but (this is the difficulty) only if the 4 preceding periods in Row C also have a value of 1. The number of preceding periods should be variable (and will be manually adjustable in cell A1).

I hope this is clear, if not please let me know. Can anyone help me?

Thanks!

Eduard

#### EduardS

##### New Member
It's a big model, with Columns F until CH and 9 other worksheets. Please help me if you know a solution!

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### Peter_SSs

##### MrExcel MVP, Moderator
It's a big model, with Columns F until CH and 9 other worksheets. Please help me if you know a solution!
And does each sheet just have a value in A1 and values in row 3 (columns F:CH) and formulas in row 4 (columns F:CH) like the sample or are there lots of rows?

If lots of rows, how are the rows of data and formulas arranged?

#### EduardS

##### New Member
Hi, I solved the problem by naming adding a maximum to the OFFSET function, that way my model stayed fast. Useful way to work your way around the volatility of the OFFSET function. At least in my experience.

Thanks for the help!

Replies
4
Views
69
Replies
9
Views
71
Replies
7
Views
108
Replies
0
Views
32
Replies
2
Views
93