To keep track of frequent price changes with several products I have
LIST1 with 3 fields:
'ProductID’, 'DateOfChange’, 'NewPrice'.
Furthermore, LIST2 stores all orders with also 3 fields: 'ProductID',
'OrderDate’, 'QuantitySold’.
Here I need a 4th field, ‘TurnOver', calculated as:[LIST1!'NewPrice']
*[LIST2!'QuantitySold'].
But which formula can find the applicable ‘NewPrice’ valid at
‘OrderDate’ ?

Any assistance is highly appreciated, thank you in advance.

Kind regards,

H.G. Lamy

Frank wrote:

To keep track of frequent price changes with several products I have
LIST1 with 3 fields:
'ProductID’, 'DateOfChange’, 'NewPrice'.
Furthermore, LIST2 stores all orders with also 3 fields: 'ProductID',
'OrderDate’, 'QuantitySold’.
Here I need a 4th field, ‘TurnOver', calculated as:[LIST1!'NewPrice']
*[LIST2!'QuantitySold'].
But which formula can find the applicable ‘NewPrice’ valid at
‘OrderDate’ ?

Any assistance is highly appreciated, thank you in advance.

No need to post your question twice.

I'd perhaps look into the LOOKUP function. It *might* serve your needs.

--
Train yourself to let go of everything you fear to lose.

On 22 Mrz., 22:19, "Auric__" wrote:
Frank wrote:
To keep track of frequent price changes with several products I have
LIST1 with 3 fields:
'ProductID’, 'DateOfChange’, 'NewPrice'.
Furthermore, LIST2 stores all orders with also 3 fields: 'ProductID',
'OrderDate’, 'QuantitySold’.
Here I need a 4th field, ‘TurnOver', calculated as:[LIST1!'NewPrice']
*[LIST2!'QuantitySold'].
But which formula can find the applicable ‘NewPrice’ valid at
‘OrderDate’ ?

Any assistance is highly appreciated, thank you in advance.

No need to post your question twice.

I'd perhaps look into the LOOKUP function. It *might* serve your needs.

--
Train yourself to let go of everything you fear to lose.

A simple LOOKUP will not identify a value between 2 dates,
unfortunately.

hgl

Hi Frank,

Am Fri, 23 Mar 2012 02:26:53 -0700 (PDT) schrieb Frank:

A simple LOOKUP will not identify a value between 2 dates,
unfortunately.

your searched ProductID in F1 and the OrderDate in G1. Then you find the
price with:
=INDEX(List1!C:C;VERGLEICH(1;(List1!A1:A100=F1)*(L ist1!B1:B100<=G1);0))
The formula is an array-formula to enter with CTRL+Shift+Enter.

Warum schreibst du nicht in den deutschen Newsgroups?
microsoft.public.de.excel
de.comp.office-pakete.ms-office.excel


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2