SMALL IF

[Adam]

Hi

In successive rows I need to find the X Smallest of a dataset given another
dataset = a specific number.

Return the first smallest score for competitors in team 1
Return the second smallest score for competitors in team 1

I have used the following function to find the Largest

=SUMPRODUCT(LARGE(('Data Input'!C$3:C$126=1)*'Data Input'!G$3:G$126,1))

But for SMALL I need to omit zeros.

I have tried different variations of

=SUMPRODUCT(IF('Data Input'!K$3:K$126<>0,(SMALL('Data
Input'!K$3:K$126,2)*'Data Input'!C$3:C$126=1)," "))

but can't seem to get it to work.

Any ideas would be gratefully received.

 

[Mike H]

Adam,

Try this array formula. See below on how to enter an array formula

=MIN(IF(C3:C126=1,IF(G3:G126>0,G3:G126)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

 

[Adam]

That is absolutely amazing Mike.

Do you know if there is a way to specify the second minimum, Third minimum
etc?

Apologies if I am being stupid.

Thanks. Adam

 

[Jacob Skaria]

Hi Adam

Again array formulas....

2nd min
=SMALL(IF(C3:C126=1,IF(G3:G126>0,G3:G126)),2)

3rd min
=SMALL(IF(C3:C126=1,IF(G3:G126>0,G3:G126)),3)

If this post helps click Yes

 

[Adam]

ABSOLUTE HERO!

Cheers. Adam