SOLVED

Combining IFS(SEARCH

%3CLINGO-SUB%20id%3D%22lingo-sub-2153451%22%20slang%3D%22en-US%22%3ECombining%20IFS(SEARCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2153451%22%20slang%3D%22en-US%22%3EI%20have%20a%20student%20quiz.%20I'm%20parsing%20a%20phrase%20to%20see%20if%20the%20student%20answered%20%22survey%22%20(case%20insensitive).%3CBR%20%2F%3EI'm%20assigning%201%20point%20for%20a%20correct%20answer%3CBR%20%2F%3E.8%20for%20the%20spelling%20survay%3CBR%20%2F%3E.5%20for%20servay%3CBR%20%2F%3EElse%200%3CBR%20%2F%3EIt's%20bugging%20on%20survay.%20It's%20not%20even%20seeing%20the%20else%20true%2C%20%220%22%3CBR%20%2F%3EIdeas%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIFS(SEARCH(%22survey%22%2CF2)%2C%221%22%2CSEARCH(%22survay%22%2CF2)%2C%22.8%22%2CSEARCH(%22servay%22%2CF2)%2C%22.5%22%2CTRUE%2C%220%22)%3CBR%20%2F%3E%3CBR%20%2F%3EJohn%20in%20Qu%C3%A9bec%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2153451%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2153463%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20IFS(SEARCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2153463%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F973750%22%20target%3D%22_blank%22%3E%40jtjohnston_quebec%3C%2FA%3E%20The%20problem%20is%20that%20SEARCH%20returns%20an%20error%20value%20if%20the%20term%20is%20not%20found.%3C%2FP%3E%0A%3CP%3ESo%20if%20the%20student%20answers%20anything%20but%20%22survey%22%2C%20the%20first%20SEARCH%20causes%20the%20entire%20formula%20to%20return%20an%20error%3B%20the%20rest%20of%20the%20formula%20isn't%20evaluated.%3C%2FP%3E%0A%3CP%3EApart%20from%20that%2C%20I'd%20make%20the%20function%20return%20numbers%20such%20as%200.8%20instead%20of%20text%20strings%20such%20as%20%220.8%22.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DIFS(IFERROR(SEARCH(%22survey%22%2CF2)%2C0)%2C1%2CIFERROR(SEARCH(%22survay%22%2CF2)%2C0)%2C.8%2CIFERROR(SEARCH(%22servay%22%2CF2)%2C0)%2C.5%2CTRUE%2C0)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2153506%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20IFS(SEARCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2153506%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F973750%22%20target%3D%22_blank%22%3E%40jtjohnston_quebec%3C%2FA%3E%3C%2FP%3E%0A%3CP%3EOr%3C%2FP%3E%0A%3CP%3E%3DIFS(ISNUMBER(SEARCH(%22survey%22%2CF2))%2C1%2CISNUMBER(SEARCH(%22survay%22%2CF2))%2C0.8%2CISNUMBER(SEARCH(%22servay%22%2CF2))%2C0.5%2CTRUE%2C0)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2153530%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20IFS(SEARCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2153530%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F973750%22%20target%3D%22_blank%22%3E%40jtjohnston_quebec%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUM(COUNTIF(F2%2C%22*%22%26amp%3B%7B%22survey%22%2C%22survay%22%2C%22servay%22%7D%20%26amp%3B%22*%22)*%7B1%2C0.8%2C0.5%7D)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2153775%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20IFS(SEARCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2153775%22%20slang%3D%22en-US%22%3EThanks%3CBR%20%2F%3E.%3CBR%20%2F%3EWhy%20isnumber%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2153777%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20IFS(SEARCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2153777%22%20slang%3D%22en-US%22%3EThanks%20for%20this%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2153779%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20IFS(SEARCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2153779%22%20slang%3D%22en-US%22%3EThat%20explains%20it.%20I%20thought%20ifs%20was%20more%20supple%20%3Asmiling_face_with_halo%3A%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2154482%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20IFS(SEARCH%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2154482%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3Ewhy%20did%20you%20use%20isnumber.%20why%20check%20to%20see%20if%20it%20is%20a%20number%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor
I have a student quiz. I'm parsing a phrase to see if the student answered "survey" (case insensitive).
I'm assigning 1 point for a correct answer
.8 for the spelling survay
.5 for servay
Else 0
It's bugging on survay. It's not even seeing the else true, "0"
Ideas?

=IFS(SEARCH("survey",F2),"1",SEARCH("survay",F2),".8",SEARCH("servay",F2),".5",TRUE,"0")

John in Québec
11 Replies

@jtjohnston_quebec The problem is that SEARCH returns an error value if the term is not found.

So if the student answers anything but "survey", the first SEARCH causes the entire formula to return an error; the rest of the formula isn't evaluated.

Apart from that, I'd make the function return numbers such as 0.8 instead of text strings such as "0.8".

 

=IFS(IFERROR(SEARCH("survey",F2),0),1,IFERROR(SEARCH("survay",F2),0),.8,IFERROR(SEARCH("servay",F2),0),.5,TRUE,0)

@jtjohnston_quebec

Or

=IFS(ISNUMBER(SEARCH("survey",F2)),1,ISNUMBER(SEARCH("survay",F2)),0.8,ISNUMBER(SEARCH("servay",F2)),0.5,TRUE,0)

Best Response confirmed by jtjohnston_quebec (Occasional Contributor)
Solution

@jtjohnston_quebec 

As variant

=SUM(COUNTIF(F2,"*"&{"survey","survay","servay"} &"*")*{1,0.8,0.5})

 

Thanks
.
Why isnumber?
Thanks for this
That explains it. I thought ifs was more supple

@Hans Vogelaarwhy did you use isnumber. why check to see if it is a number?

@Sergei BaklanI have an answer "Servay " that got evaluated as 0.

 

What's wrong?

 

=SUM(COUNTIF(F2,"*"&{"survey","survay","servay"} &"*")*{1,0.8,0.5})

@jtjohnston_quebec 

It works

image.png

If you are on Excel which doesn't support dynamic arrays try to use SUMPRODUCT() instead of SUM(). Please check the file attached.

Very possible. I was using Google sheets. I'll go through it again. Thanks

@jtjohnston_quebec 

Sorry, can't help with Google Sheets, have no idea where it could be the difference.