Home  |  About  | Last |  Submit  |  Contact
AllQuests.com



Previous Question:  Conditionally insert new row with contents of row above it  Excel QuestionsNext Question:  VBA Pivottable ChildItems not work  Excel Questions
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

Answers: grades computation ( MrExcel Message Board Excel Questions )
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

grades computation

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
=

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

grades computation

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
=

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

grades computation

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
=

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

Previous Question:  Conditionally insert new row with contents of row above it  MrExcel Message Board  Excel QuestionsNext Question:  VBA Pivottable ChildItems not work  MrExcel Message Board  Excel Questions

- 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