Home  |  About  | Last |  Submit  |  Contact

 Tweet

 Question grades computation ( MrExcel Message Board Excel Questions )Updated: 2010-08-17 14:15:11 (18)
 grades computation 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
 grades computation nope! the result should be in the columns for equivalent. say if the quiz score is 10 then the equivalent should be 100. the transmutation table look like this (for quiz of 10 items) score equivalent 9-10 100 8 95 7 90 6 85 4-5 80 1-3 70 kinalas
 grades computation thanks! the values you see in the equivalent columns are the sample desired output based on a transmutation table or range of raw scores for example: if the quiz score is 9-10 the equivalent is 100... and so on.. so i will be having multiple tables and equivalents depending on the number of items of the quizzes. dont know if can combine combining vlookup with @if but theres a limit on the formula (@if(no.of items=10,vlookup(??????? i think i still have to enter the number of items maybe above the entry of the quizzes to serve as the reference of vlookup.. :idea: [/list] kinalas

Hi kinalas:

If I have understood you correctly, the following simulation should be of interest to you ...

******** ******************** ************************************************************************>
 Microsoft Excel - Book2 ___Running: xl97 : OS = Windows 98
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 G2 =

A
B
C
D
E
F
G
1
namescorequiz1equivalentscorequiz2equivalent*9.1
2
ccc5703080*100
3
bbb9905598**
4
aaa1010050100**
 Sheet1 *

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

if cell G1 houses 9.1, then the formula in cell G2 is ...

=INDEX(C1:C4,IF(INDEX(B1:B4,MATCH(G1,B1:B4))
Does it help?

Yogi Anand

 grades computation thanks yogi i'm actually trying to figure out how to get the equivalent without having to manually look at the transmutation table i guess i have to use vlookup but dont figure out how to enter the right formula kinalas
 grades computation Hi Kinalas: If the table above correctly depicts your data, can you clearly describe what is it that you are trying to accomplish. The VLOOKUP function uses a lookup value that must be in the first field of the lookup table, and then finds the value of interest from a specified field corresponding to a row that the lookup value lies in or does not exceed. It is easier to use the VLOOKUP function than it is to describe. So please post back with a clear description of what you are trying to accomplish -- and then let us take it from there. Yogi Anand
 grades computation thanks paddy lets say ``` a b c d e name score quiz 1 equivalent score quiz 2 equivalent (10) (50) aaa 10 100 50 100 bbb 9 90 55 98 ccc 5 70 30 80``` note: scores on quiz and quiz 2 have different no of items, 10 & 50 respectively the transmutation tables can be anywhere within the worksheet. or can i place it in a different worksheet? kinalas
 grades computation bad format on the screen..... how can i have it view properly. (columns and rows)? kinalas

Hi kinalas:

This is how your table looks like ...

******** ******************** ************************************************************************>
 Microsoft Excel - Book2 ___Running: xl97 : OS = Windows 98
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 A1 =

A
B
C
D
E
1
namescorequiz1equivalentscorequiz2equivalent
2
aaa1010050100
3
bbb9905598
4
ccc5703080
 Sheet2 *

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Now in spreadsheet terminology, what are you trying to lookup, match, index, or compute?

Yogi Anand

 grades computation HI - welcome to the board. vlookup() might be what you need. care to post back with a few more details about where your data is, what vvalue(s) you're looking up...an example would help paddy PaddyD
 grades computation 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. Yogi Anand
 grades computation 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. Pionneeer
 grades computation 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. PaddyD
 grades computation 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! kakaka
 grades computation 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 aironet
 grades computation 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 kinalas
 grades computation 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! Yogi Anand
 grades computation 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 kinalas

Hi kinalas:

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

******** ******************** ************************************************************************>
 Microsoft Excel - y030326h1.xls ___Running: xl97 : OS = Windows 98
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 E2 =

A
B
C
D
E
F
G
H
I
1
*9-10100*9-10**cells*formated*as*text*before*making*entries*
2
*895*100**result*
3
*790******
4
*685******
5
*4-580******
6
*1-370******
7
*********
 Sheet2 *

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Yogi Anand

 - Source: grades computation MrExcel Message Board Excel Questions- Previous Question: Conditionally insert new row with contents of row above it MrExcel Message Board Excel Questions- Next Question: VBA Pivottable ChildItems not work MrExcel Message Board Excel Questions