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>

 

[wjhendrix3]

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.

=COUNT(AA4:AE4) did not work. The code returned a 'FALSE' message in the cell.

=((SUM(AA4:AE4)/AF4)*0.4) is in cell AG4 and works without a problem.

The following codes are in the following cells and work with no problem. I know it's redundant information but I need it all to show a certain way on the final report so this is why it's done this way.
'=M4' is in AL4
'=Z4' is in AM4
'=AG4' is in AN4

'=(AL4*.5)+(AM4*.1)+(AN4*.4)' is in AO4 and works without a problem.

I just need help with the code to increment the number of assignments in AG4. I will then be able to edit that code to put in M4 and Z4 to get those calculations done as well. Once that is done, the sheet will be complete and it will calculate properly.

 

[Gara]

=COUNT(AA4:AE4) did not work. The code returned a 'FALSE' message in the cell.

If you could send me your workbook I'll look at it. That you're getting a True/False out of a non-boolean formula is odd, so without tracing things from start to finish I can't be sure how that's happening.

 

[wjhendrix3]

If you could send me your workbook I'll look at it. That you're getting a True/False out of a non-boolean formula is odd, so without tracing things from start to finish I can't be sure how that's happening.

I will. But, how do I attach a document to this forum? If you can't, just PM me with an email address and I will send it to you. I am on the way home but should be back up and running in about 20 minutes. I will check back here when I get there.

 

[wjhendrix3]

I will. But, how do I attach a document to this forum? If you can't, just PM me with an email address and I will send it to you. I am on the way home but should be back up and running in about 20 minutes. I will check back here when I get there.

Thanks for all the assistance I got. I have figured out the problem. I used the following code to count the nonempty cells and return a value:

=COUNTA(AA4:AE4)

I have tested the code and it is working.