get last 10 entries in a table in Excel

Q87. 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?

Table below is the raw data that I capture. I update one row for each trading day. I want to set up the Worksheet to automatically calculate the HIGH and LOW values for the last 10 sessions.

excel get last 10 entries in a table

To do it in Excel, here is the answer:

a) High value in last 10 trading sessions corresponds to Max value among last 10 values in Col "High" (Col M).

Enter formula =MAX(OFFSET(M2,COUNT(M:M),0,-10)) to get High value in last 10 trading sessions as shown below.

The first argument of OFFSET function refers to the Title in column as reference.

COUNT(M:M) counts numeric values in Column M (Title "High" is excluded since it is a text) and returns reference to the last cell with numeric value in Column M (M21)*** .

The fourth argument -10 extends the range referred by OFFSET by 10 rows above the last cell M21 to create the range M12:M21.

Max formula computes the maximum value in range M12:M21

***assuming no blank rows or rows with text in column M.

excel excel get last 10 entries in a table

b) Low value in last 10 trading sessions corresponds to Min value among last 10 values in Col "Low" (Col N).

Enter formula =MIN(OFFSET(N2,COUNT(N:N),0,-10)) to get Low value in last 10 trading sessions as shown below.

The first argument of OFFSET function refers to the Title in column as reference.

COUNT(N:N) counts numeric values in Column N (Title "Low" is excluded since it is a text) and returns reference to the last cell with numeric value in Column N (N21)*** .

The fourth argument -10 extends the range referred by OFFSET by 10 rows above the last cell N21 to create the range N12:N21.

Min formula computes the maximum value in range N12:N21

***assuming no blank rows or rows with text in column N.

excel excel excel get last 10 entries in a table

 

You can find similar Excel Questions and Answer hereunder

1) Vba code to password protect workbook in Excel

2) How can I find the least common multiple using Excel?

3) How can I remove Outline from my WorkSheet?

4) I have a large list of clients whose current Age I need to maintain in my database as it influences costs - how can I have Excel display the current age at all times?

5) I want to force a page break at a certain row - how can I do that?

6) How can I get the most frequently occurring text in a range?

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

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

9) How can I get the most frequently occurring text in a range?

10) How can I display fractions as it is in a cell (instead of displaying as a decimal)?

11) How can I get the last non-zero value in a row?

12) How do i apply a formula to an entire column in Excel

13) How can I get the information about my current operating environment (OS version, Excel version, Current Directory)

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

15) How can I get row count of filtered data?