Functions for inventory

[J-LL]

The boss wants an inventory count of cars on any given pass date. The
existing table has a DELIVERED date field, for when the car is entered in
inventory; and a RELEASED date field, for when the car is sold and taken out
of inventory.

For example, the boss may want to know how many cars were in inventory on
May 2nd 2007, or June 3rd 2008, or July 25th 2008, and so on.

Thanks for your help.

 

[Bernie Deitrick]

JLL,

=SUMPRODUCT((DeliveryDates<=DateOf Interest)*(ReleasedDates>=DateOf Interest)
Like this, with the date of interest in cell A2, delivery dates in column B, and release dates in C
=SUMPRODUCT((B2:B1000<=A2)*(C2:C1000>=A2)
Or possibly this, depending how you want to handle delivery and releases that occur on the date of
interest.
=SUMPRODUCT((B2:B1000<A2)*(C2:C1000>A2)

HTH,
Bernie
MS Excel MVP

 

[J-LL]

Thanks for responding Bernie.
The formula doen't always work: Running the formula on earlier dates seems
to give the right answers, but more recent dates do not. I think it's because
of the dates (or rows) that don't have a RELEASE date yet (blank cell)....
Any help will be appreaciated.
J-LL

 

[Bernie Deitrick]

This will count those where the Release date is not filled in:

=SUMPRODUCT((DeliveryDates<=DateOf Interest)*((ReleasedDates>=DateOf Interest) +
(ReleasedDates="")))

HTH,
Bernie
MS Excel MVP

 

[J-LL]

Hi Bernie,
Still doesn't work: now I'm getting values in the hundreds when they should
be in the twenties and thirties. I tried moving the parentheses around but
that didn't work either...

 

[Bernie Deitrick]

Are your 'dates' actual dates, or are they text strings that look like dates? That is a common
problem with date values.

Try formatting the cells for number with two decimals. They should change from looking like dates
to looking like numbers if they are actual dates. If they don't change, you could also use

=SUMPRODUCT((DATEVALUE(DeliveryDates)<=DATEVALUE(DateOf Interest))*
((DATEVALUE(ReleasedDates)>=DATEVALUE(DateOf Interest)) + (ReleasedDates="")))

But DATEVALUE will produce an error if any of the values are actual dates.

HTH,
Bernie
MS Excel MVP

 

[J-LL]

The dates are formatted correctly.

Your formula should work...the logic makes sence to me. Seems to be missing
another criteria...
I have the option to switch over to Access because this table is also in
Access format. I'm just more proficient at working in Excel.
Thanks for your help Bernie.

 

[Bernie Deitrick]

If you have problems with the formula, shoot me a copy of the workbook, with any extra information
removed.

HTH,
Bernie
MS Excel MVP

 

[J-LL]

Bernie, I figured out what I was doing wrong... two things wrong actually:
As I was preparing to shoot you a copy of the workbook, I suddenly started
getting realistic numbers. As I looked at the formulas more closely, I
noticed these two issues that made a big difference in the formula result:
- the (date of interest) won't work properly if I enter 1/1/2008 directly
into the formula. But when I reference to a cell that has the same, then it
works.
- the range of cells has to match the rows in the table. I only had 367
rows or entries but I set up my range in the formula to B1:B1000, which also
resulted in incorrect values. Seems like the formula was counting cells in
blank rows.
Thanks again for your help.

PS: For future reference, how does one 'shoot' a copy of a workbook within a
discussion group?

Jean-Luc

 

[Bernie Deitrick]

It depends. If you are using a newsreader, you would hit Reply instead of Reply to Group. If you
are using avweb interface, you would need to find the properties somewhere of my user. In either
case, you would need to make the obvious changes to the email address - take out the space, change
dot to ., perhaps change at to @ etc