Newbie Question

[Ben Sharvy]

I can't figure out how to do something I think is simple.

I have a list of student names, and a list of test scores.


Student Name Test#1 Test#2 etc.... GRADE
Doe, Johnny
Smith, Betty
etc.

B2 would be the score Johnny got on Test#1, etc.

I want to calculate the grade for each student, using a formula for
the test scores (it's not a simple sum).

I can make the forumula, and get a grade by putting the formula in
each cell at the end of the student row. But I have to put it in each
cell, adding it each time I add a student, and changing the cell
references.

Isn't there a simple way to apply the same formula to a series of rows?

 

[Pete_UK]

Yes, there is.

If you can post your formula for row2 then we can advise you on how to
change it to copy it down.

Pete

 

[Ben Sharvy]

=SUM(B2,C2)/SUM(5/D2,5/E2)

Where columns B,C, D, E are scores in the columns.

 

[MartinW]

Hi Ben,

You can just drag your formula down the sheet and the
references will update automatically.

Click on the cell that contains your formula and hover the
cursor over the little box at the bottom right corner of the cell,
your cursor will change to a small cross, just click and drag
it down the sheet for say 10 cells then let go the left button
and have a look at the result.

If you don't see the little box in the corner (called the fill handle),
then go to Tools>Options>Edit Tab and check the box
marked allow cell drag and drop.

HTH
Martin

 

[MartinW]

You may also wish to write your formula like this
=IF(B2="","",SUM(B2,C2)/SUM(5/D2,5/E2))
and drag it past the end of your data.

The cell will stay blank until you enter data in column B

A few similar options are
=IF(AND(B2="",C2=""),"",SUM(B2,C2)/SUM(5/D2,5/E2))
this will stay blank until there is data in B and C

=IF(COUNT(B2:E2)=4,"",SUM(B2,C2)/SUM(5/D2,5/E2))
this will stay blank until B C D and E have data in them.

HTH
Martin

 

[Ben Sharvy]

Thank you for the help.

 

[MartinW]

You're welcome Ben. Thanks for the feedback.