# Mass-editing formulas?

1. ## Mass-editing formulas?

Hello,

I am trying to create a gradebook for about 300 students. Is there a way to batch-edit functions, and also to allow flexibility in formulas for a varying number of columns?

What I want to do is this: I have the students' names in A. Their grades go horizontally in columns B through Last column - 1. In the last column, I want to show the students' percentages. Each column represents one assignment, and since I don't yet know the total number of graded assignments, I want this to be flexible without requiring the rewriting of every formula.

Is there a way to, for example, click on a column (the "final" column of my gradebook) and insert a formula that basically says, "for each row, take every value in cells from column B through the column before this, add together, then divide by the sum of the "points possible" row (row 1)"?

Clear as mud? Since all I want to do is have each row add all its numerical values together, however many there are, and then divide by the sum of all values in the "total points possible" row, I don't want to have to encode the same formula, varying only for which row I'm in, 300 times!

Thanks everyone,
Michael

2. ## Re: Mass-editing formulas?

A sample worksheet attached would help.

3. ## Re: Mass-editing formulas?

Here's an attached worksheet to show the format.

4. ## Re: Mass-editing formulas?

In D2:

=SUM(\$B\$2:\$B2)/\$B\$1

copied down.

Is this what you mean?

5. ## Re: Mass-editing formulas?

When I plug that in, I get the correct value for D2 (although it posts in D3, I'm not doing something right...) but can I set the D column so that every grade will get computed? And also so that if I add columns in between, the formula still works? I'm very new to Excel (don't know what \$ means in a formula, for example).

6. ## Re: Mass-editing formulas?

I'm not exactly sure what you are saying... maybe try posting the sample again showing some expected results in column D.

If you insert columns between B and D, the formulas will remain referencing column B... is that what you want or no?

The \$ makes the row/column reference absolute (i.e. it "freezes" the column letter/number so that you can copy down/across the formula without it changing the cell it is referencing).

7. ## Re: Mass-editing formulas?

see the attached file.
You can insert column before F and formulas will adjust to give you average from column C to previous coulmn and the percentage as well.
modytrane.

8. ## Re: Mass-editing formulas?

NBVC,
I think he means that if you add a column, the new average or perecentage calculation should add all coulmns from \$B to the previous column.

So let's say you have added a number of columns and your average is now in column J, then you need to add all scores from B to I and divide by number of values to get the average.

I am sure you will have a better solution than the one I provided.

modytrane

9. ## Re: Mass-editing formulas?

mweyand,
my message with the sample file should read:

insert column before C and the average will account for columns B through the previous column. The percentage will also take care of inserted coulmns.

modytrane

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1