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]
On Wed, 20 Jan 2010 09:23:01 -0800, KKD
wrote:
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