Calculating Percentage of wins

Highlighted
New Contributor
I’m stumped here. Anyone know of a best practice way to do the following?

I would like to have a column in which I can type a “W” or “L”. (Wins or Losses).

Then I would like to have another column which calculates a running percentage of “W”s from among the total entries of the first column, as they accumulate.
Thank you for any resources or advice.
-Kyle
4 Replies
Highlighted

@Kyle_P19  A sample spreadsheet would be very helpful to make sure we understand what you are trying to do but if I understand correctly you have a column (lets say col A) with "W" and "L" in the rows to indicate wins and losses.  You want to have running totals.  I would recommend just having both, a cell for winning percentage and a cell for losing percentage.  but you can use a another cell with W/L to pick which.

Simple win percentage:

 

 

=COUNTIF(A:A,"W")/COUNTA(A:A)

 

 

and the simple loss percentage could be 1-win % calculated above or:

 

 

=COUNTIF(A:A,"L")/COUNTA(A:A)

 

 

If you want to use a cell (B1) with "W"/"L" to toggle then you can use:

 

 

=COUNTIF(A:A,$B$1)/COUNTA(A:A)

 

 

if you want you can put that in C2 and then in C1 you can add:

 

 

=IF($B$1="W","Win % = ","Loss % = ")

 

 

to help label it.

 

NOTE:  If you use a header with a team name or what not then you either need to change the range from A:A to something like A2:A100 or just subtract 1 in the formula.  If you have other things going on in that column like "Cancelled" or what not, you could change the denominator to explicitly sum the number of "W" and "L" cells (and you could add in "T" if you have ties also:

=COUNTIF(A:A,$B$1)/(COUNTIF(A:A,"W")+COUNTIF(A:A,"L"))

 

Highlighted
Hello,

Using PivotTable is certainly your best option with regards to what you intend to achieve.
Once you insert your PivotTable, you can drag and drop that contain Win or Loss inside the value area. Again, drag and drop the field that contains Win or Loss into the value area. Then, right-click the second field and select Show Value as % of Running Total.

That will give you exactly what you want.

Cheers
Highlighted

Thanks@mtarler 

 

I've used this concept in a few places on the spreadsheet now. Very precise.

Highlighted
you're welcome. glad it is working out for you.