how to find last value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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
 
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
 
Back
Top