I am a music teacher and I use the following formula to calculate my students performance grades:


How can I change the formula so it only calculates a grade if there is data in the cell?

Additionally I use this formula to calculate the average of my students daily participation/conduct and performance grade:


Same basic question as above, how can I change the formula so it only runs if there is data in H3 and if there is not the solution equals G3?

  • 1
    I don't understand your first formula. Please write down what you actually want to compute. The way I understand it, you add up the values in F3, F4 and F5, multiply them by 7, and then add 100 to it. (the two minuses cancel each other out). Apr 14 '13 at 17:37
  • Please clarify SUM(F3-5)? If you subtract, why use SUM. If it is a typo for SUM(F3-F5), then the same remark can be made. Apr 14 '13 at 18:45
  • My question has been answered but to clarify for you my students get a performance grade out of 5. 5=100 4=93 3=86 etc. So if there grade is a 5 F3=5 100-(5-5)*-7= 100% if they get a 4 then 100-(4-5)*-7= 93%, and so on. I am still new to formulas in spreadsheets, I literally started trying to figure this stuff out last week, so I may not even need SUM in the formula. I just haven't learned the various rules yet. Thanks for your reply.
    – bulls-eye
    Apr 15 '13 at 0:30
  • Then I suggest to write it like this: 100-((5-5)*-7). The extra brackets will give (somebody else) more insight. To give even more clarity, use this: 100-ABS((5-5)*7). This will turn all outcome between the brackets into a positive value. Apr 15 '13 at 5:50

Use the IF function, for reference check the following

Google spreadsheets function list

so for the students performance grades, you could use the following

=IF(F3<>"", 100-SUM(F3-5)*-7, "")

and for the students daily participation/conduct and performance grade

=IF(H3<>"", 100*SUM(G3:H3)/200, G3)
  • Thank You!!! These work awesome!!! Now I just have to do my homework and figure out why so I can start answering questions instead of asking them.
    – bulls-eye
    Apr 15 '13 at 0:25

Try the following formula, using an IF condition:

=if(H3=""; G3; 100*SUM(G3:H3)/200)

This means that if H3 equals "" (i.e. is empty), show the contents of G3 only, otherwise compute the result of the formula in the third argument instead.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.