assigning score

[jiggler]

I want to rank and assign scroes for a high school rodeo. 10 points pe
contestant in each event, lowest time wins. First place gets 19% o
total, 2nd- 17%, 3rd -15%, 13, 11, 9, 7 down to tenth place, 11th an
below get 0. A1 is the total score, A2 - A14 are the times, B2 - B1
are the rankings (reversed). The formula below gives me blank cells. I
i use < or > I get the right answer along with 5 wrong answers. What a
I doing wrong?

=IF(B2="1",0.19*A1,"")&IF(B2="2",0.17*A1,"")&IF(B2="3",0.15*A1,"")&IF(B2="4",0.13*A1,"")&IF(B2="5",0.11*A1,"")&IF(B2="6",0.09*A1,"")&IF(B2="7",0.07*A1,"")&IF(B2="8",0.05*A1,"")&IF(B2="9",0.03*A1,"")&IF(B2="10",0.01*A1,"")&IF(B2="0",0*A1)


Mat

 

[Bob Phillips]

Hi Mat,

Is this any good?

=VLOOKUP(B2,{1,0.19;2,0.17;3,0.15;4,0.13;5,0.11;6,0.09;7,0.07;8,0.05;9,0.03;
10,0.01},2,FALSE)*A2

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[J.E. McGimpsey]

One way:

=MAX(0, 0.21-0.02*B2)*$A$1

 

[Don Guillett]

try

=IF(b2>11,0,(21-b2*2)*0.01*A1)

 

[Don Guillett]

should be

=IF(b2>11,0,(21-b2*2)*0.01*A1)

=IF(b2>=11,0,(21-b2*2)*0.01*A1)

but just saw J.E.'s which is the one to use.

--
Don Guillett
SalesAid Software
(e-mail address removed)

try

=IF(b2>11,0,(21-b2*2)*0.01*A1)
--
Don Guillett
SalesAid Software
(e-mail address removed)

I want to rank and assign scroes for a high school rodeo. 10 points per
contestant in each event, lowest time wins. First place gets 19% of
total, 2nd- 17%, 3rd -15%, 13, 11, 9, 7 down to tenth place, 11th and
below get 0. A1 is the total score, A2 - A14 are the times, B2 - B14
are the rankings (reversed). The formula below gives me blank cells. If
i use < or > I get the right answer along with 5 wrong answers. What am
I doing wrong?

=IF(B2="1",0.19*A1,"")&IF(B2="2",0.17*A1,"")&IF(B2="3",0.15*A1,"")&IF(B2="4",0.13*A1,"")&IF(B2="5",0.11*A1,"")&IF(B2="6",0.09*A1,"")&IF(B2="7",0.07*A1,"")&IF(B2="8",0.05*A1,"")&IF(B2="9",0.03*A1,"")&IF(B2="10",0.01*A1,"")&IF(B2=

 

[jiggler]

Worked great!! Thanks so much!!

Matt

*should be

=IF(b2>11,0,(21-b2*2)*0.01*A1)

=IF(b2>=11,0,(21-b2*2)*0.01*A1)

but just saw J.E.'s which is the one to use.