Hello all!
I've got a small spreadsheet to keep track of the decks I have for a trading card game, and how many wins they have when played against my other decks. I play three rounds each against each combination of decks. The sheet is laid out such that both the first row and first column contain the names of the decks, while the resulting cells of the table below have a 1, 2, or 3 depending on how many times that deck has won against its competitor. This, in turn, means the two decks I am testing against each other will always add up to 3, to show a win/loss record (3-0, 2-1, and vice versa).
I am using a conditional formatting rule seen below:
=SUM(B2,INDIRECT(ADDRESS(COLUMN(B2),ROW(B2))))=3
in order to color the "finished" combinations green (the decks that have played 3 rounds against each other).
I am wanting to use a similar formula in order to calculate the win percentages of the decks. However, this gets thrown off as soon as a number enters the range I am testing against in which the full 3 rounds have not been completed
For example, let's say I have Decks One, Two, and Three. Deck One can't play against itself, so that cell is always blank and colored red. Deck One plays three rounds against Deck Two, winning all three. This results in:
However, once I play ONE round between Deck One and Deck Three and Deck One wins, the table becomes:
In short, I want the Win % to ONLY calculate based on "finished" rounds (rounds in which SUM(cell,INDIRECT(ADDRESS(COLUMN(cell),ROW(cell))))=3)
I have attempted using SUM(IF()) and SUMIF() in conjunction with COUNT(IF()) and COUNTIF() to no avail. If anyone could provide any guidance I would greatly appreciate it!
I've got a small spreadsheet to keep track of the decks I have for a trading card game, and how many wins they have when played against my other decks. I play three rounds each against each combination of decks. The sheet is laid out such that both the first row and first column contain the names of the decks, while the resulting cells of the table below have a 1, 2, or 3 depending on how many times that deck has won against its competitor. This, in turn, means the two decks I am testing against each other will always add up to 3, to show a win/loss record (3-0, 2-1, and vice versa).
I am using a conditional formatting rule seen below:
=SUM(B2,INDIRECT(ADDRESS(COLUMN(B2),ROW(B2))))=3
in order to color the "finished" combinations green (the decks that have played 3 rounds against each other).
I am wanting to use a similar formula in order to calculate the win percentages of the decks. However, this gets thrown off as soon as a number enters the range I am testing against in which the full 3 rounds have not been completed
For example, let's say I have Decks One, Two, and Three. Deck One can't play against itself, so that cell is always blank and colored red. Deck One plays three rounds against Deck Two, winning all three. This results in:
However, once I play ONE round between Deck One and Deck Three and Deck One wins, the table becomes:
In short, I want the Win % to ONLY calculate based on "finished" rounds (rounds in which SUM(cell,INDIRECT(ADDRESS(COLUMN(cell),ROW(cell))))=3)
I have attempted using SUM(IF()) and SUMIF() in conjunction with COUNT(IF()) and COUNTIF() to no avail. If anyone could provide any guidance I would greatly appreciate it!
