SUM Cells Using INDIRECT(ADDRESS()) on Same Sheet?

ckouns

New Member
Joined
Jan 12, 2019
Messages
1
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!
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Watch MrExcel Video

Forum statistics

Threads
1,108,579
Messages
5,523,695
Members
409,531
Latest member
wo1f

This Week's Hot Topics

Top