Last Sales Price

rajivsoni

Board Regular
Joined
May 5, 2006
Messages
133
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 :)
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Control+shift+enter, not just enter:
Rich (BB code):
=LOOKUP(9.99999999999999E+307,IF(CustomerRange="Rajiv",
    IF(ProductRange="Pendant",1)),PriceRange)
 

rajivsoni

Board Regular
Joined
May 5, 2006
Messages
133

ADVERTISEMENT

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

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
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):
Code:
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

Board Regular
Joined
May 5, 2006
Messages
133

ADVERTISEMENT

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

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
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
Code:
=LOOKUP(10^10,1/((CustomerRange=K1)*(ProductRange=L1)),PriceRange)
• the date for that price
Code:
=LOOKUP(10^10,1/((CustomerRange=K1)*(ProductRange=L1)),DateRange)

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

rajivsoni

Board Regular
Joined
May 5, 2006
Messages
133
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

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
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.
 

Forum statistics

Threads
1,141,019
Messages
5,703,776
Members
421,315
Latest member
awaisnazir139

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top