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. |
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. |
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 |
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. |
All times are GMT +1. The time now is 08:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com