if condition combined with SMALL

[kaveran]

I have a column of values c1 through c10. In d1 through d20 I would
like to display a value based on the values contained in c1 through
c10. The kicker is that I want to only place a value in the D column
for the 4 lowest values in the C column

Example...

C1 = 43
C2 = 35
C3 = 67
C4 = 74
C5 = 63

in D1 I would like to basically do the following evaluation,,,,

IF C1 is the smallest value in the C column D1 should have a value of
2
IF C1 is the second smallest value in the C column D1 should have a
value of 1.5
IF C1 is the third smallest value in the C column D1 should have a
value of 1
IF C1 is the fourth smallest value in the C column D1 should have a
value of .5

Im aware of small(C1:C10,1) to get the smallest but I am having
trouble incorporating the IF in there with it.

Does anyone know of a tutorial or anything they can point me to that
covers this?

Thanks.

 

[Sam Wilson]

=if(RANK(C1,$C$1:$C$10,1)<5,2.5-RANK(C1,$C$1:$C$10,1)/2,0)

 

[Lars-Åke Aspelin]

I have a column of values c1 through c10. In d1 through d20 I would
like to display a value based on the values contained in c1 through
c10. The kicker is that I want to only place a value in the D column
for the 4 lowest values in the C column

Example...

C1 = 43
C2 = 35
C3 = 67
C4 = 74
C5 = 63

in D1 I would like to basically do the following evaluation,,,,

IF C1 is the smallest value in the C column D1 should have a value of
2
IF C1 is the second smallest value in the C column D1 should have a
value of 1.5
IF C1 is the third smallest value in the C column D1 should have a
value of 1
IF C1 is the fourth smallest value in the C column D1 should have a
value of .5

Im aware of small(C1:C10,1) to get the smallest but I am having
trouble incorporating the IF in there with it.

Does anyone know of a tutorial or anything they can point me to that
covers this?

Thanks.

If you know that the numbers in column C are all different, then you
may try this formula:

=IF(C1<SMALL(C$1:C$5,5),SUMPRODUCT(--(C1=SMALL(C$1:C$5,{1;2;3;4})),{2;1.5;1;0.5}),"")

Hope this helps / Lars-Åke

 

[Teethless mama]

=MAX(0,(5-RANK(C1,$C$1:$C$10,1))/2)