Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if condition combined with SMALL
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if condition combined with SMALL
=if(RANK(C1,$C$1:$C$10,1)<5,2.5-RANK(C1,$C$1:$C$10,1)/2,0)
"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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if condition combined with SMALL
On Wed, 22 Jul 2009 09:30:51 -0700 (PDT), 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. 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
if condition combined with SMALL
=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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
More on Combined IF Function | Excel Worksheet Functions | |||
If combined with OR & AND | Excel Discussion (Misc queries) | |||
lookup with multiple condition, but one condition to satisfy is en | Excel Worksheet Functions | |||
ISERROR,SMALL,INDEX, MATCH, SMALL?? | Excel Discussion (Misc queries) | |||
Combined Cells | Excel Worksheet Functions |