Last Sales Price

[rajivsoni]

Hey guys,
I'm stuck with a report i need to make and need an urgent response
i have a database file containing the following:

Date | Order Number | Product Id | Product Description | Customer ID | Customer Name | Volumes sold | Selling Price | Total Value (Volume x sp)

now what i want is a formula to get the Last selling Price of a Product.

For Eg.
Lets say the Customer is "Rajiv" and the Product Purchased is a "Pendant"

I need to know @ what price was the pendant last sold to the customer rajiv.
Now the tricky part is tht this customer could have purchased the pendants before number of times. i need to know @ what price was it last purchased.
I have a date column in my database which im sure would be helpful.

Lemme know if i wasnt clear.

Thanks in Advance

 

[rajivsoni]

??????????????????????????????

 

[rajivsoni]

help me.....!!

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=LOOKUP(9.99999999999999E+307,IF(CustomerRange="Rajiv",
    IF(ProductRange="Pendant",1)),PriceRange)

 

[rajivsoni]

hey thanks Aladin, this works but i have a question here, what this does is it pickes up the last row for that customer. can there be a way to pickup the Price range based on the order date ??? it should pick up the Latest Order date
Thank you soo much ..!

 

[Ron Coderre]

Perhaps something like this.....
With your data structured like your first post and positioned in Col_A through CoL_I
and
K1: a customer....eg Rajiv
L1: a product......eg Pendant
M1: a date..........eg 2011-Jan-07

This regular formula returns the last price for that customer for that item on that date (I employed Aladin's range name convention):

N1: =LOOKUP(10^10,1/((CustomerRange=K1)*(ProductRange=L1)
*(DateRange=M1)),PriceRange)

Note each of those ranges begins in the first row under the headings (probably Row_2)

Is that something you can work with?

 

[rajivsoni]

Ron,
DateRange=M1
I'm not specifying any date in M1.
The formula needs to pick the Latest date from the DateRange

 

[Ron Coderre]

Ron,
DateRange=M1
I'm not specifying any date in M1.
The formula needs to pick the Latest date from the DateRange

If you want regular formulas to return
• the last price for the product for the customer

=LOOKUP(10^10,1/((CustomerRange=K1)*(ProductRange=L1)),PriceRange)

• the date for that price

=LOOKUP(10^10,1/((CustomerRange=K1)*(ProductRange=L1)),DateRange)

Does that help?...or do you need something else?

 

[rajivsoni]

hey Ron,
Tht dint really help but thanks anyway i tried cracking my brains to come up with this one:
=LOOKUP(10^10,1/((CustomerRange=K1)*(ProductRange=L1)
*(DateRange=MAX(DateRange))),PriceRange)
Thanks a ton

 

[Ron Coderre]

OK...I understand. You wanted to only search for the customer and product on the last entry date. I'm glad you figured it out. Thanks for letting us know your solution.