I am attempting to create a spreadsheet to keep track of teams in our tournament.
Data include the team name, owner, handicap and the score of the game.
A B C D E F
Team Owner Handicap Score winner owner
1 Broncos Mary 1 55
2 Monsters Bob 40
3
4 Aliens Joe 99
5 Cowboys Sandy 70
6
7 Bears Ed 48
8 Killers Jim 9 50
9
10 Scamps Sam 6 54
11 Eagles Patty 77
12
13 Bucks Al 8 58
14 Dolphins Kate 50
The handicap is subtracted from the final score. There are five possible outcomes:
In the first scenario, the Broncos are the favorite and score enough to win even after their handicap is deducted.
In the second scenario, neither team is handicapped, so the Aliens win the game.
In the third scenario, the Killers outscore the Bears by 2, but the Killers have a 9 point handicap, making the Bears the winners.
In scenario four, the Eagles win the game outright, even before the Scamps' handicap is added.
In scenario five, the Bucks score 8 more points than the Dolphins, meaning that the score is effectively tied after subtracting the Buck's handicap. In this case, the rules state that the team with the handicap is declared the winner.
I need an equation that will take the score of each team, subtract the handicap of the team that has one (including the possibility that neither team has one) and then put the winning team and their owner in rows E and F, respectively.
I tried an IF equation like so:
=IF((D1-C1)>(D2-C2),A1,A2). That works in all the scenarios except #3 and 5, but the tie in 5 throws it off. So I changed it to:
=IF((D1-C1)>=(D2-C2),A1,A2). That only works in #5 if the team with the handicap is on the upper row.
Thoughts: There has to be a way to do this. Can an IF equation be used to determine which team has the handicap?
In plain language:
IFBLANK C2 then IF D1-C1 >=D2-C2 would work.
IFBLANK C1 then IF D2-C2>=D1-C1 would work.
How do you combine these into a working formula?
And just to add on an extra layer: How would you set conditional formatting so that if the team without the handicap wins because the handicap lowered the other team's score below theirs (scenario #3) to not only put that team and owners names in columns E and F but change the text color to red?
Whew. Thanks in advance to whoever takes a stab at this.
Data include the team name, owner, handicap and the score of the game.
A B C D E F
Team Owner Handicap Score winner owner
1 Broncos Mary 1 55
2 Monsters Bob 40
3
4 Aliens Joe 99
5 Cowboys Sandy 70
6
7 Bears Ed 48
8 Killers Jim 9 50
9
10 Scamps Sam 6 54
11 Eagles Patty 77
12
13 Bucks Al 8 58
14 Dolphins Kate 50
The handicap is subtracted from the final score. There are five possible outcomes:
In the first scenario, the Broncos are the favorite and score enough to win even after their handicap is deducted.
In the second scenario, neither team is handicapped, so the Aliens win the game.
In the third scenario, the Killers outscore the Bears by 2, but the Killers have a 9 point handicap, making the Bears the winners.
In scenario four, the Eagles win the game outright, even before the Scamps' handicap is added.
In scenario five, the Bucks score 8 more points than the Dolphins, meaning that the score is effectively tied after subtracting the Buck's handicap. In this case, the rules state that the team with the handicap is declared the winner.
I need an equation that will take the score of each team, subtract the handicap of the team that has one (including the possibility that neither team has one) and then put the winning team and their owner in rows E and F, respectively.
I tried an IF equation like so:
=IF((D1-C1)>(D2-C2),A1,A2). That works in all the scenarios except #3 and 5, but the tie in 5 throws it off. So I changed it to:
=IF((D1-C1)>=(D2-C2),A1,A2). That only works in #5 if the team with the handicap is on the upper row.
Thoughts: There has to be a way to do this. Can an IF equation be used to determine which team has the handicap?
In plain language:
IFBLANK C2 then IF D1-C1 >=D2-C2 would work.
IFBLANK C1 then IF D2-C2>=D1-C1 would work.
How do you combine these into a working formula?
And just to add on an extra layer: How would you set conditional formatting so that if the team without the handicap wins because the handicap lowered the other team's score below theirs (scenario #3) to not only put that team and owners names in columns E and F but change the text color to red?
Whew. Thanks in advance to whoever takes a stab at this.