If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|