Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching One Column Only | Excel Discussion (Misc queries) | |||
Searching a column and returning a yes or no answer | Excel Worksheet Functions | |||
Searching for partial data in a column | Excel Discussion (Misc queries) | |||
Column searching problem | New Users to Excel | |||
Searching a column and copying rows | Excel Worksheet Functions |