IF formula question

[nieuwkrijt]

Hi,

I am a teacher from the Netherlands and I would like to create a tool that lets Excel enter a grade (double digit number, i.e 6.7) in a cell next to a pupils name based on information in another cell in another worksheet.

This is what I have:

Multiple worksheets:
Worksheet 1 = contains parameters that lets me set total points, and ...%=6.0 (sufficient grade)
Worksheet 2 = graph with scores and columns with ...points = ...score

What I want is:
Worksheet 3:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name pupil[/TD]
[TD]Points[/TD]
[TD]Grade[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Pupil 1[/TD]
[TD]45[/TD]
[TD]8.9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Pupil 2[/TD]
[TD]This information is manually entered by me.[/TD]
[TD]This information should be generated based on a command. Namely: C3=the score that result from B3's amount of points (i.e. 40) which can be found in another worksheet with the scores.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Pupil 3[/TD]
[TD]This information is manually entered by me.[/TD]
[TD]This information should be generated based on a command. Namely: C4=the score that result from B4's amount of points (i.e. 40) which can be found in another worksheet with the scores.[/TD]
[TD]


[/TD]
[/TR]
</tbody>[/TABLE]


Here are some screenshots to clarify:

https://imgur.com/5ggEnZI
https://imgur.com/ZZlv4m3
https://imgur.com/tFNAJQG

Thanks so much in advance!

Dennis

 

[nieuwkrijt]

Oh, and a possible problem could be that the location of each score in the worksheet (with the points = score) can differ depending on the total amount of points that can be earned; 40 is in cell A12 if the total amount of points is 50 but in cell A22 if the total amount of points is 60. If you know how to have excel calculate the grade based on an algorithm without consulting the already existing score board that's fine (or even better).

Tah

 

[GWteB]

Hi Dennis,

Welcome to the board. What you're looking for is the Vlookup-formula. Watch this video for more details... https://support.office.com/en-us/article/VLOOKUP-function-0BBC8083-26FE-4963-8AB8-93A18AD188A1

In the Dutch version of Excel this formula is called VERT.ZOEKEN


Kind regards