Selected Answer
The IF statement has 3 components.
= IF ( [Expression], [Result if TRUE], [Result if FALSE] )
Your formula has a nested IF statement for each of the 2 results of the Expression. Neither of these two is complete. They both miss the [Result if FALSE] component.
The first step towards fixing your formula is to get the logic right, expressing it in plain language. Here we go.
- IF ( [E5 = "Yes"],
[If True - write 5],
[If False - examine again] )
- Examine again:
IF ( [E5 = "No"],
[If True - write 0],
[if False - write 2] ))
If E5 is neither "Yes" nor "No" it must be "partial"
IF ( [E5 = "Yes"], [If True - write 5], [If False - IF ( [E5 = "No"], [If True - write 0], [if False - write 2] )] )
This logic translates into the following formula.
= IF(E5 ="Yes",5, IF(E5 ="No"],0,2))
Observe that the results should be 5, 2 and 0, not "5", "2" and "0". The former are numbers which you can use to do calculations with, the latter are words which Excel can sometimes understand as numbers, but not always.
Another logic, arriving at the same conclusion would be an instruction rather than a condition. Here is the logic in plain language.
- Find the value of E5 in the array {"Yes","No","Partial"}
and tell me its position in the array as n.
(If E5 = "No" n would be 2 because "No" is the second element)
- Return the nth value of the array {5,0,2}
This would result in the formula below. Due to Excel's construction of the two functions used it appears as if it would inverse the above logic.
=INDEX({5,0,2},MATCH(E5,{"yes","no","partial"},0))
This formula would return an error if E5 contains none of the 3 choices, for example nothing. Therefore you might embed the formula in an ISERROR() function, as shown below.
=IFERROR(INDEX({5,0,2},MATCH(E5,{"yes","no","partial"},0)),"")
Observe that the IF-based formula first shown above would return 2 = "partial" as the default if E5 has an unexpected value. You can guard against that by adding an extra IF, in fact not executing the formula if E5 is blank.
=IF(E5="","", IF(E5 ="Yes",5, IF(E5 ="No"],0,2)))
This demonstrates the inflexibility of the IF statement. It provides for "Yes", "No", "[blank]" or [Else]. Since the rest of your sheet doesn't permit any other value in E5 that doesn't matter for you but the IF-less instruction actually identifies a non-conforming value. To achieve the same with IF statements would be more voluminous.