Excel formula question

Copper Contributor

I have built a spreadsheet for a school to record the students' attendance in hours and their grades. In this school, if the student fails the class, he/she is not entitled to the hours. How can I modify the formula already in place which add the hours so that they become zero if the grade entered is an F? That is, how can I have the formula do the first part UNLESS the grade entered in another cell is an F?

2 Replies

@beckjohn In general terms you would sum the hours and multiply it by 1 if the grade is not equal to "F", otherwise multiply by 0.

Such a formula would look like: =SUM(hours)*(grade<>"F")

 

Demonstrated in the picture below. Formula entered in B6 (as shown in C6).

Screenshot 2022-09-25 at 07.18.27.png

@beckjohn 

Thank you Riny_van_Eekelen for your reply to my posted question. I've been struggling trying to figure out how to apply your suggestion to my spreadsheet with no luck so far. I agree with your concept; just can't figure out how to make it work here. You see the total hours are the result of additions and subtractions based on content in columns D, E, and F. The grade is entered in column O. How can I send you a copy of my spreadsheet?