Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Any (6) consecutive entries
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Any (6) consecutive entries
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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Any (6) consecutive entries
Yes, that works, can you tell me how to "fill" this formula into each cell
without manually typing it? -- KKD "Luke M" wrote: 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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Any (6) consecutive entries
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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "KKD" wrote: Yes, that works, can you tell me how to "fill" this formula into each cell without manually typing it? -- KKD "Luke M" wrote: 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. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Any (6) consecutive entries
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want to align some entries in consecutive rows like Word tabs | Excel Discussion (Misc queries) | |||
Identifying Consecutive Entries | Excel Discussion (Misc queries) | |||
Prevent Consecutive Entries | Excel Worksheet Functions | |||
Adding Consecutive Entries | Excel Discussion (Misc queries) | |||
Adding Consecutive Entries | Excel Discussion (Misc queries) |