MrExcel Publishing
Your One Stop for Excel Tips & Solutions

last cell's 0 & "" value


Posted by meraldo on December 22, 2001 6:33 PM

I need a furmula/function to return the last cell's value in a row whose value is not a zero and not a ""
Can someone ple hlp? Thanks and
Seasons Greetings


Posted by Aladin Akyurek on December 23, 2001 12:33 AM

Meraldo --

=INDIRECT(ADDRESS(ROW(A1:J1),MAX((A1:J1<>0)*(COLUMN(A1:J1)))))

will give you what you want. Adjust the range to your situation.

Aladin

=========

Posted by Aladin Akyurek on December 23, 2001 12:37 AM

that the formula must be array-entered. In order to array-enter a formula, you need to hit CONTROL+SHIFT+ENTER at the same time, not just ENTER.