Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
currently, my boss asked me to look for latest price in a large volumn of
data in excel. the data is like this... Product transaction date selling price A1 5/6/2005 $10 A2 6/6/2005 $11 A1 7/6/2005 $12 A1 8/6/2005 $10.5 Is there any formulas allow to get the A1 latest selling price? thanks a lot.. |
#2
![]() |
|||
|
|||
![]()
Hi!
Try this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(C2:C5,MATCH(1,(A2:A5="A1")*(B2:B5=MAX(IF(A2 :A5="A1",B2:B5))),0)) Biff "Lawrence" wrote in message ... currently, my boss asked me to look for latest price in a large volumn of data in excel. the data is like this... Product transaction date selling price A1 5/6/2005 $10 A2 6/6/2005 $11 A1 7/6/2005 $12 A1 8/6/2005 $10.5 Is there any formulas allow to get the A1 latest selling price? thanks a lot.. |
#3
![]() |
|||
|
|||
![]()
OR -
Also entered as an array: =VLOOKUP(MAX(IF(A2:A5="A1",B2:B5)),B2:C5,2,0) Biff "Biff" wrote in message ... Hi! Try this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(C2:C5,MATCH(1,(A2:A5="A1")*(B2:B5=MAX(IF(A2 :A5="A1",B2:B5))),0)) Biff "Lawrence" wrote in message ... currently, my boss asked me to look for latest price in a large volumn of data in excel. the data is like this... Product transaction date selling price A1 5/6/2005 $10 A2 6/6/2005 $11 A1 7/6/2005 $12 A1 8/6/2005 $10.5 Is there any formulas allow to get the A1 latest selling price? thanks a lot.. |
#4
![]() |
|||
|
|||
![]()
With E2 housing a product of interest:
1. If the transaction dates per product is an ascending series... =LOOKUP(2,1/($A$2:$A$5=E2),$C$2:$C$5) 2. =INDEX($C$2:$C$5,MATCH(MAX(IF($A$2:$A$5=E2,$B$2:$B $5)),$B$2:$B$5,0)) which needs to be confirmed with control+shift+enter. Lawrence wrote: currently, my boss asked me to look for latest price in a large volumn of data in excel. the data is like this... Product transaction date selling price A1 5/6/2005 $10 A2 6/6/2005 $11 A1 7/6/2005 $12 A1 8/6/2005 $10.5 Is there any formulas allow to get the A1 latest selling price? thanks a lot.. |
#5
![]() |
|||
|
|||
![]()
Biff,
it doesn't work.. it return #VALUE!..... Lawrence "Biff" wrote: Hi! Try this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(C2:C5,MATCH(1,(A2:A5="A1")*(B2:B5=MAX(IF(A2 :A5="A1",B2:B5))),0)) Biff "Lawrence" wrote in message ... currently, my boss asked me to look for latest price in a large volumn of data in excel. the data is like this... Product transaction date selling price A1 5/6/2005 $10 A2 6/6/2005 $11 A1 7/6/2005 $12 A1 8/6/2005 $10.5 Is there any formulas allow to get the A1 latest selling price? thanks a lot.. |
#6
![]() |
|||
|
|||
![]()
Bilf,
Both also work, i forgotten to press key in combo. can i know what does it function by "key combo of CTRL,SHIFT,ENTER" beside that, if the product go by thousand of items.. is it i have to press the combo everytime ? "Biff" wrote: OR - Also entered as an array: =VLOOKUP(MAX(IF(A2:A5="A1",B2:B5)),B2:C5,2,0) Biff "Biff" wrote in message ... Hi! Try this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(C2:C5,MATCH(1,(A2:A5="A1")*(B2:B5=MAX(IF(A2 :A5="A1",B2:B5))),0)) Biff "Lawrence" wrote in message ... currently, my boss asked me to look for latest price in a large volumn of data in excel. the data is like this... Product transaction date selling price A1 5/6/2005 $10 A2 6/6/2005 $11 A1 7/6/2005 $12 A1 8/6/2005 $10.5 Is there any formulas allow to get the A1 latest selling price? thanks a lot.. |
#7
![]() |
|||
|
|||
![]()
Did you enter the formula as an array?
Type the formula, then instead of hitting the enter key hold down the CTRL and SHIFT keys then hit ENTER. When done properly Excel will place squiggly braces { } around the formula. You cannot just type those braces in, you MUST use the key combination. Biff "Lawrence" wrote in message ... Biff, it doesn't work.. it return #VALUE!..... Lawrence "Biff" wrote: Hi! Try this: Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(C2:C5,MATCH(1,(A2:A5="A1")*(B2:B5=MAX(IF(A2 :A5="A1",B2:B5))),0)) Biff "Lawrence" wrote in message ... currently, my boss asked me to look for latest price in a large volumn of data in excel. the data is like this... Product transaction date selling price A1 5/6/2005 $10 A2 6/6/2005 $11 A1 7/6/2005 $12 A1 8/6/2005 $10.5 Is there any formulas allow to get the A1 latest selling price? thanks a lot.. |
#8
![]() |
|||
|
|||
![]()
Hi Lawrence
Yet another variation for a solution. The non array formula =SUMPRODUCT(--(A2:A5="A1"),--(B2:B5=MAX(B2:B5)),C2:C5) Regards Roger Govier Lawrence wrote: currently, my boss asked me to look for latest price in a large volumn of data in excel. the data is like this... Product transaction date selling price A1 5/6/2005 $10 A2 6/6/2005 $11 A1 7/6/2005 $12 A1 8/6/2005 $10.5 Is there any formulas allow to get the A1 latest selling price? thanks a lot.. |
#9
![]() |
|||
|
|||
![]()
Hi Lawrence
Forget that. It's nonsense. It works for your sample set of data, but won't work of course if the latest date isn't on a line with A1. Regards Roger Govier Roger Govier wrote: Hi Lawrence Yet another variation for a solution. The non array formula =SUMPRODUCT(--(A2:A5="A1"),--(B2:B5=MAX(B2:B5)),C2:C5) Regards Roger Govier Lawrence wrote: currently, my boss asked me to look for latest price in a large volumn of data in excel. the data is like this... Product transaction date selling price A1 5/6/2005 $10 A2 6/6/2005 $11 A1 7/6/2005 $12 A1 8/6/2005 $10.5 Is there any formulas allow to get the A1 latest selling price? thanks a lot.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Formula for calculating price | Excel Discussion (Misc queries) | |||
MSNStockQuote Function Parameters | Excel Worksheet Functions | |||
Macro's for figuring Price Lists?!? | Excel Discussion (Misc queries) | |||
Product Price List with 14k records, filter w/out using AutoFilter | Excel Worksheet Functions | |||
Computing Sales Tax and Retail Price from a number | Excel Worksheet Functions |