Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding NEXT to last, non zero numeric value in a row
trying to find a way to automatically return (via formula, not macro- I don't
know squat about macros), automatically return the NEXT TO LAST, non zero, numeric value in a row of cells. All info in the range is numeric. I found this one to find the LAST value: =LOOKUP(E1+100,1/(B3:CQ3),B3:CQ3) but i need the value to the immediate LEFT of what that formula returns. I am a complete newbie. Speak slowly and use small words. Don't know anything about macros, or any formulas beyond simple math |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding NEXT to last, non zero numeric value in a row
Try the below. Please note that this is an array formula. You create array
formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =INDEX(B3:CQ3,LARGE(IF(ISNUMBER(B3:CQ3),IF(B3:CQ3< 0, COLUMN(B3:CQ3))),2)-COLUMN(B3:CQ3)+1) -- Jacob (MVP - Excel) "gergster" wrote: trying to find a way to automatically return (via formula, not macro- I don't know squat about macros), automatically return the NEXT TO LAST, non zero, numeric value in a row of cells. All info in the range is numeric. I found this one to find the LAST value: =LOOKUP(E1+100,1/(B3:CQ3),B3:CQ3) but i need the value to the immediate LEFT of what that formula returns. I am a complete newbie. Speak slowly and use small words. Don't know anything about macros, or any formulas beyond simple math |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding NEXT to last, non zero numeric value in a row
Hi,
Try this array formula =INDEX(3:3,LARGE(IF((B3:AQ3<"")*ISNUMBER(B3:AQ3)* (B3:AQ3<""),COLUMN(B3:AQ3)),2)) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "gergster" wrote: trying to find a way to automatically return (via formula, not macro- I don't know squat about macros), automatically return the NEXT TO LAST, non zero, numeric value in a row of cells. All info in the range is numeric. I found this one to find the LAST value: =LOOKUP(E1+100,1/(B3:CQ3),B3:CQ3) but i need the value to the immediate LEFT of what that formula returns. I am a complete newbie. Speak slowly and use small words. Don't know anything about macros, or any formulas beyond simple math |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding NEXT to last, non zero numeric value in a row
Hi,
small variation from the others. You said all info in the range is numeric, so ISNUMBER is not required, and this can be entered normally (not as an array formula): =INDEX(B3:CQ3,LARGE(INDEX((B3:CQ3<"")*COLUMN(B3:C Q3),),2)-COLUMN(B3:CQ3)+1) "gergster" wrote in message ... trying to find a way to automatically return (via formula, not macro- I don't know squat about macros), automatically return the NEXT TO LAST, non zero, numeric value in a row of cells. All info in the range is numeric. I found this one to find the LAST value: =LOOKUP(E1+100,1/(B3:CQ3),B3:CQ3) but i need the value to the immediate LEFT of what that formula returns. I am a complete newbie. Speak slowly and use small words. Don't know anything about macros, or any formulas beyond simple math |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding only cells with a numeric value | Excel Discussion (Misc queries) | |||
Wildcard for finding the first numeric digit in a cell? | Excel Worksheet Functions | |||
Numeric in Text to convert back to the form of Numeric for VLookup Purposes | Excel Discussion (Misc queries) | |||
Match Single Numeric Criteria and Return Multiple Numeric Labels | Excel Worksheet Functions | |||
Finding the mode (alpha numeric) | Excel Discussion (Misc queries) |