Small If with <=

[fillqb12]

I'm trying to run a Small If formula with the criteria of numbers in a certain column being below a particular number. The formula will not work with <= but it will work with a >=.


The formula I'm running is {=SMALL(IF($E$6:$E$132<=$C$1,$Q$6:$Q$132),1)} and for some reason it works if the criteria is >= but will not work with the criteria <=. I'm kind of at a loss...

[mikeava]

Hi - can you please attach your file so we can take a look?

[fillqb12]

Here is a sample file. I'm trying to find the lowest IRRs and highest IRRs (column E) given the year of the fund (Column C). The formulas are in columns M and P--and it works fine in Column P when my criteria is greater than a certain year, but returns an error when I the criteria is less than that year.

Thank you!

[Gregb11]

In column C, select all them all and then click on the icon next to the top year and choose "Convert to Number". It should then work.
I've done it to the attached.

[josephteh]

One possible setback of using formulas is: ff 2 or more funds with the lowest 5 IRR have exactly the same IRR, then duplicate fund names would be displayed.

Use PivotTable for best results. It will also give you the flexibility of choosing the vintage year(s) to display and Top whatever highest/lowest IRR.

[fillqb12]

Thank you everyone, appreciate the help. For some reason it was showing as a number format in my file, but clearly it wasn't. I converted the formula for the Vintage column (which pulls from an external source) to a VALUE, and it worked.