Small,Frequency question
 

Ive a formula,
{=SMALL(IF(Sheet1!$A$2:$A$25000=Sheet2!$A$2,IF(She et1!$I$2:$I$25000<=-0.001,Sheet1!$M$2:$M$25000)),11)}but
the 11th largest number repeats itself more than once. I tried this formula,
=SMALL(IF(FREQUENCY(IF(Sheet1!$A$2:$A$25000=Sheet2 !$A$2,IF(Sheet1!$I$2:$I$25000<=-0.001,Sheet1!$L$2:$L$25000)),(IF(Sheet1!$A$2:$A$25 000=Sheet2!$A$2,IF(Sheet1!$I$2:$I$25000<=-0.001,Sheet1!$L$2:$L$25000)))0),(IF(Sheet1!$A$2:$ A$25000=Sheet2!$A$2,IF(Sheet1!$I$2:$I$25000<=-0.001,Sheet1!$L$2:$L$25000)))),11)but
it returned an #N/A.
I tried a different scenario using the formula
=SMALL(IF(FREQUENCY(Sheet1!$L2:$L25,Sheet1!$L2:$L2 5)0,Sheet1!$L2:$L25),9)
and it worked perfectly (once the range didnt have any blank cells). How do
I rectify the problem, and make it work to exclude the blank cells.

Small,Frequency question
 

So, you want to extract the nth smallest. Are you extracting the 1st, 2nd,
3rd, 4th etc smallest also?

If so, then what you can do is use 1 formula to extract the 1st smallest
then use another formula to extract the next smallest that is greater than
the previous smallest. This eliminates returning duplicates which is what it
seems like you want to do.

Extract the 1st smallest (also known as the MIN). Array formula entered in
say A1:

=MIN(IF(Sheet1!$A$2:$A$25000=Sheet2!$A$2,IF(Sheet1 !$I$2:$I$25000<=-0.001,Sheet1!$M$2:$M$25000)))

Then, in A2 array entered and copied down:

=MIN(IF(Sheet1!$A$2:$A$25000=Sheet2!$A$2,IF(Sheet1 !$I$2:$I$25000<=-0.001,IF(Sheet1!$M$2:$M$25000A1,Sheet1!$M$2:$M$25 000))))

--
Biff
Microsoft Excel MVP


"Sorority Girl" <Sorority wrote in message
...
I've a formula,
{=SMALL(IF(Sheet1!$A$2:$A$25000=Sheet2!$A$2,IF(She et1!$I$2:$I$25000<=-0.001,Sheet1!$M$2:$M$25000)),11)}but
the 11th largest number repeats itself more than once. I tried this
formula,
=SMALL(IF(FREQUENCY(IF(Sheet1!$A$2:$A$25000=Sheet2 !$A$2,IF(Sheet1!$I$2:$I$25000<=-0.001,Sheet1!$L$2:$L$25000)),(IF(Sheet1!$A$2:$A$25 000=Sheet2!$A$2,IF(Sheet1!$I$2:$I$25000<=-0.001,Sheet1!$L$2:$L$25000)))0),(IF(Sheet1!$A$2:$ A$25000=Sheet2!$A$2,IF(Sheet1!$I$2:$I$25000<=-0.001,Sheet1!$L$2:$L$25000)))),11)but
it returned an #N/A.
I tried a different scenario using the formula
=SMALL(IF(FREQUENCY(Sheet1!$L2:$L25,Sheet1!$L2:$L2 5)0,Sheet1!$L2:$L25),9)
and it worked perfectly (once the range didn't have any blank cells). How
do
I rectify the problem, and make it work to exclude the blank cells.