Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Search Column for Last Numeric Value"
Greetings,
I would like for a worksheet to search a particular column for the last numeric value...that is to say the last value (cell) that is an an actual number and not the "IF" scenario I created which if the conditions do not meet any of the criteria set then the cell is left with a "N/A" to show nothing occured. I would like it to search the column down until it finds the last number in it and then perform some calculation. The calculation (math) I don't have any problems with its just this "search" function, I've tried a couple different things but I can't seem to hit it. Exammple: A B C D 1 Q R 12 S 2 Q R 13.5 S 3 Q R 10 S 4 Q R N/A S Q=SOME TEXT VALUE R=SOME TEXT VALUE S=SOME TEXT VALUE C COLUMN IS THE NUMERIC COLUMN WITH THE "N/A" value that ends the search for the last cell (C3) that has the last numeric value. The cell in C column will change for each worksheet. Thanks, J. Badinger |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Search Column for Last Numeric Value"
=INDEX(C:C,MAX(IF(ISNUMBER(C1:C50),ROW(C1:C50)),1) )
Entered with Ctrl+Shift+Enter rather than just enter (since this is an array formula) will get the last numeric value. Adjust the 50 to the last row that needs to be checked - but be conservative since the more cells checked, the slower the recalculate. -- Regards, Tom Ogilvy "Jim Badinger" wrote in message ... Greetings, I would like for a worksheet to search a particular column for the last numeric value...that is to say the last value (cell) that is an an actual number and not the "IF" scenario I created which if the conditions do not meet any of the criteria set then the cell is left with a "N/A" to show nothing occured. I would like it to search the column down until it finds the last number in it and then perform some calculation. The calculation (math) I don't have any problems with its just this "search" function, I've tried a couple different things but I can't seem to hit it. Exammple: A B C D 1 Q R 12 S 2 Q R 13.5 S 3 Q R 10 S 4 Q R N/A S Q=SOME TEXT VALUE R=SOME TEXT VALUE S=SOME TEXT VALUE C COLUMN IS THE NUMERIC COLUMN WITH THE "N/A" value that ends the search for the last cell (C3) that has the last numeric value. The cell in C column will change for each worksheet. Thanks, J. Badinger |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Search Column for Last Numeric Value"
Faster would be
=Index(C:C,Match(9.99999999999999E+307,C:C,1),1) Regards, Tom Ogilvy Tom Ogilvy wrote in message ... =INDEX(C:C,MAX(IF(ISNUMBER(C1:C50),ROW(C1:C50)),1) ) Entered with Ctrl+Shift+Enter rather than just enter (since this is an array formula) will get the last numeric value. Adjust the 50 to the last row that needs to be checked - but be conservative since the more cells checked, the slower the recalculate. -- Regards, Tom Ogilvy "Jim Badinger" wrote in message ... Greetings, I would like for a worksheet to search a particular column for the last numeric value...that is to say the last value (cell) that is an an actual number and not the "IF" scenario I created which if the conditions do not meet any of the criteria set then the cell is left with a "N/A" to show nothing occured. I would like it to search the column down until it finds the last number in it and then perform some calculation. The calculation (math) I don't have any problems with its just this "search" function, I've tried a couple different things but I can't seem to hit it. Exammple: A B C D 1 Q R 12 S 2 Q R 13.5 S 3 Q R 10 S 4 Q R N/A S Q=SOME TEXT VALUE R=SOME TEXT VALUE S=SOME TEXT VALUE C COLUMN IS THE NUMERIC COLUMN WITH THE "N/A" value that ends the search for the last cell (C3) that has the last numeric value. The cell in C column will change for each worksheet. Thanks, J. Badinger |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"Search Column for Last Numeric Value"
Very helpful, I have found other uses for this now!!!
Thanks again -----Original Message----- Greetings, I would like for a worksheet to search a particular column for the last numeric value...that is to say the last value (cell) that is an an actual number and not the "IF" scenario I created which if the conditions do not meet any of the criteria set then the cell is left with a "N/A" to show nothing occured. I would like it to search the column down until it finds the last number in it and then perform some calculation. The calculation (math) I don't have any problems with its just this "search" function, I've tried a couple different things but I can't seem to hit it. Exammple: A B C D 1 Q R 12 S 2 Q R 13.5 S 3 Q R 10 S 4 Q R N/A S Q=SOME TEXT VALUE R=SOME TEXT VALUE S=SOME TEXT VALUE C COLUMN IS THE NUMERIC COLUMN WITH THE "N/A" value that ends the search for the last cell (C3) that has the last numeric value. The cell in C column will change for each worksheet. Thanks, J. Badinger . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=IF(ISNUMBER(SEARCH("ELB",B2)),"Pipe") add more like "ELB" "FLG" | Excel Worksheet Functions | |||
=IF(ISERROR(SEARCH("insurance",A125,1)),"","*") | Excel Worksheet Functions | |||
Excel: Changing "numeric $" to "text $" in a different cell. | Excel Worksheet Functions | |||
If A3=alpha numeric,"X", if A3=text,"Y", Blank | Excel Worksheet Functions | |||
Binocular search tool, Excel, loses column designation at "Find" | Excel Discussion (Misc queries) |