Searching up a column

[Mike Rogers]

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â€-â€Today’s 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

 

[Rick Rothstein \(MVP - VB\)]

Assuming you have no other numerical only entries (this would include dates)
in Column G except for your mileage notations, and assuming you have headers
in Row 1, put this formula in H3 and copy down as far as you want...

=IF(ISNUMBER(G3),G3-MAX($G$2:$G2),"")

Rick

 

[Ragdyer]

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.

 

[Ragdyer]

It seems you might have understood the OP better then I!<bg>

 

[Rick Rothstein \(MVP - VB\)]

LOL... The real "eureka" moment for me came when I realized car mileages, by
their very nature, must always be increasing numbers... hence the MAX
function. Well, they will always be increasing except when they roll-over
back to zero; although I'm not sure odometers do that any more. I made sure
I was driving my wife's 1995 car when it reached 999999 so I could watch it
go back to zero (yeah, I know, get a life<g>). Anyway, when the "big day"
came, I got to observe it go from 999999 to 1000000... who knew there was a
1 in the front there?<g>

Rick

 

[Mike Rogers]

Rick

Worked like a charm!! Had to adjust the references a row or two
but...(seems like I am always at least a row off <bg>) Thanks a million.

Mike Rogers

 

[Mike Rogers]

Ragdyer

Thanks for the post. I tried Rick's first and it worked well so yours goes
in the hopper for future reference. Thanks for the help!!! It is always
appreciated. (I knew one of those long 9999999 formulas would work, just did
not know how.)

Mike Rogers

 

[RagDyeR]

Thanks for the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Ragdyer

Thanks for the post. I tried Rick's first and it worked well so yours goes
in the hopper for future reference. Thanks for the help!!! It is always
appreciated. (I knew one of those long 9999999 formulas would work, just
did
not know how.)

Mike Rogers