Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last Non Blank
I have a row of numbers (Jan, Feb ..... Total)
I would like the last column (Total) to reflect the last non blank value in the row. I would appreciate any help or direction. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last Non Blank
=LOOKUP(2,1/(A3:M3<""),A3:M3)
adapt to fit your range -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "George W. Barrowcliff" wrote in message ... I have a row of numbers (Jan, Feb ..... Total) I would like the last column (Total) to reflect the last non blank value in the row. I would appreciate any help or direction. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last Non Blank
Try something like this:
With a list of values (or blanks) in A1:L1 The value of the last non-blank cell in that range is M1: =INDEX(A1:L1,MAX(MATCH(10^99,A1:L1),MATCH(REPT("z" ,255),A1:L1))) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "George W. Barrowcliff" wrote: I have a row of numbers (Jan, Feb ..... Total) I would like the last column (Total) to reflect the last non blank value in the row. I would appreciate any help or direction. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last Non Blank
Peo
=LOOKUP(2,1/(A3:M3<""),A3:M3) That is much better than the bloated formula I came up with. (sometimes it would be nice if we could retract a post so others won't waste their time reading it) *********** Regards, Ron XL2002, WinXP "Peo Sjoblom" wrote: =LOOKUP(2,1/(A3:M3<""),A3:M3) adapt to fit your range -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It is a good thing to follow the first law of holes; if you are in one stop digging." Lord Healey "George W. Barrowcliff" wrote in message ... I have a row of numbers (Jan, Feb ..... Total) I would like the last column (Total) to reflect the last non blank value in the row. I would appreciate any help or direction. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last Non Blank
Hi Ron
"Ron Coderre" wrote in message ... Peo =LOOKUP(2,1/(A3:M3<""),A3:M3) That is much better than the bloated formula I came up with. (sometimes it would be nice if we could retract a post so others won't waste their time reading it) Many are the times through the years when I have posted something and wanted to cancel it as soon as I saw another answer (yours included), although there is a cancellation option in most newsreaders I have never found one that really worked, besides once Google has updated it, it's there for "eternity" <bg Peo |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last Non Blank
Thank you both for your efforts. Works exactly like I wanted.
"George W. Barrowcliff" wrote in message ... I have a row of numbers (Jan, Feb ..... Total) I would like the last column (Total) to reflect the last non blank value in the row. I would appreciate any help or direction. Thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last Non Blank
Faster...
=LOOKUP(9.99999999999999E+307,A3:M3) if the last numeric value is what the OP wants. Peo Sjoblom wrote: =LOOKUP(2,1/(A3:M3<""),A3:M3) adapt to fit your range |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Imported Data creates blank cells that aren't really blank | Excel Worksheet Functions | |||
enter numbers in blank cells | Excel Worksheet Functions | |||
Counting blank and filled cells within a range. | Excel Discussion (Misc queries) | |||
blank cells | Excel Discussion (Misc queries) | |||
VLOOKUP, OFFSET, MATCH PROBLEM, HELP? | Excel Worksheet Functions |