Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last "non-blank" value in a Row...
I've found solutions for finding the last non-blank/non-zero value in a
column, but I couldn't figure out how to convert for a row: Previously I had five cells to populate (H6-L6). What I need is the last value that is completed. The first week would only have H6, the next week I6, etc... The formula I had (which worked fine): =IF(Q6="",IF(O6="",IF(M6="",IF(K6="",IF(I6="",G6,I 6),K6),M6),O6),Q6) (A1:A9) Now that I have 10 cells (H6-Q6) to fill out, I can't use the formula above as there are too many IF Statements). Unfortunately, the value each week could go up or down, so I can't do min/max. Appreciate any insight you all might have. V/R KSL. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last "non-blank" value in a Row...
This will return the rightmost numeric value from the range if there is one:
=LOOKUP(1E100,H6:Q6) If there isn't a numeric value in the range the formula will return #N/A. To prevent the error you can use this version: =IF(COUNT(H6:Q6),LOOKUP(1E100,H6:Q6),"") -- Biff Microsoft Excel MVP "Leonhardtk" wrote in message ... I've found solutions for finding the last non-blank/non-zero value in a column, but I couldn't figure out how to convert for a row: Previously I had five cells to populate (H6-L6). What I need is the last value that is completed. The first week would only have H6, the next week I6, etc... The formula I had (which worked fine): =IF(Q6="",IF(O6="",IF(M6="",IF(K6="",IF(I6="",G6,I 6),K6),M6),O6),Q6) (A1:A9) Now that I have 10 cells (H6-Q6) to fill out, I can't use the formula above as there are too many IF Statements). Unfortunately, the value each week could go up or down, so I can't do min/max. Appreciate any insight you all might have. V/R KSL. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last "non-blank" value in a Row...
=LOOKUP(10000000000,6:6) if it's a number
"Leonhardtk" wrote: I've found solutions for finding the last non-blank/non-zero value in a column, but I couldn't figure out how to convert for a row: Previously I had five cells to populate (H6-L6). What I need is the last value that is completed. The first week would only have H6, the next week I6, etc... The formula I had (which worked fine): =IF(Q6="",IF(O6="",IF(M6="",IF(K6="",IF(I6="",G6,I 6),K6),M6),O6),Q6) (A1:A9) Now that I have 10 cells (H6-Q6) to fill out, I can't use the formula above as there are too many IF Statements). Unfortunately, the value each week could go up or down, so I can't do min/max. Appreciate any insight you all might have. V/R KSL. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
If A3=alpha numeric,"X", if A3=text,"Y", Blank | Excel Worksheet Functions | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Changing "returned" values from "0" to "blank" | Excel Worksheet Functions |