Dates and Arrays Driving Me Nuts - Help!!!

[Karl Burrows]

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?

 

[Patrick Molloy]

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

 

[Karl Burrows]

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!!

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

 

[Karl Burrows]

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.


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