Searching up a column
 

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

Searching up a column
 

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


"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

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

Searching up a column
 

It seems you might have understood the OP better then I!<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"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

Searching up a column
 

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


"Ragdyer" wrote in message
...
It seems you might have understood the OP better then I!<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"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

Searching up a column
 

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

"Rick Rothstein (MVP - VB)" wrote:

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


"Ragdyer" wrote in message
...
It seems you might have understood the OP better then I!<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"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

Searching up a column
 

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" wrote:

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

Searching up a column
 

Thanks for the feed-back.
--

Regards,

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

"Mike Rogers" <Mike060349@NoxSpamxAOLDOTcom wrote in message
...
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" wrote:

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"-"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