Excel, formula adding point to answers.Closed

Question

Hello,
Using excel 2000.

I am creating a question which needs to creating points for answer.

Cell A187
Example:
What colour of car is expensive?
Answer:
blue, yellow, green.

typing in formula in Cell A188

If a person type blue, Cell A2 will get 1.
If a person type blue and green, Cell A2 will get 2 and so on.

I have 2 codes for this cell but only managed to get 1 point.
=IF( SUM(--ISNUMBER(SEARCH({"blue","green","yellow"}, A187))),"1","0")

another one is
=IF(ISNUMBER(SEARCH("blue",A187)),1,IF(ISNUMBER(SEARCH("green",A187)),1,0))

Can anyone improve or give me a new formula. Very much Thanks!!!

venkat1926 · Answer

why not this 
=IF(A1="blue",1,IF(A1="green",2,IF(A1="yellow",3,"")))

jackfood · Answer

but if i type in "green" for the cell in A1, it will turn 2 instead of 1. Because although "green" is a answer, but only counted as only 1 point.

Condition:
if type in "green and blue", 2 points
if type in "yellow and blue", 2 points
if type in "yellow" 1 point
if type in "green" 1 point
if type in "hello" 0 point
if type in "hello, i am yellow and green people" 2 points

Excel, formula adding point to answers.

2 responses

Similar discussions

Subscribe To Our Newsletter!