Formula to calculate Latest Cost (inventory valuation)

[Curtis]

I have a worksheet that contains the daily purchases for 4 products.

column B contains the products
column D contains the purchase price
column K contains the division
column J contains the day of the month

I need a formula that will pull the latest purchase price by product by
division. In other words the last purchase for the month and the associated
purchase price.

Thanks

 

[Luke M]

You can do that with this array* formula:

 

[Luke M]

doh...

And that array formula would be this (forgot to include it!)

=INDEX(D,MAX(IF(LARGE((B2:B10=Z1)*(K2:K10=Z2)*(J2:J10),1)=(B2:B10=Z1)*(K2:K10=Z2)*(J2:J10),ROW(K2:K10))))

This assumes that Z1 and Z2 are the product and division, repsectively, that
you want to look up. In the event that the same product from the same
division was has two different prices for the same day, the later record
(larger row) is returned. Remember that array formulas must be confirmed
using Ctrl+Shift+Enter, not just Enter.

 

[JBoulton]

O6 contains: =MAX(J:J) and gives you the most recent day
P6 contains your part number
Q6 contains your division
R6 contains: =SUMPRODUCT(--(J:J=O6),--(K:K=P6),--(B:B=Q6),(D))

 

[Curtis]

This formula is not returning the correct value but rather it is returning a
value of $80.05 for E90 for this division when as you can see below the price
should read $82.15 (see excerpt table from source worksheet below)

COLUMN B COL D COL J COL K

Product Unit Cost Receipt Date Division
E10 76.73 3/26/2010 North - row 620
E90 82.15 3/26/2010 North - row 621
E10 76.73 3/27/2010 North - row 670
E90 82.15 3/27/2010 North - row 671
E10 76.73 3/30/2010 North - row 697

I need the formula to return the purchase price of the last purchase

Thanks

 

[Luke M]

Where is the $80.05 located in your table?

With only the sample data you gave, the formula is working on my end. Note
that all of the ranges within the IF function need to be of equal length,
and cannot call out the entire column. (possible source of error?)

 

[Curtis]

Still not getting the latest cost price keep in mind I have removed the
divisions from the calculation

In the source sheet
COL AB = recent cost
COL W = latest purchase date (format mm/dd/yyyy)
COL A = Part number

Thanks

ce