Prev: How do I remove data in a row in excel without losing all data in
Next: Insert Lines
From: Luke M on 30 Apr 2010 09:13
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?)

--
Best Regards,

Luke M
"Curtis" <curtis.eadie(a)yahoo.ca.(do not spam)> wrote in message
news:3358F218-7A12-470E-AA30-C1980B1DC1F0(a)microsoft.com...
> 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
>> >
>> >
>>
>>
>> .
>>


From: Curtis on 4 May 2010 12:28
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



--
ce


"Luke M" wrote:

> 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?)
>
> --
> Best Regards,
>
> Luke M
> "Curtis" <curtis.eadie(a)yahoo.ca.(do not spam)> wrote in message
> news:3358F218-7A12-470E-AA30-C1980B1DC1F0(a)microsoft.com...
> > 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
> >> >
> >> >
> >>
> >>
> >> .
> >>
>
>
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: How do I remove data in a row in excel without losing all data in
Next: Insert Lines