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

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)

"Blobbies" wrote in message
...
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

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" wrote:

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)

"Blobbies" wrote in message
...
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

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)

"Blobbies" wrote in message
...
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" wrote:

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)

"Blobbies" wrote in message
...
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

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" wrote:

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)

"Blobbies" wrote in message
...
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" wrote:

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)

"Blobbies" wrote in message
...
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

Like your pragmatism <G

Bob

"Blobbies" wrote in message
...
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" wrote:

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)

"Blobbies" wrote in message
...
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" wrote:

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)

"Blobbies" wrote in message
...
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