SMALL and LARGE
Hello everyone.
I have a column of numbers in an array called TEST 1 1 3 4 5 6 7 7 Why when I run the formula =small(test,2) I get 1 and with =large(test,2) I get 7. My logic suggests I should get 3 and 6? Please help. Thanks in anticipation. John |
SMALL and LARGE
=SMALL (test,N) returns the Nth value when the elements
of test are arranged in ascending order, so =SMALL(test,1) = 1 =SMALL(test,2) = 1 =SMALL(test,3) = 3 etc. Similarly for LARGE. You have to be a bit cleverer to get the Nth smallest number ignoring duplicates. Andrew Taylor Tonto wrote: Hello everyone. I have a column of numbers in an array called TEST 1 1 3 4 5 6 7 7 Why when I run the formula =small(test,2) I get 1 and with =large(test,2) I get 7. My logic suggests I should get 3 and 6? Please help. Thanks in anticipation. John |
SMALL and LARGE
Thanks. I think I pointed that out but still need a solution. John |
SMALL and LARGE
=LARGE(IF(MATCH(S1:S10,S1:S10,0)=ROW(Data)-CELL("Row",S1:S10)+1,S1:S10),2)
=SMALL(IF(MATCH(S1:S10,S1:S10,0)=ROW(Data)-CELL("Row",S1:S10)+1,S1:S10),2) array formulae, so commit with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "Tonto" wrote in message oups.com... Thanks. I think I pointed that out but still need a solution. John |
SMALL and LARGE
Bob
My test array is 12 values between F156 to F167 Please can you talk me through your solution? Thanks John |
SMALL and LARGE
Essentially, it builds an array of the data, replacing any duplications with
FALSE, and then does a LARGE or SMALL on that enhanced array, so that the second largest is then truly the second largest if not the second ranked. The MATCH(S1:S10,S1:S10,0) builds an array of indexes of the data within the formula (as against a range S1:S10) The ROW(S1:S10)-CELL("Row",S1:S10)+1 builds an array of possible indexes, 1-10 in this example. In my example, I could have used just ROW(S1:S10) as I started in row 1, but the rest is proofing regardless of row start. Comparing one against the other gives an array of TRUE/FALSE which drives which items in S1:S10 are out-selected by the IF statement, i.e. giving an array of unique numbers, which is then passed to the LARGE or SMALL function. Your formulae would then look like =LARGE(IF(MATCH(F156:F167,F156:F167,0)=ROW(F156:F1 67)-CELL("Row",F156:F167)+ 1,F156:F167),2) and =SMALL(IF(MATCH(F156:F167,F156:F167,0)=ROW(F156:F1 67)-CELL("Row",F156:F167)+ 1,F156:F167),2) Don't forget ... array formulae -- HTH RP (remove nothere from the email address if mailing direct) "Tonto" wrote in message oups.com... Bob My test array is 12 values between F156 to F167 Please can you talk me through your solution? Thanks John |
SMALL and LARGE
Thanks Bob,
Clever, as usual. Cheers John |
All times are GMT +1. The time now is 05:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com