Database Date Analyis Problem

[George Andrews]

Excel 2000

Hello.

I am doing some analysis on results from advertising campaigns for several
different products and I want to compare response patterns from the first
day that I receive an order from a particular advert.

I have a database with various columns including Advert Reference, Date,
Order Quantity.

What I would like is a way to create a table showing the following columns:

Advert Reference, Date, Order Quantity, Days since First Order.

Pills1, 10/12/04, 5, 0
Pills1, 10/13/04, 3, 1
Pills1, 10/13/04, 0, 2
Pills1, 10/13/04, 4, 3
Pills1, 10/13/04, 17, 4

This example assumes that the advert first appeared on the 10/12/04 and that
there is a possibility that there were no orders on a particular day - in
this case on day 2 after the advert first appeared.

Therefore I need a formula that will look at the date of the order and
subtract this from the date of the first order for each Advert Reference.
Then I need to total up the total orders on that particular day.

I hope this makes sense.

Any suggestions would be greatly appreciated.

Regards

George

 

[Frank Kabel]

Hi
you may post also an example how ypur source data looks like (or do you
have alreadyave this list without the 4th column formula?)

 

[George Andrews]

Yes, I do have all the data except for the 4th Column, "Days since first
order"
Thanks

George

 

[Frank Kabel]

Hi
then enter the following formula in D1 as array formula (entered with
CTRL+SHIFT+ENTER):
=B1-MIN(IF($A$1:$A$100=A1,$B$1:$B$100))
and copy this down for all rows

 

[George Andrews]

Thanks. That seems to be working and is enough to get me going with this
project.

Regards

George