Any (6) consecutive entries

[KKD]

I have a table that shows number of hours worked each month in columns (a
single row for each employee). I want my table to alert me when the sum of
any six consecutive months equals 400.

I wanted to use conditional formatting, but can't figure out how to write
the formula to look at the sum of "any six consecutive entries"

-
KKD

 

[Luke M]

Starting with the 1st or 6th month of data (which I'll assume is column G),
conditional format:
=SUM(B2:G2)=400
A highlighted cell will indicate either the beginning or end of the 6 month
block.

 

[KKD]

Yes, that works, can you tell me how to "fill" this formula into each cell
without manually typing it?

 

[Luke M]

Sure. You can either Copy, paste special, formatting

or

Select all the cells you want formatted, and then with B2 being the active
cell, input the conditional formula. With no absolute references in the
formula, each cell will adjust the formula to match its correct reference.

 

[Chip Pearson]

Use a formula like the following for the format condition in
Conditional Formatting.

=SUM(OFFSET(B5,0,0,1,6))>400

Change the "B5" to the top left cell of the data to which CF should be
applied.

If you use this formula in a cell rather than in CF, you need to enter
it as an array formula. (CF takes care of that automatically.) You
must press CTRL SHIFT ENTER
rather than just ENTER when you first enter the formula and whenever
you edit it later. If you do this properly, Excel will display
the formula enclosed in curly braces { }. You do not type
in the braces -- Excel puts them in automatically. The
formula will not work properly if you do not enter it with
CTRL SHIFT ENTER. For much more information about array
formulas, see http://www.cpearson.com/Excel/ArrayFormulas.aspx.



Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]