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

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

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

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

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