# Variable conditional formatting

#### EduardS

##### New Member
Hi,

Could someone please advise on how to tackle the following problem:

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

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### JoeMo

##### MrExcel MVP
Hi,

Could someone please advise on how to tackle the following problem:

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
Not sure I understand where the conditional formatting comes into play, but here's an attempt to do what I think you want.
Excel Workbook
ABCD
14
21
31
41
51
611
70
80
90
101
111
121
131
1411
1511
1611
170
181
191
201
Sheet3

#### EduardS

##### New Member
Thanks!

I think I used the wrong title, maybe 'conditional formula' is better.
Thanks for your help, I made one more mistake, instead of 'rows' I should have said 'column'. I do think however, tat your answer is however exactly what I need! Only for columns instead of rows. How can I change that?

And if I change the number in cell A1 to for example 6 it will look at the 6 preceding periods right? Same for 2, or any other number?

Thanks!

Eduard

#### JoeMo

##### MrExcel MVP
Thanks!

I think I used the wrong title, maybe 'conditional formula' is better.
Thanks for your help, I made one more mistake, instead of 'rows' I should have said 'column'. I do think however, tat your answer is however exactly what I need! Only for columns instead of rows. How can I change that?

And if I change the number in cell A1 to for example 6 it will look at the 6 preceding periods right? Same for 2, or any other number?

Thanks!

Eduard
What I gave you is set up for columns A, B, C and D. Yes, the value you enter in A1 determines how many preceding periods with 1's in column C are required to return a 1 to column D.

#### Peter_SSs

##### MrExcel MVP, Moderator

If I have understood correctly, here is an alternative avoiding the volatile OFFSET function

Formula in A4 (adjust the \$AZ in my formula to include all your columns) is copied across.

Excel Workbook
ABCDEFGHIJKLMNOPQRST
14
2
31111100011111110111
4 1111
sequence

#### EduardS

##### New Member
Thanks Peter_SSs, your formula seems to work, but makes my worksheet very, very slow. It takes a couple of minutes to recalculate (if it doesn't crash). Instead I would like to use JoeMo's solution, however he assumed an vertical model, whereas I have an horizontal model (Peter_SSs has is correctly), can anyone please help me how I should rewrite JoeMo's formula for such a model, or knows another formula I could try which doesn't slow the model down that strongly?

Thanks!

#### mikerickson

##### MrExcel MVP

Names would be useful for this.

Using the layout in post#2
You can select D2 and define the relatively referenced range

Name: BottomCell
RefersTo: =Sheet3!\$C1

and the calculated ranges

Name: TopCell
RefersTo: =INDEX(Sheet3!\$C:C, 1+ROW(BottomCell)-Sheet3!\$A\$1, 1)

Name: DataRange
RefersTo: =TopCell:BottomCell

Then putting this formula in D2 and dragging down should do what you want.
= (C2=1)*(SUM(dataRange)=\$A\$1)

#### EduardS

##### New Member
Thanks, but could you help me how do this when using the layout as in posting 5? (I'm using the horizontal layout, not the vertical layout). Thanks!

#### mikerickson

##### MrExcel MVP
Select L3 and define the names

RightCell =Sheet1!K\$3
LeftCell =INDEX(Sheet1!\$3:\$3, 1, COLUMN(RightCell)-Sheet1!\$A\$1)
DataRAnge =LeftCell:RightCell

Then put =--(SUM(DataRange)=\$A\$1+1) in B4 and drag right.

#### Peter_SSs

##### MrExcel MVP, Moderator
Thanks Peter_SSs, your formula seems to work, but makes my worksheet very, very slow. It takes a couple of minutes to recalculate (if it doesn't crash). Instead I would like to use JoeMo's solution, however he assumed an vertical model, whereas I have an horizontal model (Peter_SSs has is correctly), can anyone please help me how I should rewrite JoeMo's formula for such a model, or knows another formula I could try which doesn't slow the model down that strongly?

Thanks!
JoeMo suggested a solution using the OFFSET function. Because OFFSET is a volatile function I would expect it to slow your sheet considerably more than the suggestuion I made, so I doubt it will be the best solution in the end.

Perhaps we need to know more about the overall size and layout of your sheet.

Replies
0
Views
817
Replies
0
Views
165
Replies
0
Views
204
Replies
0
Views
392
Replies
7
Views
205

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,486
Messages
5,831,968
Members
430,098
Latest member
bemmelen

### 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?

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