This is a discussion on grades computation within the Excel Questions forums, part of the Question Forums category; how can i make a worksheet/formula that automatically look for the equivalent score in a given transmutation table. example: i ...

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

2. 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

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?

4. bad format on the screen..... how can i have it view properly. (columns and rows)?

5. 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?

6. 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

7. 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.

8. 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]

9. 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?

10. 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

Page 1 of 3 123 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•