Formula for Employee win loss scores

[RachelMads02]

In the workbook below I'm looking to find a formula in column AD & AE that will show how many times a specific employee of the associate level wins and loses against senior level employees in their test scores.

I'm also looking to find out how many times the same employee when ranked as a senior level employee beats associate level employees in columns AI & AJ.

Take for example the employee Anthony:

When ranked as an associate level employee (Columns AD & AE)
1. How many times has Anthony won when competing against a senior level employee (not a specific senior level employee, but senior level employees as a whole)? Cell X30 shows that Anthony beat a senior level employee in 1991 so AD4 will display 1.

2. How many times has Anthony lost when competing against a senior level employee (not a specific senior level employee, but senior level employees as a whole)? Cells X20 & X29 show that Anthony has lost in 1989 and 1991 so AE4 will display 2.

When ranked as an Senior level employee (Columns AI & AJ)
1. How many times has Anthony won when competing against an associate level employee (not a specific associate level employee, but associate level employees as a whole)? Cell V26 shows that in 1990 Anthony won so cell AI will display 1.

2. How many times has Anthony lost when competing against a associate level employee (not a specific associate level employee, but associate level employees as a whole)? Cells V4, V31, V32, V33, V34 all show that Anthony has lost so AJ4 will display 5.

My only requirements:
Cells in AD,AE, AI, & AJ remain blank if there aren't wins or losses and that they are able to take the increase in the range T:AA as time moves forward.

Big thanks in advance.

[JohnTopley]

In AD4

=SUMPRODUCT(($X$4:$X$100=$AC4)*($Z$4:$Z$100>$Y$4:$Y$100))

in AE4

=SUMPRODUCT(($X$4:$X$100=$AC4)*($Z$4:$Z$100<$Y$4:$Y$100))

In AI4

=SUMPRODUCT(($V$4:$V$100=$AH4)*($Y$4:$Y$100>$Z$4:$Z$100))

in AJ$

=SUMPRODUCT(($V$4:$V$100=$AH4)*($Y$4:$Y$100<$Z$4:$Z$100))

You can "hide" the zeros by CUSTOM format as 0;;;@

Extend 100 to a sensible maximum

[Glenn Kennedy]

Or using COUNTIFS:

AD$ =COUNTIFS($X:$X,$AC4,$AA:$AA,"<0")

AE4 =COUNTIFS($X:$X,$AC4,$AA:$AA,">0")

AF4 =IFERROR(100*AD4/SUM(AD4:AE4),"")

and similarly (with > and < reversed) for the other columns.

[JeteMc]

The only additional thought I have is to change the range T3:AA135 into a table. The formulas are then adjusted so that they reference the expanding fields in the table.
For an example the formula for Associate Level winning would be:

Please Login or Register  to view this content.

Let us know if you have any questions.

[RachelMads02]

how do you keep the cells blank if the result is 0? I'd prefer them to remain empty unless there is a win or loss

[Glenn Kennedy]

Dunno who you were "talking" to. But as JT said, apply this as custom formatting: 0;;;@

[RachelMads02]

Thank you very much, to all three of you!

[Glenn Kennedy]

Thanks for the feedback, Rachel.

[RachelMads02]

in my final draft of the workbook i have two sheets such as the table that Jete uses from range T:AA is on one page while AC:AK is on a separate. When entering the formula as shown in the workbook it returns a 0 value in all cells where a win or a loss does not occur. How can i remove that?

[Glenn Kennedy]

Without seeing what you have done... how can we tell?? As long as the formulae are pointing to the correct sheet, it should all be OK.

[RachelMads02]

Okay scratch that, i fixed it. However, let me ask about the win % column. How can i alter the formula to allow for 0% when an employee has losses, but no wins? I still want the column to allow for blank cells when an employee doesn't have any wins or losses (ergo they haven't competed yet).

[JeteMc]

Glen's formula does that:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Glenn Kennedy]

Scrap the fancy formatting. calculate the % win as:

=IFERROR(100*COUNTIFS($X:$X,$AC4,$AA:$AA,"<0")/(COUNTIFS($X:$X,$AC4,$AA:$AA,"<0")+COUNTIFS($X:$X,$AC4,$AA:$AA,">0")),"")

and then variants of this for the individual scores:

=IF(ISNUMBER(AF4),COUNTIFS($X:$X,$AC4,$AA:$AA,"<0"),"")

[RachelMads02]

If at a later time i convert T:AA into a table will it have any negative effects on the formulas you have shown?

In column AD i only want numbers >0 to show. Everything else remains empty.

In column AE i only want numbers >0 to show. Everything else remains empty.

In column AF i only want percents shown when an employee has competed albeit 0 if they have competed but only have lost, or the difference between when they have competed and have either won and loss or only won. Everything else remains empty.

Column AF in the most recent reply by Glenn shows correctly, but columns AD & AE indicate zeros which i would prefer to remain empty.

*one final question, what can cause scrolling from cell to cell to lag? My document has many sheets, but the overall size of the workbook is less than a single MB.

[Glenn Kennedy]

Now you have me confused. What is different from what you now want with what is in Post 6?

it would also be helpful, if you would address your comments to the person to whom (if any of us...) you are specifically referring....

[RachelMads02]

Now you have me confused. What is different from what you now want with what is in Post 6?

it would also be helpful, if you would address your comments to the person to whom (if any of us...) you are specifically referring....

I figured it out. There were different replies that contained different formulas so i used a combination of them to solve the problem. Everything is good other than the lag issue

[Glenn Kennedy]

I'm now away for the night. I didn't see much lab. Can you post your final sheet?