Lneal

I am stumped, can anyone help me? I am doing an assignment that requires me to find the weighted average of a students four exams. I am asked to use absolute cell references. When I enter the formula for one student I get the correct answer according to the book. However I am asked next to use autofill to complete the other students grades and that's my problem. It does not work.

I am in college and don't know what I am doing wrong since excel is very new to me. How do I find out what I am doing wrong? Can anyone help this student?

Ilovemydogs

I use excel a lot. I will try and help. I PM'd you

raedar63

We are in the same boat, I am in statistics right now and if I only knew how to work excel it would be sooooo much easier.

Is that bridge near Lynchburg?

patchsamkim

Hope someone was able to help you...excel was very hard for me to learn about and I really haven't used it since I took an Office class. Good luck!

jljack

You use a $ to do an absolute cell reference...ie $B$7.

MTS

So this is more an Excel question than a math one, right?

Are you trying to copy the formulas to another location, and that's what's not working?

Without seeing it or more information, I'd guess you're having ABSolute issues. So this may be relevant. Or it may not be. :mrgreen:

When you use absolutes, you're attempting to lock in both or one of a cell's column or row location.

So if the formula in cell D10 is =$A$1, no matter where you copy or move it to on any other cell on your spreadsheet, it will always look for the value in cell A1.

Now, if the formula was =$A1, and you copied if from cell10
to, say, cell D11, it would still be locked in to column A, but would now be looking at the value one down (since D11 is located one cell down from D10).

Same goes for =A$1. If you copied that formula from cell D10 to cell D11, it would still look at the contents of cell A1. However, if you copied it to cell E10, then it would look at the value in cell B1.

Now, you can change each formula manually. But that's not fun or efficient.

When you set the initial formulas to figure out whatever it - totals/weighted avgs/%, - look at what would remain constant from student to student - would it be the info in a column or a row? And then lock that in with a $.

When you hit F4 to make an cell ABS in your formula, it will do a double $x$1 first. Hit F4 again, and it locks only the row. Hit it again and it locks only the column. Once more, and it unlocks it all back to x1.

It takes a little getting used to, but if you've figured out what the difference is between COPY and MOVE, then it should make sense.

Good luck!

Lneal

=$B17*$C8+$C9*$C17+$C10*$D17+$C11*$E17

This is the formula that I entered using absolute cell reference. Have I entered it wrong? Thanks for your help.

MTS

can you post a picture of the data?

And you need () around some of the formulas to break it up.

qbquilts

The values that have the weights should have two $. So say that the one value (b17, c17, d17, e17) is the student's grade and the other value (c8, c9, c10, and c11) is the weight, the weight will be the same for each student. It should be:

=$C$8*$B17+$C$9*$C17+$C$10*$D17+$C$11*$E17

(BTW - I reversed the values in the first two so that they are consistent).

Lneal

Chemistry 303
First Semester Scores
Posted 12/20/2010

Students

Class Summary Exam Weight Median Maximum Minimum
Exam 1 20%
Exam 2 20%
Exam 3 20%
Final Exam 40%
Overall 100%


Student Scores Top Ten Overall Scores
Student ID Exam 1 Exam 2 Exam 3 Final Exam Overall
390-120-2 84.0 80.0 83.0 72.0
390-267-4 98.0 92.0 91.0 99.0
390-299-8 54.0 56.0 51.0 65.0
390-354-3 98.0 95.0 90.0 94.0
390-423-5 83.0 83.0 74.0 77.0
390-433-8 52.0 63.0 58.0 53.0
390-452-0 97.0 98.0 93.0 91.0
390-485-7 87.0 77.0 83.0 87.0
390-648-6 94.0 91.0 92.0 97.0
390-699-6 74.0 75.0 50.0 64.0
391-260-8 96.0 84.0 95.0 96.0
391-273-8 73.0 75.0 78.0 74.0
391-315-1 89.0 89.0 73.0 82.0
391-373-1 99.0 94.0 85.0 93.0
391-383-6 92.0 93.0 96.0 80.0
391-500-8 81.0 88.0 78.0 88.0
391-642-7 72.0 80.0 83.0 86.0


I need to find the weighted average of the first students four exams and then use auto fill to complete the remainder.