Nesting "or" functions within an "if" function

Question

Level 1

0 points

Nesting "or" functions within an "if" function

I am creating a grading spreadsheet. I have 2 sheets within the spreadsheet, one with variables that register points possible and minimum acceptable points on 8 different criteria. I am trying to create a cell that will say "passed" if all criteria is met and "failed" if not. The "E" column has the grade and the Variables!D# is the minimum allowable. Here is the formula that I am trying to create to accomplish this:

=IF(E22<Variables!D9+OR(E21<Variables!D8)+OR(E20<Variables!D7)+OR(E19<Variables!D6)+OR(E18<Variables!D5)+OR(E17<Variables!D4)+OR(E16<Variables!D3)+OR(E15<Variables!D2),"Failed","Passed")

And I am sure there is an easier way but the spreadsheet accepts the formula but always reads "Passed".

Thanks in advance for any help you can offer.

MacBook Pro, iOS 8.1.2

Posted on Jun 28, 2015 11:46 AM

Reply

Answer 1

t quinn

Level 5

7,043 points

Jun 28, 2015 12:16 PM in response to David-Hendo

Hi David,

At some point you may find it desirable to reorganize your tables . If you do, this forum can help.

I think you have syntax issues.

=IF(OR(E22<Variables!D9,E21<Variables!D8,E20<Variables!D7,E19<Variables!D6,E18<Variables!D5),"Failed","Passed")

Should make things clearer. I dropped the last clause because it was incomplete.

quinn

Reply

Answer 2

David-Hendo Author

Level 1

0 points

Jul 2, 2015 9:51 AM in response to t quinn

Thanks for taking the time to help, Quinn. I don't understand the comment regarding reorganizing the table. Can you be more specific? Also, while your syntax does return no errors, it doesn't return the value accurately. It seems clear by the formula that I am trying to have "Failed" returned if "E22" is less than"Variable!D9" . . . etc. but the result is alway "Passed". Thanks again in advance for any help you can offer.

Reply

Answer 3

t quinn

Level 5

7,043 points

Jul 2, 2015 10:59 AM in response to David-Hendo

Hi David,

I am shooting in the dark because I don't have a good look at your tables.

OR(E22<Variables!D9,E21<Variables!D8,E20<Variables!D7,E19<Variables!D6,E18<Variables!D5)

should return "true" if any of the clauses are true. "True" would return "Failed". I did not try to recreate you tables, just fix the syntax. I don't have time right now to debug this but you could test the OR() statement separately from the if and see if you get a true.

quinn

Reply

Answer 4

SGIII

Level 8

35,276 points

Jul 2, 2015 11:51 AM in response to David-Hendo

Have you studied the Gradebook template? (File > New > Template chooser -- under Education).

You might be able to adapt it by changing the Assignments to criteria and the Grade Scale to pass/fail percentages.

SG

Reply