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?

excel last non zero value in a row

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.

excel excel last non zero value in a row

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.

excel excel excel last non zero value in a row

 

You can find similar Excel Questions and Answer hereunder

1) Is there a way I can easily identify the duplicate values in a dataset?

2) In a Table , how can I add a Total Row that displays the maximum value in a column of numbers?

3) How to read a value from a cell in vba in Excel

4) How to disable ability to insert Rows and Columns in Excel (using VBA)?

5) How can I find the slab in which a given value fits?

6) Can I add a Text histogram to Table data to make it visually appealing and easy to interpret?

7) 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 High and Low values for the last 10 trading days?

8) Given a raw data Table, how can I find the value of a field for a specific value of another field?

9) How can I set non-contiguous print area using VBA?

10) How can I find the last used cell in a Column in VBA?

11) I have to enter non alphanumeric characters in a cell using VBA - how can I get their codes for use in VBA?

12) How can I filter a table to get all records that have less than a particular value in a specific column?

13) Filtering the value field in a pivot table in Excel

14) I am considering an investment of $500 per month for 3 years @ 6% interest rate. What is the Present Value of investment?

15) How can I remove all nonprintable characters from text?