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


"kaveran" wrote:

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.