If isblank increment cell x 1

[wjhendrix3]

I am a teacher. I have created a spreadsheet that allows me to enter my students' grades for the term. I am now attempting to get it to calculate the correct grade. I am able to get the grades added but I am having problems with the calculations part. Here's my dilemma. I do not want to have to modify my spreadsheet or the formula every time I enter a grade in order to get it to calculate so I have built it so that there is no need for future expansion.

However, the problem with this is that during some terms I may have less grades than others. This will cause an issue with the grade calculations because, for instance, if I have four test grades instead of five, the calculation formula will still divide the overall grades by five instead of four. What I’ve done to combat this is create an extra column that calculates the total number of tests taken. This number will then be used to divide the total grade.

What I need help with is incrementing this cell by one when any number is entered in one of the five available slots and not doing so if the cell is left blank. I have tried the =IF(ISBLANK(AA4),0,1) formula but I cannot get it to calculate beyond one cell. I need someone to help me add one to cell AF4 when grades are entered in cells AA4 through AE4.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>

 

[Aladin Akyurek]

It's probably easier to have the formula that calculates the end grade.

 

[Gara]

Without seeing your sheet I can't be sure, but it seems like your best option is to change the denomenator to be a count of the range of cells where test grades are entered.

For example if Student A's tests were entered in cells B2-F2 you would divide by
MAX(Count(B2:F2), 1). The Max part just prevents the formula from returning an error when no grades are entered.

You could also, put that formula in cells of you count column so that you don't have to worry about changing your overall grade formula.

 

[wjhendrix3]

My formula is already set to calculate all the grades. This is fine if all of the grading slots are filled. The problem is if all the slots aren't filled, the formula will need to be modified or the grade will be inaccurate because the overall points will be divided by the total number of cells in the array as opposed to being divided by the total number of grades entered.

 

[wjhendrix3]

@Gara: Would that formula increment the total number of grades. If you give me an email address, I will send you a copy of the spreadsheet I'm working on.

 

[Aladin Akyurek]

My formula is already set to calculate all the grades. This is fine if all of the grading slots are filled. The problem is if all the slots aren't filled, the formula will need to be modified or the grade will be inaccurate because the overall points will be divided by the total number of cells in the array as opposed to being divided by the total number of grades entered.

Care to post that "formula [that] is already set to calculate all the grades"?

 

[Gara]

@Gara: Would that formula increment the total number of grades. If you give me an email address, I will send you a copy of the spreadsheet I'm working on.

So it will count the number of grades entered. If 4 of the five spaces are filled it will return a value of 4. If you were to then enter in another grade it would be 5. Similarly, if you deleted a grade (ex. accidentally put the wrong grade on the wrong student) it would drop to 3.

If you'd still like me to look at it, you can send it to gara742@gmail.com

 

[wjhendrix3]

Care to post that "formula [that] is already set to calculate all the grades"?

@Aladin: The formula is as follows:

=((SUM(AA4:AE4)/AF4)*0.4)

It adds all the cells for the test grades then divides it by cell AF4 (which is supposed to contain the incremented number) then multiplies it by 40% (which is the percentage of the total grades that tests count. There is another cell that will take the 40% test grades, 50% classwork grades, and 10% homework grades and add them all together to get the final grade.

 

[Aladin Akyurek]

@Aladin: The formula is as follows:

=((SUM(AA4:AE4)/AF4)*0.4)

It adds all the cells for the test grades then divides it by cell AF4 (which is supposed to contain the incremented number) then multiplies it by 40% (which is the percentage of the total grades that tests count.

Either...

AF4:

=COUNT(AA4:AE4)

along with...

=((SUM(AA4:AE4)/AF4)*0.4)

Or...

=((SUM(AA4:AE4)/COUNT(AA4:AE4))*0.4)

There is another cell that will take the 40% test grades, 50% classwork grades, and 10% homework grades and add them all together to get the final grade.

=SUM(TestRange) * 40% + SUM(ClassworkRange) * 50% + SUM(HomeworkRange) * 10%

 

[wjhendrix3]

Either...

AF4:

=COUNT(AA4:AE4)

along with...

=((SUM(AA4:AE4)/AF4)*0.4)

Or...

=((SUM(AA4:AE4)/COUNT(AA4:AE4))*0.4)




=SUM(TestRange) * 40% + SUM(ClassworkRange) * 50% + SUM(HomeworkRange) * 10%

I already have the following formula setup and working:

=SUM(TestRange) * 40% + SUM(ClassworkRange) * 50% + SUM(HomeworkRange) * 10%

I will try the following formula to see if it works:

=COUNT(AA4:AE4)

The last part of the formula is also setup in a different cell as well because I need to see that information separately. The only part of the formula I was not able to get to work was the incrementation part of cell AA4. I will give the formula a try and let you know if it corrected my problem.