Forum Discussion
Combining IFS(SEARCH
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
SergeiBaklanI have an answer "Servay " that got evaluated as 0.
What's wrong?
=SUM(COUNTIF(F2,"*"&{"survey","survay","servay"} &"*")*{1,0.8,0.5})
It works
If you are on Excel which doesn't support dynamic arrays try to use SUMPRODUCT() instead of SUM(). Please check the file attached.
- Thanks for this
Or
=IFS(ISNUMBER(SEARCH("survey",F2)),1,ISNUMBER(SEARCH("survay",F2)),0.8,ISNUMBER(SEARCH("servay",F2)),0.5,TRUE,0)
HansVogelaarwhy did you use isnumber. why check to see if it is a number?
- Thanks
.
Why isnumber?
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)
- That explains it. I thought ifs was more supple 😇
