last non zero value in a row in Excel
Q90. In Excel, how can I get the last non-zero value in a column?
For example, I track a stock on a daily basis and enter the Open, High, Low and Close values for every trading day. In Excel, how can I automatically get the latest Open, Close, High and Low values for use in my tracking Dashboard?
To do it in Excel, here is the answer:
a) Method 1: Enter formula =OFFSET(L2,COUNT(L:L),0) to get latest value in Col L (Open) as shown below.
The first argument of OFFSET function refers to the Title in column as reference.
COUNT(L:L) counts numeric values in Column L (Title "Open" is excluded since it is a text) and returns reference to the last cell with numeric value in Column L (L21)*** .
***assuming no blank rows or rows with text in column M.
Repeat the formula for Col M, N and O to get latest High, Low and Close values.
b) Alternatively, enter the formula =LOOKUP(1,1/(L:L>0),L:L) for getting the latest value for Open (Col L) as shown below. Repeat the formula for Col M, N and O to get latest High, Low and Close values.