You and your friend decided to go to Goa after you finish your finals.…
2023
You and your friend decided to go to Goa after you finish your finals. However, your demanding parents set a couple of conditions to join your friends (see MS-EXCEL spreadsheet below).
A | B | C | D | |
|---|---|---|---|---|
1 | Category of condition | Minimal results needed | Actual results | Allowed (Yes/No)? |
2 | Hours spent on homework weekly | 2 | 3 | |
3 | Midterm score | 70 | 68 | |
4 | Final exam score | 75 | 77 |
Imagine your parents ask you to either meet the final exam score condition or both of the two remaining (homework and midterm) conditions. Which of the following formulas A-C can be used in cell D2 to answer the question: will you be able to join your friends with the results given?
A. =IF(C4>=B4, "Yes", IF(AND(C2>=B2, C3>=B3), "Yes", "No"))
B. =IF(AND(C2>=B2, C3>=B3), "Yes", IF(C4>=B4, "Yes", "No"))
C. =IF(OR(AND(C2>=B2, C3>=B3), C4>=B4), "Yes", "No")
Choose the correct answer from the options given below:
Answer: D. A, B and C — ConceptA condition of the form X OR (Y AND Z) is true when X is true, or when both Y and Z are true. In a spreadsheet, nested IF functions may test the two OR…
- A.
A and B only
- B.
A and C only
- C.
B and C only
- D.
A, B and C
Show answer & explanation
Correct answer: D
Concept
A condition of the form X OR (Y AND Z) is true when X is true, or when both Y and Z are true. In a spreadsheet, nested IF functions may test the two OR branches in either order, while OR and AND may express the same logic directly.
Application
Let H mean C2>=B2, M mean C3>=B3, and F mean C4>=B4.
Using the sheet values, H is 3>=2, so H is TRUE; M is 68>=70, so M is FALSE; and F is 77>=75, so F is TRUE.
The required condition is F OR (H AND M). Substitution gives TRUE OR (TRUE AND FALSE), which evaluates to TRUE.
Formula A tests F first. Since F is TRUE, it returns Yes.
Formula B tests H AND M first. That branch is FALSE, so it tests F; F is TRUE, and it returns Yes.
Formula C directly evaluates OR(AND(H,M),F). This is OR(FALSE,TRUE), so it returns Yes.
Cross-check
A and B differ only in which branch of the same OR condition is tested first; C writes the two branches inside OR. Therefore all three formulas implement the stated rule and produce Yes for the supplied values.
Result: A, B and C.