Prev: How do I remove data in a row in excel without losing all data in
Next: Insert Lines
From: Curtis on 29 Apr 2010 14:45
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




--
col
From: Luke M on 29 Apr 2010 15:34
You can do that with this array* formula:


--
Best Regards,

Luke M
"Curtis" <curtis.eadie(a)yahoo.ca.(do not spam)> wrote in message
news:61160B81-7277-4C97-8494-3D048881CE23(a)microsoft.com...
>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
>
>
>
>
> --
> col


From: Luke M on 29 Apr 2010 15:50
doh...

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

=INDEX(D: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.

--
Best Regards,

Luke M
"Luke M" <lukemoraga(a)nospam.com> wrote in message
news:ObJa6L95KHA.420(a)TK2MSFTNGP02.phx.gbl...
> You can do that with this array* formula:
>
>
> --
> Best Regards,
>
> Luke M
> "Curtis" <curtis.eadie(a)yahoo.ca.(do not spam)> wrote in message
> news:61160B81-7277-4C97-8494-3D048881CE23(a)microsoft.com...
>>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
>>
>>
>>
>>
>> --
>> col
>
>


From: JBoulton on 29 Apr 2010 16:25
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:D))


"Curtis" wrote:

> 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
>
>
>
>
> --
> col
From: Curtis on 29 Apr 2010 18:58
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
--
ce


"Luke M" wrote:

> doh...
>
> And that array formula would be this (forgot to include it!)
>
> =INDEX(D: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.
>
> --
> Best Regards,
>
> Luke M
> "Luke M" <lukemoraga(a)nospam.com> wrote in message
> news:ObJa6L95KHA.420(a)TK2MSFTNGP02.phx.gbl...
> > You can do that with this array* formula:
> >
> >
> > --
> > Best Regards,
> >
> > Luke M
> > "Curtis" <curtis.eadie(a)yahoo.ca.(do not spam)> wrote in message
> > news:61160B81-7277-4C97-8494-3D048881CE23(a)microsoft.com...
> >>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
> >>
> >>
> >>
> >>
> >> --
> >> col
> >
> >
>
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: How do I remove data in a row in excel without losing all data in
Next: Insert Lines