Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Dates and Arrays Driving Me Nuts - Help!!!
This is a simple table and I should be able to figure this out, but....Chip
and John, where are you!!! I posted a message earlier, but as I have gotten into the formulas more, I have found the post doesn't fully get to the root of my problem. Here's the scenario...I have a several tables that record budgeted sales for our products. Each table has rows of date ranges (ex. 1/1/03 to 3/31/03) and corresponding budgeted and actual sales. The tables continue to show date ranges until the end of the product life cycle (anywhere from 1 to 6 years). Example: Beg Date End Date Unit $ Budget Units Act Units YTD Units Over/Under Jan 1 Mar 31 $3.50 10 8 8 (2) Apr 1 Jun 30 $3.50 7 8 16 (1) Jul 1 Sept 31 $4.00 3 5 21 1 What I want to do is create a summary report each month showing which products have an end date that is in the current month (ex. an end date may be 6/15/03, but it falls in the current end date of 6/31 above, so it would show on the summary). I also want to show if the previous period range is under budget in the same summary (ex. current period 6/03 in range 4/1/03 to 6/30/03 and previous period 1/1/03 to 3/31/03 is under budget 2 units, so it would show with the end date due) Here are my issues... 1. I have the formula to find the current period due: {=SUM(IF((BOP!$C$29:$C$71=$I$2)*(BOP!$C$29:$C$71< =$I$3),(BOP!$F$29:$F$71)," "))} I2 is beginning of month and I3 is EOM and C range is End Date range. Works, no problems...I also need to be able to show the end date along with the value (ex. 7 items budgeted, due date 6/30/03). How can I get a formula to return the value of the End Date that is within the current month? 2. I have an array formula that looks through the date ranges (Start Date and End Date) to find the range that occurs in this period (current month) and then looks to the previous period to get the variance. Here's a sample formula: {=SUM(IF(($I$2=BOP!$B$29:$B$71)*($I$3<=BOP!$C$29: $C$71),OFFSET(BOP!$J$29:$J $71,-1,0),""))} The problem is it works great if the date ranges start at the beginning of a month, but does not work if the start date is anywhere else through the month (say one period ends May 1st and the next period begins May 2). I can't figure out how to tell it to look at both ranges of values to see if it falls in the current month. 3. There are instances where pricing changes may occur mid-period, so I have 2 duplicate date ranges, with different pricing for each. For the variance part, my formula will add the two previous periods from the last current date range instead of the one form the correct period. Using OFFSET(Range,-2,0) will work, but I need something to trigger the event to tell the formula there are 2 instances of the same date. I think the best way to attack this one is create another formula in a new column to count the instances of that date range and if 2, use the new formula. How can I count the instance of the date occurance? -- Remove 'spam' from email address to contact me directly |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dates and Arrays Driving Me Nuts - Help!!!
I think its a case of can't see the wood for the trees
here. So let's step back a moment, and consider what we have. First you have a date, then you need to see which date band it falls in. From the table you sent, it is clear that your periods are calendar quarters, Jan-Mar,Apr- Jun,Jul-Sep, Oct-Dec We can now easily place our date in a band with a simple MOnth() function,, eg for 6/15/03 Month is 6 and our band is 2 .... =INT((Month(MyDate)-1)/3) MyDate could be a cell reference, but must be a date. The formula will return a number from 1 to 4 reflecting the quarter, say ThisQtr = INT((Month(MyDate)-1)/3) Now that we know the current quarter, the previous quarter would be ThisQtr-1 Now we know the qtr our date references, and our previous qtr , its relatively simple to obtain values. So for example if row 1 is headers, you could get the previous qrtr's under prod amount with =OFFSET(A1,qrtr,4) HTH Patrick Molloy Mictrosoft Excel MVP -----Original Message----- This is a simple table and I should be able to figure this out, but....Chip and John, where are you!!! I posted a message earlier, but as I have gotten into the formulas more, I have found the post doesn't fully get to the root of my problem. Here's the scenario...I have a several tables that record budgeted sales for our products. Each table has rows of date ranges (ex. 1/1/03 to 3/31/03) and corresponding budgeted and actual sales. The tables continue to show date ranges until the end of the product life cycle (anywhere from 1 to 6 years). Example: Beg Date End Date Unit $ Budget Units Act Units YTD Units Over/Under Jan 1 Mar 31 $3.50 10 8 8 (2) Apr 1 Jun 30 $3.50 7 8 16 (1) Jul 1 Sept 31 $4.00 3 5 21 1 What I want to do is create a summary report each month showing which products have an end date that is in the current month (ex. an end date may be 6/15/03, but it falls in the current end date of 6/31 above, so it would show on the summary). I also want to show if the previous period range is under budget in the same summary (ex. current period 6/03 in range 4/1/03 to 6/30/03 and previous period 1/1/03 to 3/31/03 is under budget 2 units, so it would show with the end date due) Here are my issues... 1. I have the formula to find the current period due: {=SUM(IF((BOP!$C$29:$C$71=$I$2)*(BOP!$C$29:$C$71 <=$I$3), (BOP!$F$29:$F$71)," "))} I2 is beginning of month and I3 is EOM and C range is End Date range. Works, no problems...I also need to be able to show the end date along with the value (ex. 7 items budgeted, due date 6/30/03). How can I get a formula to return the value of the End Date that is within the current month? 2. I have an array formula that looks through the date ranges (Start Date and End Date) to find the range that occurs in this period (current month) and then looks to the previous period to get the variance. Here's a sample formula: {=SUM(IF(($I$2=BOP!$B$29:$B$71)*($I$3<=BOP! $C$29:$C$71),OFFSET(BOP!$J$29:$J $71,-1,0),""))} The problem is it works great if the date ranges start at the beginning of a month, but does not work if the start date is anywhere else through the month (say one period ends May 1st and the next period begins May 2). I can't figure out how to tell it to look at both ranges of values to see if it falls in the current month. 3. There are instances where pricing changes may occur mid-period, so I have 2 duplicate date ranges, with different pricing for each. For the variance part, my formula will add the two previous periods from the last current date range instead of the one form the correct period. Using OFFSET(Range,-2,0) will work, but I need something to trigger the event to tell the formula there are 2 instances of the same date. I think the best way to attack this one is create another formula in a new column to count the instances of that date range and if 2, use the new formula. How can I count the instance of the date occurance? -- Remove 'spam' from email address to contact me directly . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dates and Arrays Driving Me Nuts - Help!!!
I just setup the dates as an example. They do not necessarily run quarter
to quarter. It depends on the product, etc. It can be monthly, quarterly, 90 days or there may be variations within a table even though it may be quarterly (ie, it may start as monthly for a promo on sales and then go quarterly). I can't depend on a set period range for the tables. Thanks!! "Patrick Molloy" wrote in message ... I think its a case of can't see the wood for the trees here. So let's step back a moment, and consider what we have. First you have a date, then you need to see which date band it falls in. From the table you sent, it is clear that your periods are calendar quarters, Jan-Mar,Apr- Jun,Jul-Sep, Oct-Dec We can now easily place our date in a band with a simple MOnth() function,, eg for 6/15/03 Month is 6 and our band is 2 .... =INT((Month(MyDate)-1)/3) MyDate could be a cell reference, but must be a date. The formula will return a number from 1 to 4 reflecting the quarter, say ThisQtr = INT((Month(MyDate)-1)/3) Now that we know the current quarter, the previous quarter would be ThisQtr-1 Now we know the qtr our date references, and our previous qtr , its relatively simple to obtain values. So for example if row 1 is headers, you could get the previous qrtr's under prod amount with =OFFSET(A1,qrtr,4) HTH Patrick Molloy Mictrosoft Excel MVP -----Original Message----- This is a simple table and I should be able to figure this out, but....Chip and John, where are you!!! I posted a message earlier, but as I have gotten into the formulas more, I have found the post doesn't fully get to the root of my problem. Here's the scenario...I have a several tables that record budgeted sales for our products. Each table has rows of date ranges (ex. 1/1/03 to 3/31/03) and corresponding budgeted and actual sales. The tables continue to show date ranges until the end of the product life cycle (anywhere from 1 to 6 years). Example: Beg Date End Date Unit $ Budget Units Act Units YTD Units Over/Under Jan 1 Mar 31 $3.50 10 8 8 (2) Apr 1 Jun 30 $3.50 7 8 16 (1) Jul 1 Sept 31 $4.00 3 5 21 1 What I want to do is create a summary report each month showing which products have an end date that is in the current month (ex. an end date may be 6/15/03, but it falls in the current end date of 6/31 above, so it would show on the summary). I also want to show if the previous period range is under budget in the same summary (ex. current period 6/03 in range 4/1/03 to 6/30/03 and previous period 1/1/03 to 3/31/03 is under budget 2 units, so it would show with the end date due) Here are my issues... 1. I have the formula to find the current period due: {=SUM(IF((BOP!$C$29:$C$71=$I$2)*(BOP!$C$29:$C$71 <=$I$3), (BOP!$F$29:$F$71)," "))} I2 is beginning of month and I3 is EOM and C range is End Date range. Works, no problems...I also need to be able to show the end date along with the value (ex. 7 items budgeted, due date 6/30/03). How can I get a formula to return the value of the End Date that is within the current month? 2. I have an array formula that looks through the date ranges (Start Date and End Date) to find the range that occurs in this period (current month) and then looks to the previous period to get the variance. Here's a sample formula: {=SUM(IF(($I$2=BOP!$B$29:$B$71)*($I$3<=BOP! $C$29:$C$71),OFFSET(BOP!$J$29:$J $71,-1,0),""))} The problem is it works great if the date ranges start at the beginning of a month, but does not work if the start date is anywhere else through the month (say one period ends May 1st and the next period begins May 2). I can't figure out how to tell it to look at both ranges of values to see if it falls in the current month. 3. There are instances where pricing changes may occur mid-period, so I have 2 duplicate date ranges, with different pricing for each. For the variance part, my formula will add the two previous periods from the last current date range instead of the one form the correct period. Using OFFSET(Range,-2,0) will work, but I need something to trigger the event to tell the formula there are 2 instances of the same date. I think the best way to attack this one is create another formula in a new column to count the instances of that date range and if 2, use the new formula. How can I count the instance of the date occurance? -- Remove 'spam' from email address to contact me directly . |
#4
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Dates and Arrays Driving Me Nuts - Help!!!
Sorry about 2 & 3. "I" corresponds to the beginning and ending dates of the
current month and "J" is the Over/Under Budget Column. I'll work on these formulas and see what they do. Thanks for the help on this! I have been beating my head against a wall trying to figure this one out. "Jerry W. Lewis" wrote in message ... 1. Use the array formula {=MAX(IF((BOP!$C$29:$C$71=$I$2)*(BOP!$C$29:$C$71< =$I$3),(BOP!$C$29:$C$71)," "))} Also, you can simplify your original formula to =SUMPRODUCT((BOP!$C$29:$C$71=$I$2)*(BOP!$C$29:$C$ 71<=$I$3),(BOP!$F$29:$F$71 )) which does not have to be array entered. 2 & 3. Your description is inadequate. Assuming that the data in your "Example" table is in columns BOP!B to BOP!H, you still have not indicated what is in column BOP!J. Jerry Karl Burrows wrote: This is a simple table and I should be able to figure this out, but....Chip and John, where are you!!! I posted a message earlier, but as I have gotten into the formulas more, I have found the post doesn't fully get to the root of my problem. Here's the scenario...I have a several tables that record budgeted sales for our products. Each table has rows of date ranges (ex. 1/1/03 to 3/31/03) and corresponding budgeted and actual sales. The tables continue to show date ranges until the end of the product life cycle (anywhere from 1 to 6 years). Example: Beg Date End Date Unit $ Budget Units Act Units YTD Units Over/Under Jan 1 Mar 31 $3.50 10 8 8 (2) Apr 1 Jun 30 $3.50 7 8 16 (1) Jul 1 Sept 31 $4.00 3 5 21 1 What I want to do is create a summary report each month showing which products have an end date that is in the current month (ex. an end date may be 6/15/03, but it falls in the current end date of 6/31 above, so it would show on the summary). I also want to show if the previous period range is under budget in the same summary (ex. current period 6/03 in range 4/1/03 to 6/30/03 and previous period 1/1/03 to 3/31/03 is under budget 2 units, so it would show with the end date due) Here are my issues... 1. I have the formula to find the current period due: {=SUM(IF((BOP!$C$29:$C$71=$I$2)*(BOP!$C$29:$C$71< =$I$3),(BOP!$F$29:$F$71)," "))} I2 is beginning of month and I3 is EOM and C range is End Date range. Works, no problems...I also need to be able to show the end date along with the value (ex. 7 items budgeted, due date 6/30/03). How can I get a formula to return the value of the End Date that is within the current month? 2. I have an array formula that looks through the date ranges (Start Date and End Date) to find the range that occurs in this period (current month) and then looks to the previous period to get the variance. Here's a sample formula: {=SUM(IF(($I$2=BOP!$B$29:$B$71)*($I$3<=BOP!$C$29: $C$71),OFFSET(BOP!$J$29:$J $71,-1,0),""))} The problem is it works great if the date ranges start at the beginning of a month, but does not work if the start date is anywhere else through the month (say one period ends May 1st and the next period begins May 2). I can't figure out how to tell it to look at both ranges of values to see if it falls in the current month. 3. There are instances where pricing changes may occur mid-period, so I have 2 duplicate date ranges, with different pricing for each. For the variance part, my formula will add the two previous periods from the last current date range instead of the one form the correct period. Using OFFSET(Range,-2,0) will work, but I need something to trigger the event to tell the formula there are 2 instances of the same date. I think the best way to attack this one is create another formula in a new column to count the instances of that date range and if 2, use the new formula. How can I count the instance of the date occurance? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple problem with Dates and Times is driving me nuts | Excel Worksheet Functions | |||
Need Help, this is driving me nuts | Excel Discussion (Misc queries) | |||
question driving me nuts | New Users to Excel | |||
question driving me nuts | Excel Discussion (Misc queries) | |||
Sum and Count are driving me nuts!! | Charts and Charting in Excel |