In this post we will see how we could select n rows in any excel formula.
Suppose we have data in column A from row A2 to row A20 and we have a value in cell E2 which defines the number of rows to be included in the formula.
If E2 has 14 we want the sum of values from A2 to A14 and if E2 has value 6 then we want the sum of numbers from A2 to A6.
We could not hardcode the value of N to include number of rows each time the value in E2 changes so how to select n rows in excel dynamically.
We want summation of values till n rows and n is defined in cell E2.
Index function in excel comes to rescue us in these kind of situation, it could return a dynamic range and select n rows as we will see in next steps.
Insert the formula =SUM(A1:INDEX($A$1:$A$20, $E$2, 1)) in cell B2.
The Index function will take care of the range that is to be included in the formula as you could see below that we have got the sum of 639 which is the summation of values from A2 to A14 as we have 14 in cell E2 which has been passed as an argument in the INDEX formula.
Hope this helped.
