Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pull latest date data - ignore the rest
Is there a function in excel that I can use to pull the latest dated price by
item in data arrayed as follows: Thanks. Items Date Price 1 01/01/05 1.50 1 01/15/05 2.00 1 01/01/06 2.75 2 02/01/05 10.00 2 02/15/05 10.75 2 02/01/06 11.00 -- thanks, mc |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pull latest date data - ignore the rest
Try something like this:
With your table of info (incl col headings) in Cells A1:C7 D1: (an item number) E1: =SUMPRODUCT(($B$2:$B$7=MAX(($A$2:$A$7=D1)*$B$2:$B$ 7))*($A$2:$A$7=D1)*($C$2:$C$7)) Adjust references to suit your situation Does that help? *********** Regards, Ron XL2002, WinXP-Pro "dallin" wrote: Is there a function in excel that I can use to pull the latest dated price by item in data arrayed as follows: Thanks. Items Date Price 1 01/01/05 1.50 1 01/15/05 2.00 1 01/01/06 2.75 2 02/01/05 10.00 2 02/15/05 10.75 2 02/01/06 11.00 -- thanks, mc |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pull latest date data - ignore the rest
Yes it worked fine. Thanks.
-- thanks, mc "Ron Coderre" wrote: Try something like this: With your table of info (incl col headings) in Cells A1:C7 D1: (an item number) E1: =SUMPRODUCT(($B$2:$B$7=MAX(($A$2:$A$7=D1)*$B$2:$B$ 7))*($A$2:$A$7=D1)*($C$2:$C$7)) Adjust references to suit your situation Does that help? *********** Regards, Ron XL2002, WinXP-Pro "dallin" wrote: Is there a function in excel that I can use to pull the latest dated price by item in data arrayed as follows: Thanks. Items Date Price 1 01/01/05 1.50 1 01/15/05 2.00 1 01/01/06 2.75 2 02/01/05 10.00 2 02/15/05 10.75 2 02/01/06 11.00 -- thanks, mc |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pull latest date data - ignore the rest
What does the * do in the formula??
-- thanks, mc "Ron Coderre" wrote: Try something like this: With your table of info (incl col headings) in Cells A1:C7 D1: (an item number) E1: =SUMPRODUCT(($B$2:$B$7=MAX(($A$2:$A$7=D1)*$B$2:$B$ 7))*($A$2:$A$7=D1)*($C$2:$C$7)) Adjust references to suit your situation Does that help? *********** Regards, Ron XL2002, WinXP-Pro "dallin" wrote: Is there a function in excel that I can use to pull the latest dated price by item in data arrayed as follows: Thanks. Items Date Price 1 01/01/05 1.50 1 01/15/05 2.00 1 01/01/06 2.75 2 02/01/05 10.00 2 02/15/05 10.75 2 02/01/06 11.00 -- thanks, mc |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pull latest date data - ignore the rest
The formula contains a sequence of boolean expressions (phrases that equate
to True or False). When multiplied, Excel converts those values to 1 (for True) or 0 (for False). The asterisk (*) is Excel's multiply operator. Consequently, if: the Date equals the MaxDate for that item (True) X the item matches the item you're looking for (True) X the price for that item THEN True X True X Price = ? 1 X 1 X Price = Price BUT If even one of the conditions is False THEN 1 X 0 X Price = zero or 0 X 1 X Price = zero or 0 X 0 X Price = zero The SUMPRODUCT function adds the results of the above for each record in the table, all of which equal zero except for the one record that matches. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "dallin" wrote: What does the * do in the formula?? -- thanks, mc "Ron Coderre" wrote: Try something like this: With your table of info (incl col headings) in Cells A1:C7 D1: (an item number) E1: =SUMPRODUCT(($B$2:$B$7=MAX(($A$2:$A$7=D1)*$B$2:$B$ 7))*($A$2:$A$7=D1)*($C$2:$C$7)) Adjust references to suit your situation Does that help? *********** Regards, Ron XL2002, WinXP-Pro "dallin" wrote: Is there a function in excel that I can use to pull the latest dated price by item in data arrayed as follows: Thanks. Items Date Price 1 01/01/05 1.50 1 01/15/05 2.00 1 01/01/06 2.75 2 02/01/05 10.00 2 02/15/05 10.75 2 02/01/06 11.00 -- thanks, mc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pasting data from a website changes text to date | Excel Discussion (Misc queries) | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
can custom autofilter pull parameters from other data? | Excel Discussion (Misc queries) | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Printing data validation scenarios | Excel Worksheet Functions |