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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to determine number of current records by week
If the excel sheet properties and columns are not changing and you have and
know how to use access I believe it would be easier to just link that sheet to a access database and have a couple of queries to pull thte data that you are looking for. "Keith" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to determine number of current records by week
Keith,
Slightly different approach. If the date in L is a number and <= b3 If the date in M is a number, M, if date in N is a number, N, else TODAY If that date =b3 it gets counted =SUM((Sheet1!L$7:L$100)*(Sheet1!L$7:L$10<=Sheet2! B3)* (IF(ISNUMBER(Sheet1!M$7:M$10),Sheet1!M$7:M$10,IF(I SNUMBER(Sheet1!N$7:N$10),Sheet1!N$7:N$10,TODAY())) =Sheet2!B3)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Keith" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to determine number of current records by week
The trouble is function like OR and MAx return single value when used
with arrays. Try instead: =SUMPRODUCT((Sheet1!L$7:L$10000<=Sheet2!B3)*(Sheet 1!L$7:L$100000)* ((Sheet1!M$7:M$10000Sheet2!B3+Sheet1!N$7:N$10000 Sheet2!B3)0)) Note that sumproduct formulas don't need to be array-entered. On Feb 6, 2:35 pm, "Keith" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to determine number of current records by week
Brilliant! Thanks Bob!
Keith "Bob Phillips" wrote in message ... Keith, Slightly different approach. If the date in L is a number and <= b3 If the date in M is a number, M, if date in N is a number, N, else TODAY If that date =b3 it gets counted =SUM((Sheet1!L$7:L$100)*(Sheet1!L$7:L$10<=Sheet2! B3)* (IF(ISNUMBER(Sheet1!M$7:M$10),Sheet1!M$7:M$10,IF(I SNUMBER(Sheet1!N$7:N$10),Sheet1!N$7:N$10,TODAY())) =Sheet2!B3)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Keith" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to determine number of current records by week
Ah, that's a good learning for me. Is there a list of which functions (in
addition to OR and MAX) are subject to this restriction? Thanks Lori, Keith "Lori" wrote in message oups.com... The trouble is function like OR and MAx return single value when used with arrays. Try instead: =SUMPRODUCT((Sheet1!L$7:L$10000<=Sheet2!B3)*(Sheet 1!L$7:L$100000)* ((Sheet1!M$7:M$10000Sheet2!B3+Sheet1!N$7:N$10000 Sheet2!B3)0)) Note that sumproduct formulas don't need to be array-entered. On Feb 6, 2:35 pm, "Keith" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to determine number of current records by week
Hi Keith - I know the last post wasn't very clear.
The point is that all summary and logical functions which allow arrays as arguments such as sum, average, product, and, etc. will only ever return a single value. However there are a few functions that can return arrays when used with the offset and indirect functions, these include n, t, subtotal, sumif, countif and database-functions. On Feb 6, 3:59 pm, "Keith" wrote: Ah, that's a good learning for me. Is there a list of which functions (in addition to OR and MAX) are subject to this restriction? Thanks Lori, Keith "Lori" wrote in message oups.com... The trouble is function like OR and MAx return single value when used with arrays. Try instead: =SUMPRODUCT((Sheet1!L$7:L$10000<=Sheet2!B3)*(Sheet 1!L$7:L$100000)* ((Sheet1!M$7:M$10000Sheet2!B3+Sheet1!N$7:N$10000 Sheet2!B3)0)) Note that sumproduct formulas don't need to be array-entered. On Feb 6, 2:35 pm, "Keith" wrote: 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 |
Reply |
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 |