Excel, formula adding point to answers.

Closed
Report
-
 jackfood -
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!!!

2 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
why not this
=IF(A1="blue",1,IF(A1="green",2,IF(A1="yellow",3,"")))
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