![]() |
2nd to last value in column
Hi everyone.
My question has to do with the 2nd to last cell with data in a column. To get the last data, I am using "=LOOKUP(10^100,A2:D2)" but how can I get the data from the cell right above that? For instance, my data looks like this (multiple tabs for different entities): # of Accounts 1/1/10 45 $ of Accounts 1/1/10 6300 # of Accounts 1/2/10 23 $ of Accounts 1/2/10 1550 So, I want my totals page to show the last 2 entries (# and $). TIA, any help would be wonderful, Thanks, Greg |
2nd to last value in column
Try the below to get the data from the cell right above that?
With data in ColA =INDEX(A:A,MATCH(10^10,A:A)-1) -- Jacob "Greg" wrote: Hi everyone. My question has to do with the 2nd to last cell with data in a column. To get the last data, I am using "=LOOKUP(10^100,A2:D2)" but how can I get the data from the cell right above that? For instance, my data looks like this (multiple tabs for different entities): # of Accounts 1/1/10 45 $ of Accounts 1/1/10 6300 # of Accounts 1/2/10 23 $ of Accounts 1/2/10 1550 So, I want my totals page to show the last 2 entries (# and $). TIA, any help would be wonderful, Thanks, Greg |
2nd to last value in column
Worked like a charm.
Thank you very much, Greg "Jacob Skaria" wrote: Try the below to get the data from the cell right above that? With data in ColA =INDEX(A:A,MATCH(10^10,A:A)-1) -- Jacob "Greg" wrote: Hi everyone. My question has to do with the 2nd to last cell with data in a column. To get the last data, I am using "=LOOKUP(10^100,A2:D2)" but how can I get the data from the cell right above that? For instance, my data looks like this (multiple tabs for different entities): # of Accounts 1/1/10 45 $ of Accounts 1/1/10 6300 # of Accounts 1/2/10 23 $ of Accounts 1/2/10 1550 So, I want my totals page to show the last 2 entries (# and $). TIA, any help would be wonderful, Thanks, Greg |
2nd to last value in column
Greg,
You question is a bit muddled, you ask for the second to last value in a column yet your formula is a row (A2:D2), which is it. Assumin no blanks try these Row =INDEX(A2:D2,COUNT(A2:D2)-1) column =INDEX(A1:A6,COUNT(A1:A6)-1) -- Mike When competing hypotheses are equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. Occam''''s razor (Abbrev) "Greg" wrote: Hi everyone. My question has to do with the 2nd to last cell with data in a column. To get the last data, I am using "=LOOKUP(10^100,A2:D2)" but how can I get the data from the cell right above that? For instance, my data looks like this (multiple tabs for different entities): # of Accounts 1/1/10 45 $ of Accounts 1/1/10 6300 # of Accounts 1/2/10 23 $ of Accounts 1/2/10 1550 So, I want my totals page to show the last 2 entries (# and $). TIA, any help would be wonderful, Thanks, Greg |
2nd to last value in column
You are correct. I used a copied formula template (A2:D2) for my example.
The actual number I was looking for was in a column. My fault. Thank you for your input. Having both formulas is great. Greg "Mike H" wrote: Greg, You question is a bit muddled, you ask for the second to last value in a column yet your formula is a row (A2:D2), which is it. Assumin no blanks try these Row =INDEX(A2:D2,COUNT(A2:D2)-1) column =INDEX(A1:A6,COUNT(A1:A6)-1) -- Mike When competing hypotheses are equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. Occam''''s razor (Abbrev) "Greg" wrote: Hi everyone. My question has to do with the 2nd to last cell with data in a column. To get the last data, I am using "=LOOKUP(10^100,A2:D2)" but how can I get the data from the cell right above that? For instance, my data looks like this (multiple tabs for different entities): # of Accounts 1/1/10 45 $ of Accounts 1/1/10 6300 # of Accounts 1/2/10 23 $ of Accounts 1/2/10 1550 So, I want my totals page to show the last 2 entries (# and $). TIA, any help would be wonderful, Thanks, Greg |
2nd to last value in column
Hi Greg,
Here's another that seems to do what you want. =OFFSET(F1,COUNTA(F:F)-2,0) HTH Regards, Howard "Greg" wrote in message ... Hi everyone. My question has to do with the 2nd to last cell with data in a column. To get the last data, I am using "=LOOKUP(10^100,A2:D2)" but how can I get the data from the cell right above that? For instance, my data looks like this (multiple tabs for different entities): # of Accounts 1/1/10 45 $ of Accounts 1/1/10 6300 # of Accounts 1/2/10 23 $ of Accounts 1/2/10 1550 So, I want my totals page to show the last 2 entries (# and $). TIA, any help would be wonderful, Thanks, Greg |
Find last 20 values in column
Hi guys,
Along a similar line to finding the 2nd last value in a column, I have a question that's a little trickier.. I have a single column of numbers ~100 rows high.. Not all cells have values in them and each week another value is entered at the bottom of the column.. I need to find the 10 lowest values in the last 20 cells that have values in them.. So each week the top value of the 20 'found' values will drop off and the most recent value will become part of the list of 20 values that the 10 lowest values are calculated from.. Any ideas?? Thanks in advance, Doggy L. Howard Kittle wrote: Hi Greg,Here is another that seems to do what you want. 18-Jan-10 Hi Greg, Here is another that seems to do what you want. =OFFSET(F1,COUNTA(F:F)-2,0) HTH Regards, Howard Previous Posts In This Thread: On Monday, January 18, 2010 8:41 AM Greg wrote: 2nd to last value in column Hi everyone. My question has to do with the 2nd to last cell with data in a column. To get the last data, I am using "=LOOKUP(10^100,A2:D2)" but how can I get the data from the cell right above that? For instance, my data looks like this (multiple tabs for different entities): $ of Accounts 1/1/10 6300 $ of Accounts 1/2/10 1550 So, I want my totals page to show the last 2 entries (# and $). TIA, any help would be wonderful, Thanks, Greg On Monday, January 18, 2010 8:52 AM Jacob Skaria wrote: Try the below to get the data from the cell right above that? Try the below to get the data from the cell right above that? With data in ColA =INDEX(A:A,MATCH(10^10,A:A)-1) -- Jacob "Greg" wrote: On Monday, January 18, 2010 8:59 AM Greg wrote: Worked like a charm.Thank you very much,Greg"Jacob Skaria" wrote: Worked like a charm. Thank you very much, Greg "Jacob Skaria" wrote: On Monday, January 18, 2010 8:59 AM Mike H wrote: Greg,You question is a bit muddled, you ask for the second to last value in Greg, You question is a bit muddled, you ask for the second to last value in a column yet your formula is a row (A2:D2), which is it. Assumin no blanks try these Row =INDEX(A2:D2,COUNT(A2:D2)-1) column =INDEX(A1:A6,COUNT(A1:A6)-1) -- Mike When competing hypotheses are equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. Occam''''s razor (Abbrev) "Greg" wrote: On Monday, January 18, 2010 9:19 AM Greg wrote: You are correct. I used a copied formula template (A2:D2) for my example. You are correct. I used a copied formula template (A2:D2) for my example. The actual number I was looking for was in a column. My fault. Thank you for your input. Having both formulas is great. Greg "Mike H" wrote: On Monday, January 18, 2010 2:31 PM L. Howard Kittle wrote: Hi Greg,Here is another that seems to do what you want. Hi Greg, Here is another that seems to do what you want. =OFFSET(F1,COUNTA(F:F)-2,0) HTH Regards, Howard Submitted via EggHeadCafe - Software Developer Portal of Choice Sending SMTP email from within BizTalk Orchestration http://www.eggheadcafe.com/tutorials...il-from-w.aspx |
Find last 20 values in column
What do you want to do with these 10 lowest values? Sounds like a golf
handicap calculation. What if there aren't 20 numbers? -- Biff Microsoft Excel MVP <David Donald wrote in message ... Hi guys, Along a similar line to finding the 2nd last value in a column, I have a question that's a little trickier.. I have a single column of numbers ~100 rows high.. Not all cells have values in them and each week another value is entered at the bottom of the column.. I need to find the 10 lowest values in the last 20 cells that have values in them.. So each week the top value of the 20 'found' values will drop off and the most recent value will become part of the list of 20 values that the 10 lowest values are calculated from.. Any ideas?? Thanks in advance, Doggy L. Howard Kittle wrote: Hi Greg,Here is another that seems to do what you want. 18-Jan-10 Hi Greg, Here is another that seems to do what you want. =OFFSET(F1,COUNTA(F:F)-2,0) HTH Regards, Howard Previous Posts In This Thread: On Monday, January 18, 2010 8:41 AM Greg wrote: 2nd to last value in column Hi everyone. My question has to do with the 2nd to last cell with data in a column. To get the last data, I am using "=LOOKUP(10^100,A2:D2)" but how can I get the data from the cell right above that? For instance, my data looks like this (multiple tabs for different entities): $ of Accounts 1/1/10 6300 $ of Accounts 1/2/10 1550 So, I want my totals page to show the last 2 entries (# and $). TIA, any help would be wonderful, Thanks, Greg On Monday, January 18, 2010 8:52 AM Jacob Skaria wrote: Try the below to get the data from the cell right above that? Try the below to get the data from the cell right above that? With data in ColA =INDEX(A:A,MATCH(10^10,A:A)-1) -- Jacob "Greg" wrote: On Monday, January 18, 2010 8:59 AM Greg wrote: Worked like a charm.Thank you very much,Greg"Jacob Skaria" wrote: Worked like a charm. Thank you very much, Greg "Jacob Skaria" wrote: On Monday, January 18, 2010 8:59 AM Mike H wrote: Greg,You question is a bit muddled, you ask for the second to last value in Greg, You question is a bit muddled, you ask for the second to last value in a column yet your formula is a row (A2:D2), which is it. Assumin no blanks try these Row =INDEX(A2:D2,COUNT(A2:D2)-1) column =INDEX(A1:A6,COUNT(A1:A6)-1) -- Mike When competing hypotheses are equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. Occam''''s razor (Abbrev) "Greg" wrote: On Monday, January 18, 2010 9:19 AM Greg wrote: You are correct. I used a copied formula template (A2:D2) for my example. You are correct. I used a copied formula template (A2:D2) for my example. The actual number I was looking for was in a column. My fault. Thank you for your input. Having both formulas is great. Greg "Mike H" wrote: On Monday, January 18, 2010 2:31 PM L. Howard Kittle wrote: Hi Greg,Here is another that seems to do what you want. Hi Greg, Here is another that seems to do what you want. =OFFSET(F1,COUNTA(F:F)-2,0) HTH Regards, Howard Submitted via EggHeadCafe - Software Developer Portal of Choice Sending SMTP email from within BizTalk Orchestration http://www.eggheadcafe.com/tutorials...il-from-w.aspx |
All times are GMT +1. The time now is 02:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com