Can you please explain the role of Row(A1) in the formula?

Hi ,

The SMALL function or the LARGE function has the following syntax :

=SMALL(range , k)

where

**range** is a single or multi-row , single or multi-column reference , and

**k** is an integer taking the values 1 , 2 , 3 ,...

Depending on the value of

**k** , the SMALL function will return the smallest , the second smallest , the third smallest value in a range.

However , when you use the SMALL function in a formula , where the formula is entered in one cell and then copied either across columns or down rows , then if you have the value 1 in the formula , that value will be copied to all other cells. Thus , if you want the first cell to contain the smallest value , the second cell to contain the second smallest value , the third cell to contain the third smallest value and so on , you will not get what you want. Every cell will contain the same value.

Thus , if you want

**k** to change from 1 to 2 to 3 and beyond , then there to be a variable in k , not a fixed value such as 1 or 2 or 3.

This is the reason we use the ROW function , with a parameter such as A1.

=ROW(A1) returns 1

=ROW(A2) returns 2

=ROW(A3) returns 3

and so on.

When we have a formula such as :

=SMALL($J$7:$J$19 , ROW(A1))

entered in a cell say D3 , and copy this formula down to cells D4 , D5 , D6 and beyond , then the formulas in those cells will be :

D3 : =SMALL($J$7:$J$19 , ROW(A

**1**))

D4 : =SMALL($J$7:$J$19 , ROW(A

**2**))

D5 : =SMALL($J$7:$J$19 , ROW(A

**3**))

D6 : =SMALL($J$7:$J$19 , ROW(A

**4**))

So because the

**k** value changes , the result of the formula will also change , thus giving us what we want.

Another way of changing the

**k** value is to use the ROWS function , as follows :

D3 : =SMALL($J$7:$J$19 , ROWS(A$

**1**:A

**1**))

D4 : =SMALL($J$7:$J$19 , ROWS(A$

**1**:A

**2**))

D5 : =SMALL($J$7:$J$19 , ROWS(A$

**1**:A

**3**))

D6 : =SMALL($J$7:$J$19 , ROWS(A$

**1**:A

**4**))

=ROWS(A1:A1) returns the number of rows within the range A1:A1 ; the result is 1.

=ROWS(A2:A2) returns the number of rows within the range A2:A2 ; the result is still 1.

=ROWS(A99:A99) returns the number of rows within the range A99:A99 ; the result is still 1.

If we prefix the first row reference with the $ symbol to make it absolute , and leave the second row reference relative (without the $ symbol) , then when we copy the formula downwards , the first row reference will remain unchanged , while the second will change from one cell to the next.

=ROWS(A$

**1**:A

**2**) returns the number of rows within the range A1:A2 ; the result is

**2**.

=ROWS(A$

**1**:A

**3**) returns the number of rows within the range A1:A3 ; the result is

**3**.

=ROWS(A$

**1**:A

**4**) returns the number of rows within the range A1:A4 ; the result is

**4**.

If you have any doubts , feel free to ask.

Narayan