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



.