Assign a Value if Some of the Text in 2 Cells are the Same

[Guest]

Excel 2003

I have a sports picking spreadsheet. It simply compares peoples' picks with
the actual result and awards 1 point for getting it right and 0 for getting
it wrong.

At a late stage I have decided to award 2 points for getting the result
exactly right and 1 point for managing to pick the winning correct team.

I am struggling with how to get Excel to compare 2 cells and picking out
that they both have (or don't have) the same winning team, albeit different
winning margins.

eg

The result of the game is: Highlanders beat the Bulls by 18 points.

Person A has picked "Highlanders by 15 or less" (Cell A1)
The actual result is "Highlanders by 16 or more" (Cell A2)

So I need a formula that says: A1=A2 so 2 points, but if A1 does not equal
A2, then "Highlanders" is in both cell A1 & A2, so 1 point!

Hope that's not too confusing! Many thanks for any answers!



Mike

 

[Bob Phillips]

Use multiple cells

A1/B1 - winner
A2/B2 - spread

then it is imple to use

=if(B1=A1,IF(B2=A2,2,1),0)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

thanks for your reply, bob!

however, that would involve some more work for me when i input the players
picks - i.e. i would need to be putting 2 seperate entries for each game - i
was hoping i might be able to do it from the 1 original input!

i could certainly do it that way, but as i say, was hoping i could get
around any extra inputting .....


cheers bob!



mike

 

[Bob Phillips]

You could do it in one formula as long as it is all single names

=IF(LEFT(A1,FIND(" ",A1))=LEFT(B1,FIND(" ",B1)),IF(MID(A1,FIND("
",A1),255)=MID(B1,FIND(" ",B1),255),2,1),0)

It fails if a team is named The Highlanders for instance.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

thanks bob

that is great! i can use that by simply changing the "western force" to
"westernforce" the rest of the teams are one word!!

i really appreciate your time and efforts - cheers!



mike

 

[Bob Phillips]

Like your pragmatism <G>

Bob

thanks bob

that is great! i can use that by simply changing the "western force" to
"westernforce" the rest of the teams are one word!!

i really appreciate your time and efforts - cheers!



mike