EXCEL formula

[lbkubic]

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?

 

[DelJo63]

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.

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.

 

[SNGX1275]

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.

 

[terra_nova]

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

 

[DelJo63]

my sample is attached

 

[lbkubic]

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.

 

[DelJo63]

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?

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?

 

[DelJo63]

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.

 

[fastco]

My head is spinning after reading this stuff. Too advanced for me.