+ Reply to Thread
Results 1 to 9 of 9

Mass-editing formulas?

  1. #1
    Registered User
    Join Date
    03-31-2009
    Location
    Plymouth, MN
    MS-Off Ver
    Excel 2008 for Mac
    Posts
    3

    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
    Last edited by mweyandt; 04-03-2009 at 09:36 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Mass-editing formulas?

    A sample worksheet attached would help.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    03-31-2009
    Location
    Plymouth, MN
    MS-Off Ver
    Excel 2008 for Mac
    Posts
    3

    Re: Mass-editing formulas?

    Here's an attached worksheet to show the format.
    Attached Files Attached Files

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Mass-editing formulas?

    In D2:

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

    copied down.

    Is this what you mean?

  5. #5
    Registered User
    Join Date
    03-31-2009
    Location
    Plymouth, MN
    MS-Off Ver
    Excel 2008 for Mac
    Posts
    3

    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. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    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. #7
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,676

    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.
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,676

    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. #9
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,676

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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