Retrieving Every Nth Value in a Range

استعادة او عرض القيم من مجال او الخلايا في اكسل

بعدد معين او ثابت لكل الخلايا

 

n this article, we will learn how to retrieve every nth value in a range. We will use Index function with Row function.

Why and how we use Index and row function?

We use Index function to return value from the intersection between the row number and the column number in an Array. This function will pick the number and give use as output.

Row function is used to return the row number of a cell reference. So in this situation row function will help to pick every nth value from range.

Let’s take an example and understand how we use Index and row function?

Let’s take an example to understand how we can retrieve the Nth value in a row, column and range.

We have Agent data in Excel. Column A contains agent name and column B contains revenue. From column B, we want to retrieve every nth value.


 

 

 

 

 

 

 

 

 

 

 

 

[ads1]

Follow below steps to retrieve the Nth value from the list:-

• Enter the formula in cell C2.


• =INDEX($B$2:$B$23,(ROW()-2)*9+9)


• Press Enter.


• Copy the same formula in next cells according to data.


• Below you can see formula has returned every nth value in column C.

 

Note: – If you want to return every 3^rd value, then you just need to change in formula 3 at the place of 9, and formula will pick every 3^rd value from the range.

This is the way we can use Excel formulas Index and row to get the Nth, 3^rd, 4^thetc. value from the range.