Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bob bob is offline
external usenet poster
 
Posts: 2
Default Find last date of sold stock and sold price.

This problem is tricky as there can be more than one stock sold on
same day, also there can be more than one company sold on that day.
A B C
Co. Sym Date sold Price
---------------------------------------
F 01/06/12 11.77
F 01/06/12 11.56
JBLU 01/06/12 5.44

I have used the following Formulas with only partial success.
INDEX(A4:C6,MATCH(A4,A4:A6,MATCH(B4,B4:B6,0)),3)

And Also
SUM(IF(A4:A6=A4,IF(B4:B6=B4,C4:C6)))

Each works partially. For example, the second Sum(If finds the correct
price for JBLU, but adds the two prices for F. I want only the last
sold price. If the dates were different, the solution would be easy.
However, many times more than one stock is sold on the same date and
at times, multiple lots of the same company symbol.

Also, I use another Column to find the Largest or Max date which is
then substituted for the dates in column B. I show my example for a
solution because an expert probably has a quick fix without using an
extra column for solving my problem.

Thank you in advance for any help someone can offer.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,522
Default Find last date of sold stock and sold price.

try
=INDEX(D:D,MATCH(B4,B:B))

On Feb 18, 8:11*pm, bob wrote:
This problem is tricky as there can be more than one stock sold on
same day, also there can be more than one company sold on that day.
A * * * * * * *B * * * * * * *C
Co. Sym Date sold * * * Price
---------------------------------------
F * * * * * * * 01/06/12 * * * *11.77
F * * * * * * * 01/06/12 * * * *11.56
JBLU * *01/06/12 * * * * *5.44

I have used the following Formulas with only partial success.
INDEX(A4:C6,MATCH(A4,A4:A6,MATCH(B4,B4:B6,0)),3)

And Also
SUM(IF(A4:A6=A4,IF(B4:B6=B4,C4:C6)))

Each works partially. For example, the second Sum(If finds the correct
price for JBLU, but adds the two prices for F. I want only the last
sold price. If the dates were different, the solution would be easy.
However, many times more than one stock is sold on the same date and
at times, multiple lots of the same company symbol.

Also, I use another Column to find the Largest or Max date which is
then substituted for the dates in column B. I show my example for a
solution because an expert probably has a quick fix without using an
extra column for solving my problem.

Thank you in advance for any help someone can offer.


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,522
Default Find last date of sold stock and sold price.

On Feb 18, 8:11*pm, bob wrote:
This problem is tricky as there can be more than one stock sold on
same day, also there can be more than one company sold on that day.
A * * * * * * *B * * * * * * *C
Co. Sym Date sold * * * Price
---------------------------------------
F * * * * * * * 01/06/12 * * * *11.77
F * * * * * * * 01/06/12 * * * *11.56
JBLU * *01/06/12 * * * * *5.44

I have used the following Formulas with only partial success.
INDEX(A4:C6,MATCH(A4,A4:A6,MATCH(B4,B4:B6,0)),3)

And Also
SUM(IF(A4:A6=A4,IF(B4:B6=B4,C4:C6)))

Each works partially. For example, the second Sum(If finds the correct
price for JBLU, but adds the two prices for F. I want only the last
sold price. If the dates were different, the solution would be easy.
However, many times more than one stock is sold on the same date and
at times, multiple lots of the same company symbol.

Also, I use another Column to find the Largest or Max date which is
then substituted for the dates in column B. I show my example for a
solution because an expert probably has a quick fix without using an
extra column for solving my problem.

Thank you in advance for any help someone can offer.


=INDEX(D:D,MATCH(B4,B:B))
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculating the Median Price Sold AMB Excel Worksheet Functions 3 August 22nd 08 09:37 PM
inventory Add, Cost, Sold, Sold price - formula Summer Excel Discussion (Misc queries) 3 July 20th 08 06:26 PM
sum units sold every ten days Neophyte New Users to Excel 1 June 3rd 08 11:18 AM
Reducing Quantity When an Item is Sold Tami Excel Discussion (Misc queries) 4 March 21st 07 04:15 PM
Numeric Rank By Qty Sold JeremyH1982 Excel Discussion (Misc queries) 3 February 22nd 07 08:45 PM


All times are GMT +1. The time now is 02:47 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"