how to find last value

[adam]

every day we record the product name, quantity and the date, how to make
lookup or vlookup (or any other function) find the first date and the last
date for specific product to calculate for how many days this product produced
example; we have products a,b and c we record the quantity produced every
day for 30 days (also record the date) the columns ( product / qty / date)
if the product b records only in days 5,6,8,15 and 20 of the month i need
formula to know the the first date is 5 and the last date is 20 the result
should be 15 days
thanks

[Biff]

Hi!

Try this:

Column A = product (A, B, C)
Column C = dates produced

This is an array formula. It MUST be entered using the key combination of
CTRL,SHIFT,ENTER:

This is for product "A":

=MAX(IF(A1:A20="A",C1:C20))-MIN(IF(A1:A20="A",C1:C20))

Adjust ranges to suit.

Biff

"adam" <[email protected]> wrote in message
news:[email protected]...
> every day we record the product name, quantity and the date, how to make
> lookup or vlookup (or any other function) find the first date and the last
> date for specific product to calculate for how many days this product
> produced
> example; we have products a,b and c we record the quantity produced every
> day for 30 days (also record the date) the columns ( product / qty / date)
> if the product b records only in days 5,6,8,15 and 20 of the month i need
> formula to know the the first date is 5 and the last date is 20 the result
> should be 15 days
> thanks

[adam]

Hi
the formula working in the formula screen but the cell always 0

"Biff" wrote:

> Hi!
>
> Try this:
>
> Column A = product (A, B, C)
> Column C = dates produced
>
> This is an array formula. It MUST be entered using the key combination of
> CTRL,SHIFT,ENTER:
>
> This is for product "A":
>
> =MAX(IF(A1:A20="A",C1:C20))-MIN(IF(A1:A20="A",C1:C20))
>
> Adjust ranges to suit.
>
> Biff
>
> "adam" <[email protected]> wrote in message
> news:[email protected]...
> > every day we record the product name, quantity and the date, how to make
> > lookup or vlookup (or any other function) find the first date and the last
> > date for specific product to calculate for how many days this product
> > produced
> > example; we have products a,b and c we record the quantity produced every
> > day for 30 days (also record the date) the columns ( product / qty / date)
> > if the product b records only in days 5,6,8,15 and 20 of the month i need
> > formula to know the the first date is 5 and the last date is 20 the result
> > should be 15 days
> > thanks
>
>
>

[[email protected]]

Hello,

I suggest

=LOOKUP(2,1/("A"=$A$1:$A$25),$C$1:$C$25)-INDEX($C$1:$C$25,MATCH("A",$A$1:$A$25,))

(no array formula)

Regards,
Bernd

[Biff]

The formula is an array formula. It MUST be entered using the key
combination of CTRL,SHIFT,ENTER. Type the formula, then, instead of just
hitting ENTER like you normally would, hold down both the CTRL key and the
SHIFT key then hit ENTER. If done properly Excel will enclose the formula in
squiggly braces { }. You cannot just type these braces in, you MUST use the
key combo.

Biff

"adam" <[email protected]> wrote in message
news:[email protected]...
> Hi
> the formula working in the formula screen but the cell always 0
>
> "Biff" wrote:
>
>> Hi!
>>
>> Try this:
>>
>> Column A = product (A, B, C)
>> Column C = dates produced
>>
>> This is an array formula. It MUST be entered using the key combination of
>> CTRL,SHIFT,ENTER:
>>
>> This is for product "A":
>>
>> =MAX(IF(A1:A20="A",C1:C20))-MIN(IF(A1:A20="A",C1:C20))
>>
>> Adjust ranges to suit.
>>
>> Biff
>>
>> "adam" <[email protected]> wrote in message
>> news:[email protected]...
>> > every day we record the product name, quantity and the date, how to
>> > make
>> > lookup or vlookup (or any other function) find the first date and the
>> > last
>> > date for specific product to calculate for how many days this product
>> > produced
>> > example; we have products a,b and c we record the quantity produced
>> > every
>> > day for 30 days (also record the date) the columns ( product / qty /
>> > date)
>> > if the product b records only in days 5,6,8,15 and 20 of the month i
>> > need
>> > formula to know the the first date is 5 and the last date is 20 the
>> > result
>> > should be 15 days
>> > thanks
>>
>>
>>

[adam]

Hi
what is 2,1/ meaning in this formula

"[email protected]" wrote:

> Hello,
>
> I suggest
>
> =LOOKUP(2,1/("A"=$A$1:$A$25),$C$1:$C$25)-INDEX($C$1:$C$25,MATCH("A",$A$1:$A$25,))
>
> (no array formula)
>
> Regards,
> Bernd
>
>

[[email protected]]

Hi Adam,

LOOKUP ignores error values and returns the last non-error value if the
search value was not found.

1/(comparison) gives 1,1,1,#DIV0!,1,1,#DIV0!,1,#DIV0!,1... and if you
search any value > 1 in here (i.e. 2) then you will get the position of
the last 1 - exactly what we need. Go into the formula editor and
evaluate this partially by selecting (comparison) and pushing F9, then
1/(comparison) and F9 again, etc.

[Not my invention, just my favourite question if somebody claims to be
an Excel expert :-) ]

Regards,
Bernd