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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

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
4
Views
69
Replies
9
Views
71
Replies
7
Views
108
Replies
0
Views
31
Replies
2
Views
93