formula reference~muliple sheets

G

Guest

Hi, I need to generate a formula that will look at the data on sheet
one..reference a list on sheet 2 ..do some arithmatic then add the total of 3
cells together placing that total in its own cell.

sheet 1

Student ID Exam 1 Exam 2 Final Overall Grade
318-84-6039 100 93 79 87.40 B
332-03-6854 99 90 74 F
341-38-6902 63 51 43 F
362-80-6830 65 91 81 F
351-25-6606 74 65 58 F
303-86-6698 74 63 63 F
360-99-6115 63 50 43 F
342-45-6149 84 72 65 F
360-49-6615 78 65 58 F
331-38-6683 88 78 77 F
337-55-6535 72 64 62 F
300-60-6949 100 89 85 F
373-46-6412 99 88 84 F
397-44-6180 98 84 84 F
398-56-6736 100 92 86 F

sheet 2

Exam Type Weight
Exam 1 25%
Exam 2 25%
Final 50%

using these charts, i need to generate the data that would populate under
the overall column. What i need to do is reference the type, multiply the
value in the cell that corresponds to it by the appropriate weight which is
found on sheet 2 in the list. add the 3 cells together after multiplying them
by their weights and put that total in the column under overall.

ex 100*20%+93*30%+79*50% 87.40 would go under overall.

anyhelp on this would be appreciated
~Eelinla
 
D

D.

Hi, I need to generate a formula that will look at the data on sheet
one..reference a list on sheet 2 ..do some arithmatic then add the total of 3
cells together placing that total in its own cell.

sheet 1

Student ID Exam 1 Exam 2 Final Overall Grade
318-84-6039 100 93 79 87.40 B
332-03-6854 99 90 74 F
341-38-6902 63 51 43 F
362-80-6830 65 91 81 F
351-25-6606 74 65 58 F
303-86-6698 74 63 63 F
360-99-6115 63 50 43 F
342-45-6149 84 72 65 F
360-49-6615 78 65 58 F
331-38-6683 88 78 77 F
337-55-6535 72 64 62 F
300-60-6949 100 89 85 F
373-46-6412 99 88 84 F
397-44-6180 98 84 84 F
398-56-6736 100 92 86 F

sheet 2

Exam Type Weight
Exam 1 25%
Exam 2 25%
Final 50%

using these charts, i need to generate the data that would populate under
the overall column. What i need to do is reference the type, multiply the
value in the cell that corresponds to it by the appropriate weight which is
found on sheet 2 in the list. add the 3 cells together after multiplying them
by their weights and put that total in the column under overall.

ex 100*20%+93*30%+79*50% 87.40 would go under overall.

anyhelp on this would be appreciated
~Eelinla
Click to expand...

Hi there,
Place this formula inB1 sheet 2 and type in the student # in A1

See if it works for you

=(VLOOKUP(A1,Sheet1!$A$2:$F$16,2,0)*0.2)+(VLOOKUP(A1,Sheet1!$A$2:$F
$16,3,0)*0.3)+(VLOOKUP(A1,Sheet1!$A$2:$F$16,4,0)*0.5)
 
G

Guest

maybe im misunderstanding what your saying, but reentering the student
numbers is going to require more work than i want if at all avoidable. Also i
need the information to be able to be absolute so if the data changes in teh
look up sheet it can be adjusted across all 5 sheets by just adjusting the
list data.
 
G

Guest

there are multiple sheets with the same type of data on each.. if there are 6
classes there will be 6 sheets. If i have to input the student ID numbers
into a list for each it will take quite a bit of time.(lets say sheets 2-7
are class sheets and sheet one has the reference list)

i have a list similar to the one i posted already. i need to make a formula
that will allow me to multiple the test scores indiviually by the proper
%(which is based on the type and can be found in the list on Sheet 1) once i
get that amount i need to add the total of the 3 scores together and put them
into their own cell (in this case in the overall column) no matter how i try
i keep getting different errors. this needs to be able to carry over to all
sheets and copied down the entire column.
 
G

Guest

Looking at your original post ..

In Sheet1,
Col E = Overall

Put in E2
=SUM(B2*VLOOKUP(B$1,Sheet2!A:B,2,0),C2*VLOOKUP(C$1,Sheet2!A:B,2,0),D2*VLOOKUP(D$1,Sheet2!A:B,2,0))
Copy E2 down

---
 
G

Guest

An alternative, perhaps simpler in this instance, is to use defined ranges

In Sheet2, within A2:B4 are your weights:

Exam 1 25%
Exam 2 25%
Final 50%

Select A2:B4, then click Insert > Name > Create
"Left column" will be checked. Just click OK.

The above will auto-create 3 defined ranges:
Exam_1 =Sheet2!$B$2
Exam_2 =Sheet2!$B$3
Final =Sheet2!$B$4

Then in Sheet1,
Col E = Overall

Put in E2:
=SUM(B2*Exam_1,C2*Exam_2,D2*Final)
Copy E2 down.

You could use the same formula above
in all other sheets similar to Sheet1 within the book.

---
 
G

Guest

Thanks. defining the range seems to have made the difference. Will be
something I think about more after working thru this issue.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VLookup 8
grading 2
Match & Vlookup Function 1
Validation 3
Rearrange numbers from a column in a different order in a second c 14
large conditional "if" query 5
Finding a number in a table? 10
Looking for a function to use ... 8

Top