Win / Loss / draw analysis

**olubitkabuu** · 05-21-2015, 06:17 AM

Hello I am trying to do a win/loss/draw analysis for my homework.

I made some trials and I have some performance results about the products in different circumstances.
In every trial, cicumstances a little bit different and not all products are tested in every trial.
I mean, there are 60 different products and in every trial I can only test 15 product in one trial.

for example;
trial A ->
circumstance 1=cold/normal/hot
circumstance 2= humid/normal/dry
Results: Product X=1000 points, product y=1250 points, product Z=1500 points, .....etc

trial b ->
circumstance 1=cold/normal/hot
circumstance 2= humid/normal/dry
Results: Product W=800 points, product X=850 points, product Z=780 points, .....etc

I want to compare how much one product win over other product. But since it would be unfair to compare the product in bad circumstances with the product in good circumstances, I need to compare them only when they shared a trial. For example, If the only trials I did were trial A and trial B that I wrote above, I wouldn't compare W or Y with anything. and Z would average to 920 and X would average to 925. as a result X would win. If the difference in points were ±2 I would consider them draw since the points are too close to each other.

So in the end I should have a table like,
W wins over X 6 wins in 10 trials success rate 60%
X win over Z, 13 wins in 18 trials success rate 72%
P wins over T, 14 wins and 1 draw, in 19 trials ... etc
blabla wins over blabla, blabla wins in blabla trails. success rate %blabla

I don't know if I could express myself properly. It's too complicated for me.

Thanks in advance.

**MrShorty** · 05-21-2015, 08:51 AM

It is difficult to provide specific help with such an open question.

I expect that any solution to this will involve the =AVERAGEIF() or =AVERAGEIFS() functions https://support.office.com/en-us/art...rs=en-US&ad=US If you are unfamiliar with these functions, I would suggest you start by becoming familiar with them.

Then I would recommend that you input your raw data into the spreadsheet in a list format that will be easy to use with these functions. You will probably have columns for Trial, Product X result, Product Y result, and so on, along with any other columns that will be needed for the "criteria ranges" in the AVERAGEIF() or AVERAGEIFS() functions.