software

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

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-Proquot;dallinquot; wrote:

gt; Is there a function in excel that I can use to pull the latest dated price by
gt; item in data arrayed as follows: Thanks.
gt;
gt; Items Date Price
gt; 1 01/01/05 1.50
gt; 1 01/15/05 2.00
gt; 1 01/01/06 2.75
gt; 2 02/01/05 10.00
gt; 2 02/15/05 10.75
gt; 2 02/01/06 11.00
gt; --
gt; thanks, mc

Yes it worked fine. Thanks.
--
thanks, mcquot;Ron Coderrequot; wrote:

gt; Try something like this:
gt; With your table of info (incl col headings) in Cells A1:C7
gt;
gt; D1: (an item number)
gt; E1:
gt; =SUMPRODUCT(($B$2:$B$7=MAX(($A$2:$A$7=D1)*$B$2:$B$ 7))*($A$2:$A$7=D1)*($C$2:$C$7))
gt;
gt; Adjust references to suit your situation
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;dallinquot; wrote:
gt;
gt; gt; Is there a function in excel that I can use to pull the latest dated price by
gt; gt; item in data arrayed as follows: Thanks.
gt; gt;
gt; gt; Items Date Price
gt; gt; 1 01/01/05 1.50
gt; gt; 1 01/15/05 2.00
gt; gt; 1 01/01/06 2.75
gt; gt; 2 02/01/05 10.00
gt; gt; 2 02/15/05 10.75
gt; gt; 2 02/01/06 11.00
gt; gt; --
gt; gt; thanks, mc

What does the * do in the formula??
--
thanks, mcquot;Ron Coderrequot; wrote:

gt; Try something like this:
gt; With your table of info (incl col headings) in Cells A1:C7
gt;
gt; D1: (an item number)
gt; E1:
gt; =SUMPRODUCT(($B$2:$B$7=MAX(($A$2:$A$7=D1)*$B$2:$B$ 7))*($A$2:$A$7=D1)*($C$2:$C$7))
gt;
gt; Adjust references to suit your situation
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;dallinquot; wrote:
gt;
gt; gt; Is there a function in excel that I can use to pull the latest dated price by
gt; gt; item in data arrayed as follows: Thanks.
gt; gt;
gt; gt; Items Date Price
gt; gt; 1 01/01/05 1.50
gt; gt; 1 01/15/05 2.00
gt; gt; 1 01/01/06 2.75
gt; gt; 2 02/01/05 10.00
gt; gt; 2 02/15/05 10.75
gt; gt; 2 02/01/06 11.00
gt; gt; --
gt; gt; thanks, mc

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-Proquot;dallinquot; wrote:

gt; What does the * do in the formula??
gt; --
gt; thanks, mc
gt;
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Try something like this:
gt; gt; With your table of info (incl col headings) in Cells A1:C7
gt; gt;
gt; gt; D1: (an item number)
gt; gt; E1:
gt; gt; =SUMPRODUCT(($B$2:$B$7=MAX(($A$2:$A$7=D1)*$B$2:$B$ 7))*($A$2:$A$7=D1)*($C$2:$C$7))
gt; gt;
gt; gt; Adjust references to suit your situation
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;dallinquot; wrote:
gt; gt;
gt; gt; gt; Is there a function in excel that I can use to pull the latest dated price by
gt; gt; gt; item in data arrayed as follows: Thanks.
gt; gt; gt;
gt; gt; gt; Items Date Price
gt; gt; gt; 1 01/01/05 1.50
gt; gt; gt; 1 01/15/05 2.00
gt; gt; gt; 1 01/01/06 2.75
gt; gt; gt; 2 02/01/05 10.00
gt; gt; gt; 2 02/15/05 10.75
gt; gt; gt; 2 02/01/06 11.00
gt; gt; gt; --
gt; gt; gt; thanks, mc