Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to determine number of current records by week
In Sheet1, I have records in each row. The following information is part of
each record Column L (L7:L1000) = start date for each record Column M (M7:M1000) = initially planned end date for each record Column N (N7:N1000) = revised end date for each record [these ranges may contain blank or non-numeric values like "tbd"] Now in Sheet2, I'm trying to build a list of values to sum up how many projects were actually active during each week- so for a target date "X", how many records had a start date before X and the end date or revised end date after X. I'm pulling this data from someone else's workbook, so I don't have much control over how clean the source data is, and I'm trying to build a formula to get the data we need and I'm close, but don't quite have it. Any help would be appreciated. I've started by placing in Sheet2,Column B the comparison dates (1/106, 1/8/06, 1/15/06, etc.) I'm trying to use an Array formula in Sheet2 (column C) that references the date in column B to get all records that were active during that span. The following is intended to determine: (1) is there a start date (startdate value 0) to make sure it isn't a blank row (2) is the start date prior to the date in this same row in Sheet2 column B (the week I'm trying to pull data for) (3) is the end date /or/ revised end date after the date in this same row in Sheet2 column B (showing that the project ended or will end after the week I'm pulling data for) {=SUM(IF((Sheet1!L$7:L$10000<=Sheet2!B3)*(Sheet1!L $7:L$100000)*OR(Sheet1!M$7:M$10000Sheet2!B3,Shee t1!N$7:N$10000Sheet2!B3),1,0))} translated to english: if the startdate <comparison date, and startdate 0, and either the enddate or revised enddate is greater than the comparison date, count the record. There is something wrong with the latter half of this formula, because I get a running total by week that increments as records are started, but the running list of values doesn't decrement as records pass their end date. I also tried a variation of the above, with an enddate checked using (MAX(Sheet1!M$7:M$10000,Sheet1!N$7:N$10000)Sheet2 !B3) but that didn't seem to work either. Any suggestions would be greatly appreciated! Thanks, Keith |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup to Return a Range of Data | Excel Discussion (Misc queries) | |||
Show week number in current month | Excel Discussion (Misc queries) | |||
How do I convert a formula calculated number to a current value ? | New Users to Excel | |||
Formula to determine whether number falls within range?? | Excel Worksheet Functions | |||
Formula to determine number of Standard Deviations based on % of population | Excel Worksheet Functions |