Searching up a column
Maybe someone can come up with something more concise, but this will
subtract the next to last number in A1:A50 from the last number in that
range:
=LOOKUP(99^99,A1:A50)-LOOKUP(99^99,INDIRECT("A1:A"&MATCH(LOOKUP(99^99,A1 :A50
),A1:A50,0)-1))
It doesn't matter if there's text, blanks, nulls, logicals or errors within
the range.
--
HTH,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Mike Rogers" <Mike060349@NoxSpamxAOLDOTcom wrote in message
...
I have a receipt sheet set up where I log every receipt from every store I
buy anything from. I have a €śComments€ť column to make note of what item
was
used for. In this €śComments€ť column, I also log my mileage after I buy
gas.
I can then use the information to determine my gas mileage after each
purchase. I need a formula in Column H to look back up Column G to the
last
number to subtract it from my current mileage. Without a formula here I
have
to look back up the column, find the last number, and enter the formula:
=€ťMiles last time I bought gas€ť-€ťTodays mileage€ť. The number of rows
between each mileage entry can very depending on how many other receipts I
have accumulated. I know that =LOOKUP(9.99999999999999E+307,G:G) will
look
down a column and find the last number, can I use something similar to
look
back up the column?
Mike Rogers
|