| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
2nd and 3rd last number in a column - how to get it?
Hi there,
I currently have a table from columns V3 to AM1000. The cells contain numbers varying from 0 to 50. I have a formula in place that calculates the last number in the column: =LOOKUP(2,1/(V$3:V$1000<""),V$3:V$1000) what I'm looking for is 2 formulas, - one that will return the 2nd last number in the column into cell AP3 - one that will return the 3rd last number in the column into cell AQ3 Ex. ColumnV looks like the following: 0 1 0 1 2 3 0 0 Now, my forumla gives my 0 as the last number. I would like AP3 to show the 2nd last number (0) and AQ3 to show (3) as it's the 3rd last number. Thanks! Matt |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
2nd and 3rd last number in a column - how to get it?
In AP3 enter =OFFSET(INDEX($V3:$V1000,MATCH(99^99,$V3:$V1000)),-1,0)
In AQ3 enter =OFFSET(INDEX($V3:$V1000,MATCH(99^99,$V3:$V1000)),-2,0) Gord Dibben MS Excel MVP On Mon, 11 Feb 2008 11:48:02 -0800, mpenkala wrote: Hi there, I currently have a table from columns V3 to AM1000. The cells contain numbers varying from 0 to 50. I have a formula in place that calculates the last number in the column: =LOOKUP(2,1/(V$3:V$1000<""),V$3:V$1000) what I'm looking for is 2 formulas, - one that will return the 2nd last number in the column into cell AP3 - one that will return the 3rd last number in the column into cell AQ3 Ex. ColumnV looks like the following: 0 1 0 1 2 3 0 0 Now, my forumla gives my 0 as the last number. I would like AP3 to show the 2nd last number (0) and AQ3 to show (3) as it's the 3rd last number. Thanks! Matt |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
2nd and 3rd last number in a column - how to get it?
As long as there are no empty/blank/text cells within the range:
2nd to last: =INDEX(V$3:V$1000,COUNT(V$3:V$1000)-1) 3rd to last: =INDEX(V$3:V$1000,COUNT(V$3:V$1000)-2) For the last number: =LOOKUP(1E100,V$3:V$1000) -- Biff Microsoft Excel MVP "mpenkala" wrote in message ... Hi there, I currently have a table from columns V3 to AM1000. The cells contain numbers varying from 0 to 50. I have a formula in place that calculates the last number in the column: =LOOKUP(2,1/(V$3:V$1000<""),V$3:V$1000) what I'm looking for is 2 formulas, - one that will return the 2nd last number in the column into cell AP3 - one that will return the 3rd last number in the column into cell AQ3 Ex. ColumnV looks like the following: 0 1 0 1 2 3 0 0 Now, my forumla gives my 0 as the last number. I would like AP3 to show the 2nd last number (0) and AQ3 to show (3) as it's the 3rd last number. Thanks! Matt |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
2nd and 3rd last number in a column - how to get it?
Thanks Gord - works great!
Matt "Gord Dibben" wrote: In AP3 enter =OFFSET(INDEX($V3:$V1000,MATCH(99^99,$V3:$V1000)),-1,0) In AQ3 enter =OFFSET(INDEX($V3:$V1000,MATCH(99^99,$V3:$V1000)),-2,0) Gord Dibben MS Excel MVP On Mon, 11 Feb 2008 11:48:02 -0800, mpenkala wrote: Hi there, I currently have a table from columns V3 to AM1000. The cells contain numbers varying from 0 to 50. I have a formula in place that calculates the last number in the column: =LOOKUP(2,1/(V$3:V$1000<""),V$3:V$1000) what I'm looking for is 2 formulas, - one that will return the 2nd last number in the column into cell AP3 - one that will return the 3rd last number in the column into cell AQ3 Ex. ColumnV looks like the following: 0 1 0 1 2 3 0 0 Now, my forumla gives my 0 as the last number. I would like AP3 to show the 2nd last number (0) and AQ3 to show (3) as it's the 3rd last number. Thanks! Matt |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
2nd and 3rd last number in a column - how to get it?
Thanks for the feedback.
See also Biff's shorter suggestion if you have no blanks. Gord On Mon, 11 Feb 2008 12:26:31 -0800, mpenkala wrote: Thanks Gord - works great! Matt "Gord Dibben" wrote: In AP3 enter =OFFSET(INDEX($V3:$V1000,MATCH(99^99,$V3:$V1000)),-1,0) In AQ3 enter =OFFSET(INDEX($V3:$V1000,MATCH(99^99,$V3:$V1000)),-2,0) Gord Dibben MS Excel MVP On Mon, 11 Feb 2008 11:48:02 -0800, mpenkala wrote: Hi there, I currently have a table from columns V3 to AM1000. The cells contain numbers varying from 0 to 50. I have a formula in place that calculates the last number in the column: =LOOKUP(2,1/(V$3:V$1000<""),V$3:V$1000) what I'm looking for is 2 formulas, - one that will return the 2nd last number in the column into cell AP3 - one that will return the 3rd last number in the column into cell AQ3 Ex. ColumnV looks like the following: 0 1 0 1 2 3 0 0 Now, my forumla gives my 0 as the last number. I would like AP3 to show the 2nd last number (0) and AQ3 to show (3) as it's the 3rd last number. Thanks! Matt |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count number of cells and total in one column, based on another column suffix | Excel Worksheet Functions | |||
| Finding a number in a column out of an array in another column | Excel Discussion (Misc queries) | |||
| Display missing Part Number if Column A does not match column B | Excel Worksheet Functions | |||
| how i write a number on a column A and see that number on letters. | Excel Worksheet Functions | |||
| Auto number w/ different letter-number combos in same column | Excel Worksheet Functions | |||
Linear Mode
