Ether
April 12, 2017, 12:34pm
1
*Here’s all the raw score data for weeks 1 thru 6 in one large sheet.

You can do things like sorting to find the highest scores in the various columns etc.

You can create new columns with sums or logic and sort on that.

See this thread for some examples.

Or you can export selected columns from the spreadsheet into a data file to do some number crunching.

If you are viewing this thread for the first time, please scroll through the posts for links to revised versions.

Ether
April 12, 2017, 1:45pm
2
Here’s another simple example: Suppose you want to know how many alliances scored at least 40 auto fuel points.

Just sort on Column AE and you’ll see there were 3 red alliances that did so.

Then sort on Column BM and you’ll see there were 22 blue alliances that did so.

You can look at Column A to see which matches these scores occurred in.

```
2017ilch_f1m1 56
2017ilch_sf1m2 56
2017ilch_f1m3 55
2017ilch_f1m2 51
2017ilch_qf1m1 51
2017ilch_sf1m1 49
2017nvlv_qm15 48
2017cada_qf1m2 46
2017mokc_qf1m2 45
2017ilch_qm49 44
2017ilch_qm36 44
2017ilch_qm20 43
2017ilpe_sf1m1 43
2017necmp_qf1m1 42
2017casf_sf1m1 42
2017cada_sf1m1 41
2017nvlv_sf2m1 40
2017abca_qf1m1 40
2017necmp_qm96 40
2017mokc_sf1m2 40
2017nytr_qf1m1 40
2017ohcl_qm23 40
2017necmp_qm85 40
2017iscmp_qm66 40
2017alhu_qm46 40
```

