Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
dallin
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
dallin
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
dallin
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre
 
Posts: n/a
Default 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
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
pasting data from a website changes text to date stebro Excel Discussion (Misc queries) 8 August 12th 06 08:39 PM
Excel Macro to Copy & Paste [email protected] Excel Worksheet Functions 0 December 1st 05 02:56 PM
can custom autofilter pull parameters from other data? DaveCPA Excel Discussion (Misc queries) 0 September 28th 05 04:28 PM
Help PLEASE! Not sure what answer is: Match? Index? Other? baz Excel Worksheet Functions 7 September 3rd 05 03:47 PM
Printing data validation scenarios SJC Excel Worksheet Functions 14 July 24th 05 12:43 AM


All times are GMT +1. The time now is 09:37 PM.

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"