goal reward in excel

New Contributor

Greetings,

I am trying to do the following on the table below;

yannidakis_1-1673967402814.png

A student has two goals in each lesson. Goal one is a number (grade) and the other goal is a percentage.

A user can type in cells E3, and F3 the results of his grades.

1. If the student achieves the grade goal (in our example is 1000) then the user will be rewarded with 1000 points.

2. If the student achieves the percentage goal (in our example is 20%) then the user will be rewarded with 1000 points.

3. For every 1% plus the grade goal the reward will be increased by 200 points, until the limit of 5% where the reward will be another 1000 points (1000+1000=2000)

4. For every 1% minus the grade goal the reward will be decreased by 200 points, until the limit of 5% where there will not be any reward.

5. If the student scores on his percentage, 5% or more above the percentage goal, then the student will be rewarded by 1000 points.

6. If the student scores on his percentage, 5% or less below the percentage goal, then the student will not be rewarded by any points.

7. If the percentage goal or the grade goal is not achieved then the student will not get any reward.

 

Thanks for your help!

1 Reply

@yannidakis 

In H3: =MIN(MAX(1000+200*(E3-B3)/100,0),2000)

In I3: =MIN(MAX(1000+200*(F3-C3)*100,0),2000)

 

See the attached demo