grades computation

[kinalas]

how can i make a worksheet/formula that automatically look for the equivalent score in a given transmutation table.

example: i have an exam of 10 items, what will be my formula to so that it will automatically look for the equivalent score under the transmutation table of 10 items

 

[Yogi Anand]

Hi kinalas:

Please look at the following simulation, which uses the VLOOKUP function to get the needed results ...

y030326h1.xls
	A	B	C	D	E	F	G	H	I
1		9-10	100		9-10			cellsformatedastextbeforemakingentries
2		8	95		100			result
3		7	90
4		6	85
5		4-5	80
6		1-3	70
7
Sheet2

 

[kinalas]

thanks yogi!

that is actually what i'm doing.. but now problem is how can i have a single formula that will automatically look for the right table if the number of items is diff.

say if the number of items is 100, then the vlookup function should look for the right table/equivalent

 

[Yogi Anand]

Hi kinalas:

I see a few possibilities (perhaps there are other solutions as well) ...

1. Create a Lookup Table as in columns B and C to be used in the VLOOKUP formula

2. Develop a relationship between the score and grade that can be formulated as a single formula (one can handle a few exceptions with an IF function or a series of nested IF functions)

3. Make use of the CHOOSE function

But before doing anything else, you must have all your data clearly laid out and the relationship between score and grade clearly delineated -- it is this data that would suggest the use of the right formulation to be used. Good Luck!

 

[kinalas]

thanks!

i tried combining vlookup with @if but the problem is the limitation of length (no of characters) of the formula. i have about 8 tables for vlookup

any suggestions

 

[Yogi Anand]

Hi kinalas:

It is hard to visualizewhat you are working with. If you want to email me the related part of your file, I will glad to look at it and get back to you. I have sent you a PM with my email address in it.

 

[Pionneeer]

hi kinalas,
this thread is 6 years old. anyway, i hope i can still find answers. if anyone can help me. i teach in university where the passing mark is 75%. For any given exam/quiz/semester a score of 65 is equivalent to 75% or "pass". but at times my students fail to even reach that mark. but i cannot fail everone in my class. in other words, i have to "adjust". After i decide on a "passing mark" i have difficulty transmuting grades unless i have a tranmutation table which i can use for ANY value i put in as passing. I teach in the medical field. Anything that has to do with numbers boggle me. Please help.

 

[PaddyD]

hi pioneeer - welcome to the board.

a) it is more usual, and generally a quicker way to an answer - to start a new thread for a new issue - if another is relevant, just include a link

b) given a population of grade in, eg, a1:a100, then:

=quartile(a1:a100,3)

is the 3rd quartile number, i.e. number that 25% of the population score above.

 

[kakaka]

the transmutation for 10 items is like this

10 - 100
9 - 95
8 - 90
7 - 85
6 - 80
5 - 75
4 - 74
3 - 73
2 - 72
1 - 71
0 - 70

i wish i help you!

 

[aironet]

this also my problem, i making a grading system where theres a table where total scores correspond with a equivalent percentage but will depend on total of items of quiz, http://i38.tinypic.com/v4ck0k.jpg

 

[joey21jns]

hi i am currently having the same problem right now i know this is a very old thread i hope someone can help me do it