Member Avatar for lagmywagon

Hey, Im not sure if this is the right place to post this, but I need some help.

I have 14 classes of 40 students each and I need to make a grade book with excel.

Here's what I need help with:
I'm adding grades to column G, in column H I've entered the value of 2. I need to add 2 points to each grade from column G so that G+H=I. Then, I need to find the average for each student. Is there a way to do this with out having to type "=sum(g3,h3), =sum(g4,h4), =AVERAGE(G2,I2) etc..." individually?

I realize the example I posted is easy math and I can quickly add that in my head. For some classes its not as easy and there are lots of students to do this for. I want to just plug grades in for each class and come up with averages quickly.

  • 4 Contributors
  • 5 Replies
  • 163 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by MidiMagic

Recommended Answers

All 5 Replies

Member Avatar for chuckc

Maybe you are already aware of this, but if you highlight a section of spreadsheet (with your mouse) then type Ctrl-D, Excel will fill in all cells below the top cell, or row, of the highlighted section with adjusted formulas.
For example, if you enter "=Average(B3:F3)" in G3, then highlight G3 thru G20 and type Ctrl-D all the cells in that section will be adjusted, with G20 containing "=Average(B20:F20)". You can do the same thing with copy and paste using the Ctrl-C and Ctrl-V keys.
If you need to use one particular cell in all of the rows (e.g. H2), enter that as "$H$2" and that entry will remain unchanged when copied.
Ctrl-R is similar to Ctrl-D but copies and adjusts formulas to the right.
Excel really does have a lot of very powerful features; these are just the beginning of what can be done. Hope this helps.

Member Avatar for lagmywagon

Maybe you are already aware of this, but if you highlight a section of spreadsheet (with your mouse) then type Ctrl-D, Excel will fill in all cells below the top cell, or row, of the highlighted section with adjusted formulas.
For example, if you enter "=Average(B3:F3)" in G3, then highlight G3 thru G20 and type Ctrl-D all the cells in that section will be adjusted, with G20 containing "=Average(B20:F20)". You can do the same thing with copy and paste using the Ctrl-C and Ctrl-V keys.
If you need to use one particular cell in all of the rows (e.g. H2), enter that as "$H$2" and that entry will remain unchanged when copied.
Ctrl-R is similar to Ctrl-D but copies and adjusts formulas to the right.
Excel really does have a lot of very powerful features; these are just the beginning of what can be done. Hope this helps.

Perfect. Thank you, that is very helpful. As a teacher, this program is very helpful keeping records but I have limited experience with it. I have used some very intense grading templates in the past, but now I have to make my own and it is kinda tough. Do you know where I can find a cheat sheet with that kind of info?

Thanks again.

Member Avatar for chuckc

Hi again,
Glad I was able help. I don't consider myself an expert on Excel, but what I have learned is from using Excel, digging through books, asking friends, and often doing what you did - going online. Some of the control+key functions are indicated on the menu lists.

> Do you know where I can find a cheat sheet with that kind of info?

Not really. However I have found the Microsoft Excel97 Field Guide by Stephen Nelson to be helpful. Don't know if it has been updated, but most of the content is still good. I found my copy on Amazon.com and have included a link below if you are interested.
Good Luck!

http://www.amazon.com/Microsoft-Excel-97-Field-Guide/dp/0735610606

Member Avatar for MidiMagic

Use the fill handle.

Once you have typed in a formula, you can select it (so the black outline is around it).

In the lower right corner of the black outline is a little black square. This is the fill handle. The mouse pointer becomes a black cross when it is over the fill handle (as opposed to the white selecting cross or the 4-arrow move pointer).

Grab the fill handle and drag it in the direction you wish to copy the formula for as many rows as you want. The formulas will be copied to the range you select when you drag.

The absolute and relative addressing modes will be preserved, addressing the cells as you intended.

Note that if the cell includes the name of a month, a month abbreviation, a quarter, a day of the week, or a day of the week abbreviation, the cells the fill handle copied to will increment those, making a list of successive months, quarters, or days of the week.

mon fills to

mon tue wed thu

1 qtr fills to

1 qtr 2 qtr 3 qtr 4 qtr

jan fills to

jan feb mar apr may

If you select two numbered cells with different numbers in them, and drag the fill handle in the same direction the cells are lined up in, it will create a series. For instance, if I start with these cells selected:

1 2

It will fill in the series if I drag the fill handle to the right 6 more places:

1 2 3 4 5 6 7 8

If the cells are:

6 12

It will fill in:

6 12 18 24 30 36 42 48

Edited by MidiMagic because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.