Variable conditional formatting

EduardS

New Member
Joined
Jul 25, 2010
Messages
13
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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,204
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Jul 25, 2010
Messages
13
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
Joined
May 26, 2009
Messages
17,204
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
46,226
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jul 25, 2010
Messages
13
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
Joined
Jan 15, 2007
Messages
23,779

ADVERTISEMENT

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
Joined
Jul 25, 2010
Messages
13
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
Joined
Jan 15, 2007
Messages
23,779
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
Joined
May 28, 2005
Messages
46,226
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,940
Messages
5,525,759
Members
409,665
Latest member
littleriver

This Week's Hot Topics

Top