There are 100 students and their grades are calculated by (example:
factor (5.9) x score (3). The factors range from 5.9 to 6.9 and the
score from 1-5. The factor could be based on analytical writing,
Quantative, Verbal, so on. There must be a way to calculate these
scores with a lookup function instead of typing in a formula like the
example above.

Another example: Quantative factor: 6.1, student score 4 (6.1x4).
There are 10 factors total and the percentages change for each.

Ideally, I would like to type in the score and excel calculate the
factor and give me the overall percentage for that test question.

Any help will be greatly appreciated

Dre

I don't know how your sheet is set up, but
On one sheet, set up a table with all the different factors, in Col A the
subject, and in B the relevant factor. Sort these alphabetically. Name the
range (say A1:B10) "Factors".
On another sheet, Say Student #s in A2 down, Student Surname in B2 down and
Student initials in C2 down. In D1, F1, H1 etc the names of the classes, eg
Verbal, Quantative, Analytical Writing etc. In D2, F2, H2 etc down the
actual scores - 1 to 5.
In E2, G2, I2 and down a formula to do the calculation
Start in E1 with
=IF(D2="","",VLOOKUP(D$1,Factors,2,0)*D2)
Copy this down as far as you want to go.
Now copy this to F2, H2 etc, and as far down as you want to go.
As you enter a score in D2, F2, H2 etc, the weighted score will appear in
E2, G2, I2 etc


"Dre" wrote:

There are 100 students and their grades are calculated by (example:
factor (5.9) x score (3). The factors range from 5.9 to 6.9 and the
score from 1-5. The factor could be based on analytical writing,
Quantative, Verbal, so on. There must be a way to calculate these
scores with a lookup function instead of typing in a formula like the
example above.

Another example: Quantative factor: 6.1, student score 4 (6.1x4).
There are 10 factors total and the percentages change for each.

Ideally, I would like to type in the score and excel calculate the
factor and give me the overall percentage for that test question.

Any help will be greatly appreciated

Dre

Thank you Kassie, I will try this. Also, below is a view of the sheet.
Let me know what you think.

A B C D
E F
L. Name F. Name Verbal Analytical
Writing, so on Overall Total

Loading Factor for Verbal 6.9
Loading Factor for Analytical 5.5
Loading Factor for Writing 7.0

The students earn a score between 1-5 in these areas. Example: Verbal
6.9X4 = 27.6
Then we total all scores.
The score will change for each student in each area and I don't want to
calculate each time.

Thank you again, I will let you know if this works.

Dre

kassie wrote:
I don't know how your sheet is set up, but
On one sheet, set up a table with all the different factors, in Col A the
subject, and in B the relevant factor. Sort these alphabetically. Name the
range (say A1:B10) "Factors".
On another sheet, Say Student #s in A2 down, Student Surname in B2 down and
Student initials in C2 down. In D1, F1, H1 etc the names of the classes, eg
Verbal, Quantative, Analytical Writing etc. In D2, F2, H2 etc down the
actual scores - 1 to 5.
In E2, G2, I2 and down a formula to do the calculation
Start in E1 with
=IF(D2="","",VLOOKUP(D$1,Factors,2,0)*D2)
Copy this down as far as you want to go.
Now copy this to F2, H2 etc, and as far down as you want to go.
As you enter a score in D2, F2, H2 etc, the weighted score will appear in
E2, G2, I2 etc


"Dre" wrote:

There are 100 students and their grades are calculated by (example:
factor (5.9) x score (3). The factors range from 5.9 to 6.9 and the
score from 1-5. The factor could be based on analytical writing,
Quantative, Verbal, so on. There must be a way to calculate these
scores with a lookup function instead of typing in a formula like the
example above.

Another example: Quantative factor: 6.1, student score 4 (6.1x4).
There are 10 factors total and the percentages change for each.

Ideally, I would like to type in the score and excel calculate the
factor and give me the overall percentage for that test question.

Any help will be greatly appreciated

Dre

My pleasure

"Dre" wrote:

Thank you Kassie, I will try this. Also, below is a view of the sheet.
Let me know what you think.

A B C D
E F
L. Name F. Name Verbal Analytical
Writing, so on Overall Total

Loading Factor for Verbal 6.9
Loading Factor for Analytical 5.5
Loading Factor for Writing 7.0

The students earn a score between 1-5 in these areas. Example: Verbal
6.9X4 = 27.6
Then we total all scores.
The score will change for each student in each area and I don't want to
calculate each time.

Thank you again, I will let you know if this works.

Dre

kassie wrote:
I don't know how your sheet is set up, but
On one sheet, set up a table with all the different factors, in Col A the
subject, and in B the relevant factor. Sort these alphabetically. Name the
range (say A1:B10) "Factors".
On another sheet, Say Student #s in A2 down, Student Surname in B2 down and
Student initials in C2 down. In D1, F1, H1 etc the names of the classes, eg
Verbal, Quantative, Analytical Writing etc. In D2, F2, H2 etc down the
actual scores - 1 to 5.
In E2, G2, I2 and down a formula to do the calculation
Start in E1 with
=IF(D2="","",VLOOKUP(D$1,Factors,2,0)*D2)
Copy this down as far as you want to go.
Now copy this to F2, H2 etc, and as far down as you want to go.
As you enter a score in D2, F2, H2 etc, the weighted score will appear in
E2, G2, I2 etc


"Dre" wrote:

There are 100 students and their grades are calculated by (example:
factor (5.9) x score (3). The factors range from 5.9 to 6.9 and the
score from 1-5. The factor could be based on analytical writing,
Quantative, Verbal, so on. There must be a way to calculate these
scores with a lookup function instead of typing in a formula like the
example above.

Another example: Quantative factor: 6.1, student score 4 (6.1x4).
There are 10 factors total and the percentages change for each.

Ideally, I would like to type in the score and excel calculate the
factor and give me the overall percentage for that test question.

Any help will be greatly appreciated

Dre