Excel Made Easy

Last non zero value in a row in Excel

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.

You can find similar Excel Questions and Answer hereunder

1) How can I prevent users from entering duplicate values in a range?

2) How can I extract First Name and Last Name from a cell that has Full name?

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

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

5) How to freeze a row or column in Excel

6) How can I ensure that user enters only certain acceptable values in an input cell?

7) Is there a way to create an visually impactful report with stop lights indicating automatically if Target is met?

8) How do you know which column was used last, Here the explanation to find it with VBA

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

10) I have a table where inputs have to come from various users. In Excel, how can I highlight a row automatically that does not have input so that I can follow up with the concerned?