S
Sorority Girl
I’ve a formula,
{=SMALL(IF(Sheet1!$A$2:$A$25000=Sheet2!$A$2,IF(Sheet1!$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$25000=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:$L25)>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.
{=SMALL(IF(Sheet1!$A$2:$A$25000=Sheet2!$A$2,IF(Sheet1!$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$25000=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:$L25)>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.