text results based on score and also changing the score

[lezawang]

Hi
I want to provide text result based on students score but also I want to modify the score and add bonus based on the score and put the new result in "new score" column. The conditions are the following

[FONT=&quot]If the score is >20 = very good ( they get a 5% performance bonus)[/FONT]
[FONT=&quot]If the score is 16-20 = good ( they get a 3% performance bonus)[/FONT]
[FONT=&quot]If the score is 15 and below = needs improvement ( no bonus)

name	score	result	new score
mary	22
john	17
linda	11

<tbody>
</tbody>

I am thinking of If() and maybe AND() and OR() and another formula for the "New score" column. Any help would be very much appreciated.[/FONT]

 

[James006]

Hi,

In cell C2 you could have : =LOOKUP(B2,{0,16,20},{"needs improvement","good","very good"})

and in cell D2 this formula : =IF(C2="very good",B2*1.05,IF(C2="good",B2*1.03,B2))

Hope this will help

 

[oldbrewer]

I want to provide text result based on students score but also I want to modify the score and add bonus based on the score and put the new result in "new score" column. The conditions are the following

If the score is >20 = very good ( they get a 5% performance bonus)

If the score is 16-20 = good ( they get a 3% performance bonus)

mytable

If the score is 15 and below = needs improvement ( no bonus)

name

score

result

new score

0

needs improvement

mary

22

very good

23.1

15

good

john

17

good

17.51

21

very good

linda

11

needs improvement

11

99

very good

very good obtained by

=VLOOKUP(B8,mytable,2)

23.1 obtained by

=IF(B8>20,B8*1.05,IF(B8<=15,B8,B8*1.03))

<colgroup><col span="2"><col><col span="13"></colgroup><tbody>
</tbody>

 

[Logit]

Deleted

 

[Logit]

.
Another suggestion:

In C2 and copied down: =IF(B2>=20,"Very Good",IF(AND(B2>=16,B2<20),"Good",IF(B2<16,"Needs Improvement","")))

In D2 and copied down: =IF(C2="very good",B2*1.05,IF(C2="good",B2*1.03,B2))

 

[MB110]

I had come up with Logit's suggestion. Only difference I would consider is creating another table to reference the text and bonus value, giving you control over the performance thresholds and bonus amounts rather than hard coding to the formulas.

HTH,
MB110

 

[oldbrewer]

...which is what mine does..........