Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Using IF Formula on Text to produce more than 2 results

0

I am trying to use Excel for a survey. The responses from a drop down are Yes, No, or Partial. The scores are Yes = 5 No = 0 Partial = 2.

respondents enter the response from a drop down and automatically see their score based on the thre choices using the IF Statement. What is wrong with my formula?

=IF(E5="Yes","5", IF(E5="no","0"), IF(E5="partial", "2")) 

Answer
Discuss

Answers

0

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.

  1. IF ( [E5 = "Yes"],
    [If True - write 5],
    [If False - examine again] )
  2. 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.

  1. 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)
  2. 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.

Discuss

Discussion

As an afterthought, you should consider carefully whether it suits your intentions to assign the same value (0 = "") to both "No" answers and no answers. Note that the COUNT() function will count a 0 but not "". You could use the COUNTIF() or COUNIFS() functions to count both the selected results or their evaluations.
Variatus (rep: 4889) Dec 19, '18 at 7:51 pm
Thank you so much. This works great aside from one small error.  Using the formula =INDEX({5,0,2},MATCH(E4,{"yes","no","partial"}))
When E4 is populated with Yes it returns 2. It should produce a 5.
 It works great when cell E 4 is populated with No = 0 and  Partial it produces 2.
I've tried it in E% and other cells but I cant get it to populate 5 with yes.
What do I have wrong with this formula?
=INDEX({5,0,2},MATCH(E4,{"yes","no","partial"}))

Thank You!
jrb2003 Dec 19, '18 at 10:56 pm
Sorry, jr. My apologies! The MATCH() function was missing a required parameter, in this case a zero. I have modified my answer to correct this omission. The formula now works as advertised.
Variatus (rep: 4889) Dec 20, '18 at 2:31 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login