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,
How does small function find the k -SMALL (array, k)?
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
-
- Administrator
- Posts: 12577
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: How does small function find the k -SMALL (array, k)?
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
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
-
- 5StarLounger
- Posts: 817
- Joined: 24 Jan 2010, 15:56
Re: How does small function find the k -SMALL (array, k)?
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
Rory
-
- 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)?
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
Hans
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: How does small function find the k -SMALL (array, k)?
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,
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,
-
- Administrator
- Posts: 12577
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: How does small function find the k -SMALL (array, k)?
The array did not have the value of 2, it had the value of {1,2,3,...,197,198,199}BittenApple wrote: ↑03 Jun 2021, 04:25I 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 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
-
- BronzeLounger
- Posts: 1498
- Joined: 01 Mar 2015, 02:03
Re: How does small function find the k -SMALL (array, k)?
Thanks StuartR,
It is coming clear, because I thought it has 2!
Respectfully,
It is coming clear, because I thought it has 2!
Respectfully,