TechSpot

EXCEL formula

By lbkubic
Jun 28, 2006
  1. I need help with an Excel Formula. I am creating a gradebook using points and not values for grades. For examples, E5:BE5 represents the total points possible for each assignment, E6:BE6 represents the actual points received, and E7:BE7 represents attendance. BD7 will sum E5:BE5, BC7 will sum E6:BE6, and BC6 will give the percent grade. My problem is when a new student comes in during the middle of marking period, I only want to add the total points from the point when he/ she entered. In other words, in E7:BE7 (attendance line) I want to put an "n" for new and in BD7 I wantto have a formula that states "if there is an "n" in E7:BE7 to sum only the cells of E5:BE5 that are after the column that "n" is in". Is this possible?
     
  2. jobeard

    jobeard TS Ambassador Posts: 13,446   +324

    just to be sure what you're saying; Cell E5:BE5 contains the value of maximum points and the student score is in cell E6:BE6.
     
  3. SNGX1275

    SNGX1275 TS Forces Special Posts: 12,623   +320

    You can leave the earned points formula the same, because if excel doesn't see a value in a box that is in a sum (example Sum(E6:BE6) will ignore E6:Z6 if nothing is there).
    I think though you will have to create a new formula for points possible. Actually there are some IF things in excel I think, but its over my head. Surely you don't have so many new students comming in that you need something complex like that rather than just rewriting the formula in just their cell.
     
  4. terra_nova

    terra_nova TS Rookie

    Hi lbkubic,

    There is a formula in excel that will do this for you, at lest if my interpretation of your post is correct. All you need is a formula to sum the total available marks from when the student started?

    If this is the case then you can use an if statement, or rather many if statements nested together (this works in excel 2003 i assume it will work for previous versions also)

    The way excel uses if statements is of the form IF [logical test] THEN [if test is true] ELSE [if test is false]. The syntax for this in excel is IF([test],[then part],[else part])

    so here is what you need. the first test is of the first column of the attendance row. if this is an "n" then you want to test the next column in the attendance row to see if that is also an "n", this is where the nesting takes place. if it is not an "n" (this is the else part) then you want to sum all values in the available marks row. I have done a test run on a much smaller database the layout was available marks from c5 to g5, actual marks from c6 to g6, and attendance from c7 to g7. the formula i used is below

    =IF(C7="n",IF(D7="n",IF(E7="n",IF(F7="n","hi",SUM(F5:G5)),SUM(E5:G5)),SUM(D5:G5)),SUM(C5:G5))

    What the formula does is sums all of the values after the last n, but will only work if there is a run of n's from the start of the marking. ie all n's first, followed by something other than n's

    I realise this isnt too easy to read! i stopped nesting the IF statement at f7, so this is the middle statement.

    I couldnt find a way round having to check each of the cells, but once the fomula is written autofill should help you out for the other students.

    I hope this helps, and if your unsure, ill try and be a little clearer!

    Terra
     
  5. jobeard

    jobeard TS Ambassador Posts: 13,446   +324

    my sample is attached
     
  6. lbkubic

    lbkubic TS Rookie Topic Starter

    Excel formula sampler

    I am attaching a sample of what I am trying to achieve. i would like to incorporate a formula that in BD7 that states "if there is an "n" in E7:BE7, then only sum the columns in E5:BE5 that are after the column that "n" is located.
     

    Attached Files:

  7. jobeard

    jobeard TS Ambassador Posts: 13,446   +324

    It appears that the NEW STUDENT is graded upon only the POSSIBLE scores from
    the date of entry to the end of the term. Is this the reasoning?
     
  8. jobeard

    jobeard TS Ambassador Posts: 13,446   +324

    Like to recommend an approach to designing 'solutions':
    1. do the hard part first
    2. solve the problem to get a solution
    3. then add the presentation of the solution
    Steps 1-2 ensure you will complete the problem and if not, you can move on
    without investing lots of time in a presentation that goes no where.

    Second thought: Separate concepts into separate worksheets.
    Attendance is very different from Grading. If there is some values from one
    worksheet that needs to be referenced in another there's the style
    =worksheet_name!cell_location
    =average(worksheet_name!b3:b9)​

    Excel has a limit of nested functions and nesting even just once per month will still get you into
    trouble.

    SNGX1275's point on empty cells getting ignored is right on and fits the 'Excel programming model'.
    You can infer the 'n' status from a cell w/o any data. A Cell containing #0 however,
    might mean the test has not been taken or sadly, the student scored zero.


    I recommend you rethink the problem to be solved and uncouple it from the
    presentation.

    best wishes.
     
  9. fastco

    fastco TS Rookie Posts: 1,511

    My head is spinning after reading this stuff. Too advanced for me.
     
Topic Status:
Not open for further replies.


Add New Comment

TechSpot Members
Login or sign up for free,
it takes about 30 seconds.
You may also...


Get complete access to the TechSpot community. Join thousands of technology enthusiasts that contribute and share knowledge in our forum. Get a private inbox, upload your own photo gallery and more.