Ether
April 12, 2017, 3:56pm
3
I just uploaded RevB ](https://www.chiefdelphi.com/media/papers/download/5028 ), which adds one extra column (“CB”) at the end containing the event week number.

This allows you to easily incorporate the week number into queries.

For example, you might want to compare the average alliance score in week1 vs the average alliance score in week6 etc etc.

Ether
April 12, 2017, 6:27pm
4
Another example: To find the Top12 highest losing alliance scores, create a column at the end with this function: =IF(BT1<BX1,BT1,BX1)…

…then sort the data by that column, descending

```
2017necmp_qf1m1 470 frc131 frc501 frc6763 515 frc1073 frc2168 frc195 470
2017necmp_sf1m2 466 frc558 frc125 frc4564 506 frc1073 frc2168 frc195 466
2017njski_sf1m1 496 frc1676 frc1279 frc223 465 frc1923 frc75 frc25 465
2017necmp_qf2m2 460 frc1153 frc3464 frc133 515 frc4564 frc125 frc558 460
2017necmp_qf4m2 457 frc95 frc6329 frc230 484 frc5813 frc3719 frc1519 457
2017nvlv_sf1m1 456 frc842 frc649 frc5199 508 frc968 frc118 frc148 456
2017incmp_qf3m2 453 frc1741 frc1720 frc4926 453 frc3940 frc1018 frc1024 453
2017mrcmp_qf3m1 452 frc219 frc203 frc1640 460 frc3929 frc1807 frc2590 452
2017pncmp_f1m2 471 frc2471 frc2046 frc1595 452 frc1318 frc3238 frc3663 452
2017incmp_qf1m1 450 frc5188 frc6498 frc1529 450 frc3147 frc135 frc234 450
2017necmp_qf4m3 450 frc95 frc6329 frc230 457 frc5813 frc3719 frc1519 450
2017pncmp_f1m1 484 frc2471 frc2046 frc1595 450 frc1318 frc3238 frc3663 450
```

Ether
April 13, 2017, 5:51pm
5
*Cosmetic changes to make the columns less bulky and reduce the file size.

(check my most recent post in this thread for link to updated revisions of 2017weeks1thru6 data)

```
"frc" removed from the Team Numbers
in Columns BU..BW and BY..CA
"Mobility" replaced with "M"
"None" replaced with blank
"TRUE" replaced with "T"
"FALSE" replaced with "F"
"ReadyForTakeoff" replaced with "RFT"
"Unknown" replaced with "U"
```

Ether
April 14, 2017, 9:00pm
6
Here’s a not-very-useful, but nevertheless fun, question to ask the database:

How many alliance scores were greater than the sum of that alliance’s team numbers?

Solution:

```
Step1
Put the following formulas into cells CC1 thru CE1:
CC1 =IF(BT1>SUM(BU1:BW1),1,0)
CD1 =IF(BX1>SUM(BY1:CA1),1,0)
CE1 =CC1+CD1
Step2
Copy cells CC1 thru CE1 all the way down
Step3
Sort the data by Column CC, descending
The answer will be at the top of Columns CC1 thru CE1
```

Combining Excel’s “sort” command and “=if” function allows you to easily ask the database many different questions.
*
*

Ether
April 15, 2017, 10:54am
7
```
Top35 lowest winning scores weeks1thru6
Match score blue1 blue2 blue3 score red1 red2 red3 week score
2017waspo_qm1 8 3575 5468 6129 0 2149 4125 3712 1 8
2017cama_qm3 25 6657 3256 2367 40 1388 5852 6619 2 40
2017mxtl_qm6 0 6676 6694 6708 41 4731 3472 3510 1 41
2017gagai_qm10 40 6710 6712 4235 45 6716 6325 4026 1 45
2017gacol_qm22 40 6750 4730 5848 50 6349 4701 1414 3 50
2017ausp_qm8 45 6449 4529 5564 50 6770 5333 6187 3 50
2017mabri_qm19 45 97 5265 6529 50 4151 6301 5844 2 50
2017flor_qm15 55 6322 4562 6473 41 6609 2425 5283 2 55
2017mxtl_qm26 45 4731 4723 4685 55 3527 6170 5133 1 55
2017njfla_qm30 45 2016 5310 5992 55 1923 1279 2600 2 55
2017mawor_qm28 47 5969 6723 1965 57 6367 6337 1277 2 57
2017vabla_qm8 55 6194 5954 3494 61 1599 3072 5279 1 61
2017scmb_qm4 75 3651 6693 1287 45 5022 4074 5020 1 75
2017mndu2_qm8 48 5172 3267 4607 75 2861 3026 5348 1 75
2017mxtl_qm5 75 3794 4584 6607 50 6170 6605 5932 1 75
2017gagai_qm2 80 6712 1746 3344 40 1002 1648 832 1 80
2017flwp_qm10 45 5196 5557 6038 80 4517 6404 59 1 80
2017gadal_qm25 50 1414 3344 6471 80 6139 4459 5109 2 80
2017gadal_qm9 80 4459 4516 6341 50 4193 3329 3318 2 80
2017mxtl_qm44 50 6608 3480 4584 80 5133 5750 5887 1 80
2017cama_qm12 76 5677 5852 2643 80 2761 2813 6059 2 80
2017ausp_qm2 70 6063 5988 6187 81 4729 6447 6509 3 81
2017mxtl_qm1 35 2283 5887 5344 85 5948 5716 4782 1 85
2017misou_qm4 85 5619 6136 247 45 5756 453 6013 1 85
2017mxtl_qm46 45 6348 6708 6319 85 6606 4371 4775 1 85
2017gagai_qm36 50 3265 3344 6325 85 3573 1795 4749 1 85
2017gush_qm6 85 6441 6374 6766 50 6360 6394 6375 2 85
2017mxtl_qm14 85 4584 6606 3933 50 6608 6607 4262 1 85
2017scmb_qm3 55 4847 1319 5063 85 343 3976 5898 1 85
2017mike2_qm6 60 2612 5487 5251 85 468 5713 5926 2 85
2017ncgre_qm6 60 1225 3331 4767 85 6639 3661 3845 2 85
2017mxtl_qm13 65 3794 5344 5716 85 6348 3510 3480 1 85
2017gagai_qm6 85 4112 6710 6325 80 3318 5900 3091 1 85
2017milak_qm16 80 6011 6119 5448 85 3875 4453 6002 1 85
2017miwat_qm16 80 2673 5090 4130 85 5225 5662 6610 3 85
```

Put this formula in Cell CC1:

CC1 =IF(BT1>BX1,BT1,IF(BX1>BT1,BX1,9999))

… then copy that formula all the way down.

Sort the data by that column, ascending.

when I click on your link all I get is a page that says: error

Ether
April 15, 2017, 12:44pm
9
You probably clicked on an expired link.

Click on the link in the most recent post in this thread.

If that doesn’t work, please let me know.