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
 

EduardS

New Member
Joined
Jul 25, 2010
Messages
13
It's a big model, with Columns F until CH and 9 other worksheets. Please help me if you know a solution!
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,165
Office Version
  1. 365
Platform
  1. Windows
It's a big model, with Columns F until CH and 9 other worksheets. Please help me if you know a solution!
And does each sheet just have a value in A1 and values in row 3 (columns F:CH) and formulas in row 4 (columns F:CH) like the sample or are there lots of rows?

If lots of rows, how are the rows of data and formulas arranged?
 

EduardS

New Member
Joined
Jul 25, 2010
Messages
13
Hi, I solved the problem by naming adding a maximum to the OFFSET function, that way my model stayed fast. Useful way to work your way around the volatility of the OFFSET function. At least in my experience.

Thanks for the help!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,605
Messages
5,523,839
Members
409,539
Latest member
Re1ease

This Week's Hot Topics

Top