Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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

=100-SUM(F3-5)*-7

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:

=100*SUM(G3:H3)/200

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?

share|improve this question
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). –  likeitlikeit 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. –  Jacob Jan Tuinstra 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. –  Jacob Jan Tuinstra Apr 15 '13 at 5:50
add comment

2 Answers 2

up vote 0 down vote accepted

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)
share|improve this answer
    
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
add comment

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.

share|improve this answer
    
Thank You!! This worked like a charm!! –  bulls-eye Apr 15 '13 at 0:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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