Success Rate Percentage

%3CLINGO-SUB%20id%3D%22lingo-sub-1281678%22%20slang%3D%22en-US%22%3ESuccess%20Rate%20Percentage%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1281678%22%20slang%3D%22en-US%22%3E%3CP%3ESo%2C%20I%20am%20creating%20a%20little%20spreadsheet%20for%20my%20local%20e-racing%20team%20for%20some%20friendly%20competition.%20Lots%20of%20people%20have%20family%20commitments%20etc%20that%20means%20not%20everyone%20will%20be%20able%20to%20compete%20in%20every%20race%2C%20and%20some%20in%20very%20few%20at%20all.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20made%20a%20position%20ranking%20column%20to%20total%20up%20all%20the%20points%20(10%20for%201st%20-%201%20for%2010th)%20and%20see%20who%20is%20first%2C%20second%20third%20overall%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20for%20those%20that%20are%20not%20competing%20often%20I%20would%20like%20a%20'success%20rate'%20column%20to%20indicate%20how%20well%20they%20have%20on%20average%20performed%2C%20regardless%20of%20the%20number%20of%20races%20they%20have%20completed.%20For%20example%2C%20if%20one%20racer%20competes%20in%20only%20one%20race%20but%20wins%2C%20they%20get%20a%20success%20rate%20of%20100%25.%20If%20another%20racer%20races%20twice%20and%20gets%20one%20first%20place%20and%20one%20last%20place%2C%20they%20get%2050%25%2C%20and%20so%20on.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%20on%20how%20I%20might%20be%20able%20to%20do%20this%3F%20Thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1281678%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1281723%22%20slang%3D%22en-US%22%3ERe%3A%20Success%20Rate%20Percentage%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1281723%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F609397%22%20target%3D%22_blank%22%3E%40Pete_Attrill%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20long%20as%20we're%20just%20playing%20with%20ideas%2C%20I%20think%20I'd%20modify%20your%20%22success%20rate%22%20calculation%20to%20factor%20in%20some%20kind%20of%20%22participation%20percentage%22%20or%20%22participation%20weighting.%22%20You%20don't%20want%20to%20incentivize%20a%20person--or%20course%20I%20realize%20we're%20talking%20a%20friendly%20family%20competition%20here%2C%20so%20this%20would%20never%20happen--but%20you%20don't%20want%20to%20incentivize%20a%20person%20to%20participate%20once%2C%20win%2C%20and%20then%20stop%20participating.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20to%20ideas%20on%20how%20to%20do%20this%2C%20a%20database%20that%20gives%20each%20person%20an%20ID%2C%20and%20then%20for%20each%20date%2C%20their%20finishing%20place%2C%20one%20row%20per%20person%20per%20race...that%20would%20be%20a%20place%20to%20start.%20I%20think%20you%20could%20develop%20a%20Pivot%20Table%20to%20summarize%20the%20results.%20Are%20you%20familiar%20with%20that%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

So, I am creating a little spreadsheet for my local e-racing team for some friendly competition. Lots of people have family commitments etc that means not everyone will be able to compete in every race, and some in very few at all.

 

I have made a position ranking column to total up all the points (10 for 1st - 1 for 10th) and see who is first, second third overall etc.

 

However, for those that are not competing often I would like a 'success rate' column to indicate how well they have on average performed, regardless of the number of races they have completed. For example, if one racer competes in only one race but wins, they get a success rate of 100%. If another racer races twice and gets one first place and one last place, they get 50%, and so on.

 

Any ideas on how I might be able to do this? Thanks.

1 Reply
Highlighted

@Pete_Attrill 

 

As long as we're just playing with ideas, I think I'd modify your "success rate" calculation to factor in some kind of "participation percentage" or "participation weighting." You don't want to incentivize a person--or course I realize we're talking a friendly family competition here, so this would never happen--but you don't want to incentivize a person to participate once, win, and then stop participating.

 

As to ideas on how to do this, a database that gives each person an ID, and then for each date, their finishing place, one row per person per race...that would be a place to start. I think you could develop a Pivot Table to summarize the results. Are you familiar with that?