software

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 quot;Highlanders by 15 or lessquot; (Cell A1)
The actual result is quot;Highlanders by 16 or morequot; (Cell A2)

So I need a formula that says: A1=A2 so 2 points, but if A1 does not equal
A2, then quot;Highlandersquot; is in both cell A1 amp; 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)

quot;Blobbiesquot; gt; wrote in message
...
gt; Excel 2003
gt;
gt; I have a sports picking spreadsheet. It simply compares peoples' picks
with
gt; the actual result and awards 1 point for getting it right and 0 for
getting
gt; it wrong.
gt;
gt; At a late stage I have decided to award 2 points for getting the result
gt; exactly right and 1 point for managing to pick the winning correct team.
gt;
gt; I am struggling with how to get Excel to compare 2 cells and picking out
gt; that they both have (or don't have) the same winning team, albeit
different
gt; winning margins.
gt;
gt; eg
gt;
gt; The result of the game is: Highlanders beat the Bulls by 18 points.
gt;
gt; Person A has picked quot;Highlanders by 15 or lessquot; (Cell A1)
gt; The actual result is quot;Highlanders by 16 or morequot; (Cell A2)
gt;
gt; So I need a formula that says: A1=A2 so 2 points, but if A1 does not
equal
gt; A2, then quot;Highlandersquot; is in both cell A1 amp; A2, so 1 point!
gt;
gt; Hope that's not too confusing! Many thanks for any answers!
gt;
gt;
gt;
gt; 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

quot;Bob Phillipsquot; wrote:

gt; Use multiple cells
gt;
gt; A1/B1 - winner
gt; A2/B2 - spread
gt;
gt; then it is imple to use
gt;
gt; =if(B1=A1,IF(B2=A2,2,1),0)
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Blobbiesquot; gt; wrote in message
gt; ...
gt; gt; Excel 2003
gt; gt;
gt; gt; I have a sports picking spreadsheet. It simply compares peoples' picks
gt; with
gt; gt; the actual result and awards 1 point for getting it right and 0 for
gt; getting
gt; gt; it wrong.
gt; gt;
gt; gt; At a late stage I have decided to award 2 points for getting the result
gt; gt; exactly right and 1 point for managing to pick the winning correct team.
gt; gt;
gt; gt; I am struggling with how to get Excel to compare 2 cells and picking out
gt; gt; that they both have (or don't have) the same winning team, albeit
gt; different
gt; gt; winning margins.
gt; gt;
gt; gt; eg
gt; gt;
gt; gt; The result of the game is: Highlanders beat the Bulls by 18 points.
gt; gt;
gt; gt; Person A has picked quot;Highlanders by 15 or lessquot; (Cell A1)
gt; gt; The actual result is quot;Highlanders by 16 or morequot; (Cell A2)
gt; gt;
gt; gt; So I need a formula that says: A1=A2 so 2 points, but if A1 does not
gt; equal
gt; gt; A2, then quot;Highlandersquot; is in both cell A1 amp; A2, so 1 point!
gt; gt;
gt; gt; Hope that's not too confusing! Many thanks for any answers!
gt; gt;
gt; gt;
gt; gt;
gt; gt; Mike
gt;
gt;
gt;

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

=IF(LEFT(A1,FIND(quot; quot;,A1))=LEFT(B1,FIND(quot; quot;,B1)),IF(MID(A1,FIND(quot;
quot;,A1),255)=MID(B1,FIND(quot; quot;,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)

quot;Blobbiesquot; gt; wrote in message
...
gt; thanks for your reply, bob!
gt;
gt; however, that would involve some more work for me when i input the players
gt; picks - i.e. i would need to be putting 2 seperate entries for each game -
i
gt; was hoping i might be able to do it from the 1 original input!
gt;
gt; i could certainly do it that way, but as i say, was hoping i could get
gt; around any extra inputting .....
gt;
gt;
gt; cheers bob!
gt;
gt;
gt;
gt; mike
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Use multiple cells
gt; gt;
gt; gt; A1/B1 - winner
gt; gt; A2/B2 - spread
gt; gt;
gt; gt; then it is imple to use
gt; gt;
gt; gt; =if(B1=A1,IF(B2=A2,2,1),0)
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Blobbiesquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Excel 2003
gt; gt; gt;
gt; gt; gt; I have a sports picking spreadsheet. It simply compares peoples'
picks
gt; gt; with
gt; gt; gt; the actual result and awards 1 point for getting it right and 0 for
gt; gt; getting
gt; gt; gt; it wrong.
gt; gt; gt;
gt; gt; gt; At a late stage I have decided to award 2 points for getting the
result
gt; gt; gt; exactly right and 1 point for managing to pick the winning correct
team.
gt; gt; gt;
gt; gt; gt; I am struggling with how to get Excel to compare 2 cells and picking
out
gt; gt; gt; that they both have (or don't have) the same winning team, albeit
gt; gt; different
gt; gt; gt; winning margins.
gt; gt; gt;
gt; gt; gt; eg
gt; gt; gt;
gt; gt; gt; The result of the game is: Highlanders beat the Bulls by 18 points.
gt; gt; gt;
gt; gt; gt; Person A has picked quot;Highlanders by 15 or lessquot; (Cell A1)
gt; gt; gt; The actual result is quot;Highlanders by 16 or morequot; (Cell A2)
gt; gt; gt;
gt; gt; gt; So I need a formula that says: A1=A2 so 2 points, but if A1 does not
gt; gt; equal
gt; gt; gt; A2, then quot;Highlandersquot; is in both cell A1 amp; A2, so 1 point!
gt; gt; gt;
gt; gt; gt; Hope that's not too confusing! Many thanks for any answers!
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Mike
gt; gt;
gt; gt;
gt; gt;
thanks bob

that is great! i can use that by simply changing the quot;western forcequot; to
quot;westernforcequot; the rest of the teams are one word!!

i really appreciate your time and efforts - cheers!
mike

quot;Bob Phillipsquot; wrote:

gt; You could do it in one formula as long as it is all single names
gt;
gt; =IF(LEFT(A1,FIND(quot; quot;,A1))=LEFT(B1,FIND(quot; quot;,B1)),IF(MID(A1,FIND(quot;
gt; quot;,A1),255)=MID(B1,FIND(quot; quot;,B1),255),2,1),0)
gt;
gt; It fails if a team is named The Highlanders for instance.
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Blobbiesquot; gt; wrote in message
gt; ...
gt; gt; thanks for your reply, bob!
gt; gt;
gt; gt; however, that would involve some more work for me when i input the players
gt; gt; picks - i.e. i would need to be putting 2 seperate entries for each game -
gt; i
gt; gt; was hoping i might be able to do it from the 1 original input!
gt; gt;
gt; gt; i could certainly do it that way, but as i say, was hoping i could get
gt; gt; around any extra inputting .....
gt; gt;
gt; gt;
gt; gt; cheers bob!
gt; gt;
gt; gt;
gt; gt;
gt; gt; mike
gt; gt;
gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt;
gt; gt; gt; Use multiple cells
gt; gt; gt;
gt; gt; gt; A1/B1 - winner
gt; gt; gt; A2/B2 - spread
gt; gt; gt;
gt; gt; gt; then it is imple to use
gt; gt; gt;
gt; gt; gt; =if(B1=A1,IF(B2=A2,2,1),0)
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; Bob Phillips
gt; gt; gt;
gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt;
gt; gt; gt; quot;Blobbiesquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; Excel 2003
gt; gt; gt; gt;
gt; gt; gt; gt; I have a sports picking spreadsheet. It simply compares peoples'
gt; picks
gt; gt; gt; with
gt; gt; gt; gt; the actual result and awards 1 point for getting it right and 0 for
gt; gt; gt; getting
gt; gt; gt; gt; it wrong.
gt; gt; gt; gt;
gt; gt; gt; gt; At a late stage I have decided to award 2 points for getting the
gt; result
gt; gt; gt; gt; exactly right and 1 point for managing to pick the winning correct
gt; team.
gt; gt; gt; gt;
gt; gt; gt; gt; I am struggling with how to get Excel to compare 2 cells and picking
gt; out
gt; gt; gt; gt; that they both have (or don't have) the same winning team, albeit
gt; gt; gt; different
gt; gt; gt; gt; winning margins.
gt; gt; gt; gt;
gt; gt; gt; gt; eg
gt; gt; gt; gt;
gt; gt; gt; gt; The result of the game is: Highlanders beat the Bulls by 18 points.
gt; gt; gt; gt;
gt; gt; gt; gt; Person A has picked quot;Highlanders by 15 or lessquot; (Cell A1)
gt; gt; gt; gt; The actual result is quot;Highlanders by 16 or morequot; (Cell A2)
gt; gt; gt; gt;
gt; gt; gt; gt; So I need a formula that says: A1=A2 so 2 points, but if A1 does not
gt; gt; gt; equal
gt; gt; gt; gt; A2, then quot;Highlandersquot; is in both cell A1 amp; A2, so 1 point!
gt; gt; gt; gt;
gt; gt; gt; gt; Hope that's not too confusing! Many thanks for any answers!
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; Mike
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;

Like your pragmatism lt;Ggt;

Bob

quot;Blobbiesquot; gt; wrote in message
...
gt; thanks bob
gt;
gt; that is great! i can use that by simply changing the quot;western forcequot; to
gt; quot;westernforcequot; the rest of the teams are one word!!
gt;
gt; i really appreciate your time and efforts - cheers!
gt;
gt;
gt;
gt; mike
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; You could do it in one formula as long as it is all single names
gt; gt;
gt; gt; =IF(LEFT(A1,FIND(quot; quot;,A1))=LEFT(B1,FIND(quot; quot;,B1)),IF(MID(A1,FIND(quot;
gt; gt; quot;,A1),255)=MID(B1,FIND(quot; quot;,B1),255),2,1),0)
gt; gt;
gt; gt; It fails if a team is named The Highlanders for instance.
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Blobbiesquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; thanks for your reply, bob!
gt; gt; gt;
gt; gt; gt; however, that would involve some more work for me when i input the
players
gt; gt; gt; picks - i.e. i would need to be putting 2 seperate entries for each
game -
gt; gt; i
gt; gt; gt; was hoping i might be able to do it from the 1 original input!
gt; gt; gt;
gt; gt; gt; i could certainly do it that way, but as i say, was hoping i could get
gt; gt; gt; around any extra inputting .....
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; cheers bob!
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; mike
gt; gt; gt;
gt; gt; gt; quot;Bob Phillipsquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Use multiple cells
gt; gt; gt; gt;
gt; gt; gt; gt; A1/B1 - winner
gt; gt; gt; gt; A2/B2 - spread
gt; gt; gt; gt;
gt; gt; gt; gt; then it is imple to use
gt; gt; gt; gt;
gt; gt; gt; gt; =if(B1=A1,IF(B2=A2,2,1),0)
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; HTH
gt; gt; gt; gt;
gt; gt; gt; gt; Bob Phillips
gt; gt; gt; gt;
gt; gt; gt; gt; (remove nothere from email address if mailing direct)
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Blobbiesquot; gt; wrote in message
gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; Excel 2003
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I have a sports picking spreadsheet. It simply compares peoples'
gt; gt; picks
gt; gt; gt; gt; with
gt; gt; gt; gt; gt; the actual result and awards 1 point for getting it right and 0
for
gt; gt; gt; gt; getting
gt; gt; gt; gt; gt; it wrong.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; At a late stage I have decided to award 2 points for getting the
gt; gt; result
gt; gt; gt; gt; gt; exactly right and 1 point for managing to pick the winning
correct
gt; gt; team.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I am struggling with how to get Excel to compare 2 cells and
picking
gt; gt; out
gt; gt; gt; gt; gt; that they both have (or don't have) the same winning team, albeit
gt; gt; gt; gt; different
gt; gt; gt; gt; gt; winning margins.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; eg
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; The result of the game is: Highlanders beat the Bulls by 18
points.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Person A has picked quot;Highlanders by 15 or lessquot; (Cell A1)
gt; gt; gt; gt; gt; The actual result is quot;Highlanders by 16 or morequot; (Cell A2)
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; So I need a formula that says: A1=A2 so 2 points, but if A1 does
not
gt; gt; gt; gt; equal
gt; gt; gt; gt; gt; A2, then quot;Highlandersquot; is in both cell A1 amp; A2, so 1 point!
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Hope that's not too confusing! Many thanks for any answers!
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Mike
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;