Get last 10 entries in a table in Excel

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 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 get last 10 entries in a table

 

You can find similar Excel Questions and Answer hereunder

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

2) 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?

3) How do you know which row was used the last. Here the explanation to find it with VBA

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

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

6) How can I declare a variable in VBA, what is important to declare

7) Is there a way to get a log value of a number using Excel?

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

9) I conducted a test and the test scores are available - how can I get Percentile rank of students?

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

 

Here the previous and next chapter