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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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)
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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