The small function
- Thread starter Ufoo
- Start date
Faseeh
Excel Ninja
The Row function is used with small to make items unique. Say in your data 5 is repeated three times in a column. We make every value unique by adding a very small number. This number is generated by dividing row number by a very large value say 100000 so if the values are range A1:A3, then it becomes:
5+1/100000=5.00001
5+2/100000=5.00002
5+3/100000=5.00003
So now instead of looking for 5, we will look for 5.00001,...,5.00003
It is helpful when you have duplicate values and you want to fetch data for first k small values.
5+1/100000=5.00001
5+2/100000=5.00002
5+3/100000=5.00003
So now instead of looking for 5, we will look for 5.00001,...,5.00003
It is helpful when you have duplicate values and you want to fetch data for first k small values.
NARAYANK991
Excel Ninja
Hi ,
Both the SMALL and the LARGE functions make use of the second parameter to return the appropriate value from the array / range.
Suppose we have a named range List , which consists of the following numbers :
7 ; 3 ; 3 ; 2 ; 6 ; 9 ; 1 ; 4
If we sort this list of numbers in descending order , it would look like this :
9 ; 7 ; 6 ; 4 ; 3 ; 3 ; 2 ; 1
If we sort this list of numbers in ascending order , it would look like this :
1 ; 2 ; 3 ; 3 ; 4 ; 6 ; 7 ; 9
When we use the LARGE function , we are basically returning values from the list of numbers sorted in descending order.
When we use the SMALL function , we are basically returning values from the list of numbers sorted in ascending order.
Thus , =LARGE(List , 1) will return 9 ; this is equivalent to the formula =MAX(List).
Similarly , =SMALL(List , 1) will return 1 ; this is equivalent to the formula =MIN(List).
=LARGE(List , 2) will return 7 , =LARGE(List , 3) will return 6 , and so on.
=SMALL(List , 2) will return 2 , =SMALL(List , 3) will return 3 , and so on.
Why we use COUNTIF and ROWS in the second parameter for both the functions is because we wish to make it dynamic , so that when the formula is copied downwards , the second parameter automatically changes.
The reason for using COUNTIF as against ROWS is different ; it is to take care to return appropriate values in cases when the original list has duplicates.
For example , in the above list , the number 3 occurs twice.
Thus =LARGE(List , 3) and =LARGE(List , 4) will both return 3.
So , if we use a formula such as =LARGE(List , ROWS($A$1:$A1)) , and copy it downwards , the value 3 will be returned twice.
Now suppose we had names associated with these values , and wished to return the name associated with each value ; as long as the value is unique , there is no problem , but when we come to 3 , how do we retrieve the second name associated with the value 3 ?
This is where the COUNTIF function is useful.
Suppose we have List containing the numbers in column A , and the associated names in column B , with the names being defined as a named range called Names.
Now in column F , we have the formula :
=LARGE(List , ROWS($A$1:$A1))
entered in F1 , and copied downwards till F8.
In G1 , we enter the formula :
=INDEX(Names, LARGE(IF(List = F1, ROW(List) - MIN(ROW(List)) + 1),COUNTIF($F$1:$F1, $F1)))
This will list , in column G , all the names corresponding to the values in column F.
What happens when we have two values which are the same , as has happened with the value 3 ?
When we come to the first occurrence of 3 , the IF function returns an array of values as follows :
{FALSE;2;3;FALSE;FALSE;FALSE;FALSE;FALSE}
From this array of values , the LARGE function should first return 3 , and in the next row return 2 ; this is enabled by use of the COUNTIF function , which when it encounters the first 3 in column F returns 1 , and when it comes across the second 3 in column F , returns 2.
Thus , along with the LARGE function , what we have is :
LARGE({FALSE;2;3;FALSE;FALSE;FALSE;FALSE;FALSE} , 1)
in the first case , and :
LARGE({FALSE;2;3;FALSE;FALSE;FALSE;FALSE;FALSE} , 2)
You can see that the first formula part will return 3 , and the second will return 2.
Narayan
Both the SMALL and the LARGE functions make use of the second parameter to return the appropriate value from the array / range.
Suppose we have a named range List , which consists of the following numbers :
7 ; 3 ; 3 ; 2 ; 6 ; 9 ; 1 ; 4
If we sort this list of numbers in descending order , it would look like this :
9 ; 7 ; 6 ; 4 ; 3 ; 3 ; 2 ; 1
If we sort this list of numbers in ascending order , it would look like this :
1 ; 2 ; 3 ; 3 ; 4 ; 6 ; 7 ; 9
When we use the LARGE function , we are basically returning values from the list of numbers sorted in descending order.
When we use the SMALL function , we are basically returning values from the list of numbers sorted in ascending order.
Thus , =LARGE(List , 1) will return 9 ; this is equivalent to the formula =MAX(List).
Similarly , =SMALL(List , 1) will return 1 ; this is equivalent to the formula =MIN(List).
=LARGE(List , 2) will return 7 , =LARGE(List , 3) will return 6 , and so on.
=SMALL(List , 2) will return 2 , =SMALL(List , 3) will return 3 , and so on.
Why we use COUNTIF and ROWS in the second parameter for both the functions is because we wish to make it dynamic , so that when the formula is copied downwards , the second parameter automatically changes.
The reason for using COUNTIF as against ROWS is different ; it is to take care to return appropriate values in cases when the original list has duplicates.
For example , in the above list , the number 3 occurs twice.
Thus =LARGE(List , 3) and =LARGE(List , 4) will both return 3.
So , if we use a formula such as =LARGE(List , ROWS($A$1:$A1)) , and copy it downwards , the value 3 will be returned twice.
Now suppose we had names associated with these values , and wished to return the name associated with each value ; as long as the value is unique , there is no problem , but when we come to 3 , how do we retrieve the second name associated with the value 3 ?
This is where the COUNTIF function is useful.
Suppose we have List containing the numbers in column A , and the associated names in column B , with the names being defined as a named range called Names.
Now in column F , we have the formula :
=LARGE(List , ROWS($A$1:$A1))
entered in F1 , and copied downwards till F8.
In G1 , we enter the formula :
=INDEX(Names, LARGE(IF(List = F1, ROW(List) - MIN(ROW(List)) + 1),COUNTIF($F$1:$F1, $F1)))
This will list , in column G , all the names corresponding to the values in column F.
What happens when we have two values which are the same , as has happened with the value 3 ?
When we come to the first occurrence of 3 , the IF function returns an array of values as follows :
{FALSE;2;3;FALSE;FALSE;FALSE;FALSE;FALSE}
From this array of values , the LARGE function should first return 3 , and in the next row return 2 ; this is enabled by use of the COUNTIF function , which when it encounters the first 3 in column F returns 1 , and when it comes across the second 3 in column F , returns 2.
Thus , along with the LARGE function , what we have is :
LARGE({FALSE;2;3;FALSE;FALSE;FALSE;FALSE;FALSE} , 1)
in the first case , and :
LARGE({FALSE;2;3;FALSE;FALSE;FALSE;FALSE;FALSE} , 2)
You can see that the first formula part will return 3 , and the second will return 2.
Narayan
Chihiro
Excel Ninja
Just as example. I used COUNTIFS -1 as "k" in thread below.
It was used to pull DateTime from column A, where count of matching criteria is 1 less than the line being checked (I.E. previous DateTime where conditions are met).
http://chandoo.org/forum/threads/fo...difference-between-collected-by-column.31188/
It was used to pull DateTime from column A, where count of matching criteria is 1 less than the line being checked (I.E. previous DateTime where conditions are met).
http://chandoo.org/forum/threads/fo...difference-between-collected-by-column.31188/
Thanks Narayan. Hence ROW is for returning numbers and COUNTIF is for text. Right?
