Calculating fencing scoring in excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2158271%22%20slang%3D%22en-US%22%3ECalculating%20fencing%20scoring%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2158271%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%20I%20am%20trying%20to%20use%20excel%20for%20fencing%20scoring%20and%20I%20am%20struggling%20to%20get%20the%20correct%20answer.%3C%2FP%3E%3CP%3EThe%20scoring%20is%20calculated%20as%20follows%3A%3C%2FP%3E%3CP%3EThe%20bouts%20are%20determined%20by%20how%20many%20athletes%20in%20each%20pool.%3CBR%20%2F%3EThe%20total%20as%20per%20the%20table%20in%20rules%20starts%20at%2019.%3CBR%20%2F%3EWe%20have%20to%20get%20to%2019%20or%20more.%3CBR%20%2F%3EEg.%206%20athletes%20in%20one%20pool.%20An%20athlete%20can't%20fence%20against%20him%2Fherself.%3CBR%20%2F%3ESo%20if%20you%20take%205%20athletes%20x%204%20hits%20per%20bout%20this%20will%20give%20you%20a%20total%20of%2020%20hits%3CBR%20%2F%3E70%25%20of%20the%20total%20(14)%20gives%20you%20250%20points.%3CBR%20%2F%3EEach%20hit%20is%20worth%209%20points.%3CBR%20%2F%3EIf%20an%20athlete%20has%20more%20than%2014%20hits%2C%20say%2016%2C%20you%20add%2018(9pts%20x%202)%20to%20250%20%3D%20268.%3CBR%20%2F%3ELikewise%2C%20subtract%209pts%20per%20hit%20if%20the%20athlete%20scores%20below%2014%20hits.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Janedb_0-1614066426676.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F256930i13CBD2BD72761074%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Janedb_0-1614066426676.png%22%20alt%3D%22Janedb_0-1614066426676.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThis%20is%20my%20spreadsheet%20but%20I%20just%20can't%20get%20the%20scoring%20to%20work%20out%2C%20please%20help!%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Janedb_0-1614066637867.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F256932iA545064BCEB15086%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Janedb_0-1614066637867.png%22%20alt%3D%22Janedb_0-1614066637867.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2158271%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2158467%22%20slang%3D%22en-US%22%3ERe%3A%20Calculating%20fencing%20scoring%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2158467%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F509992%22%20target%3D%22_blank%22%3E%40Janedb%3C%2FA%3E%26nbsp%3BNot%20sure%20I%20understood%20the%20scoring%20sheet%20you%20are%20trying%20to%20create%2C%20but%20perhaps%20the%20attached%20file%20is%20a%20step%20into%20the%20right%20direction.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hi All, I am trying to use excel for fencing scoring and I am struggling to get the correct answer.

The scoring is calculated as follows:

The bouts are determined by how many athletes in each pool.
The total as per the table in rules starts at 19.
We have to get to 19 or more.
Eg. 6 athletes in one pool. An athlete can't fence against him/herself.
So if you take 5 athletes x 4 hits per bout this will give you a total of 20 hits
70% of the total (14) gives you 250 points.
Each hit is worth 9 points.
If an athlete has more than 14 hits, say 16, you add 18(9pts x 2) to 250 = 268.
Likewise, subtract 9pts per hit if the athlete scores below 14 hits.

Janedb_0-1614066426676.png

This is my spreadsheet but I just can't get the scoring to work out, please help!

Janedb_0-1614066637867.png

 

 

 

2 Replies

@Janedb Not sure I understood the scoring sheet you are trying to create, but perhaps the attached file is a step into the right direction.

@Riny_van_Eekelen thank you very much, we are getting closer to the end results.