How does small function find the k -SMALL (array, k)?

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

How does small function find the k -SMALL (array, k)?

Post by BittenApple »

Hello all,

SMALL(IF((Sheet2!$A$2:$A$200=$A$2)*(Sheet2!$B$2:$B$200=$B$2),ROW(Sheet2!$A$2:$A$200)-MIN(ROW(Sheet2!$A$2:$A$200))+1),ROWS($A$5:$A7)


ROW(Sheet2!$A$2:$A$200)-MIN(ROW(Sheet2!$A$2:$A$200))+1) ---> This part equals to 1.
How does this function: ROWS($A$5:$A7) which equals to 3 can be found in Small function?


Regards,

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: How does small function find the k -SMALL (array, k)?

Post by StuartR »

I'm not sure I understand what you are asking.

ROWS($A$5:$A7) counts the number of rows in the array $A$5:$A7, which is 3 (rows 5, 6, and 7)
ROW(Sheet2!$A$2:$A$200)-MIN(ROW(Sheet2!$A$2:$A$200))+1) returns the array 1, 2, 3, ..., 198, 199

I have no idea what the IF function is doing though
StuartR


User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: How does small function find the k -SMALL (array, k)?

Post by rory »

The IF function is returning FALSE for anything that doesn't match the two criteria, or the position (row number - 1) if it does match. The SMALL function is then returning the 3rd smallest value/position. This is presumably part of an INDEX function.
Regards,
Rory

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How does small function find the k -SMALL (array, k)?

Post by HansV »

BittenApple, it would be much better if you asked these questions in the thread where the formula was posted than in a new thread.
Best wishes,
Hans

BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: How does small function find the k -SMALL (array, k)?

Post by BittenApple »

Hans and the rest of team,

Thanks for the responses.

I will do and thanks for your advice. I thought that would be confusing to post it on the same thread.

What StuartR explains helped me to understand more and getting close to the point.


Back to Small(array,k), what is the array for small function when if statement evaluates to false on row one?

what is the array for small function when if statement evaluates to true on row one?

I think if I am not wrong, the array has the value of 2 and then how does small function returns the third smallest value?

Regards,

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: How does small function find the k -SMALL (array, k)?

Post by StuartR »

BittenApple wrote:
03 Jun 2021, 04:25
I think if I am not wrong, the array has the value of 2 and then how does small function returns the third smallest value?
The array did not have the value of 2, it had the value of {1,2,3,...,197,198,199}
The small function finds the value indicated so if k is 3 it finds the third smallest number in the array, which is of course 3.
StuartR


BittenApple
BronzeLounger
Posts: 1498
Joined: 01 Mar 2015, 02:03

Re: How does small function find the k -SMALL (array, k)?

Post by BittenApple »

Thanks StuartR,
It is coming clear, because I thought it has 2!
Respectfully,