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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,081
Office Version
365, 2010
Platform
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,081
Office Version
365, 2010
Platform
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
45,918
Office Version
365
Platform
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,770

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,770
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
45,918
Office Version
365
Platform
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,440
Messages
5,511,371
Members
408,844
Latest member
Utican2

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top