SMALL IF
 

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.

SMALL IF
 

Adam,

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

=MIN(IF(C3:C126=1,IF(G3:G1260,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" wrote:

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.

SMALL IF
 

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

SMALL IF
 

Hi Adam

Again array formulas....

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

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

If this post helps click Yes
---------------
Jacob Skaria


"Adam" wrote:

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

SMALL IF
 

ABSOLUTE HERO!

Cheers. Adam