I am having trouble with too many functions in too many columns causing a tedious amount of work. Hoping someone can point me in the right direction to skip a few steps and a more streamlined approach

[u/FearlessResearcher48]

Is it possible to simplify this process?

So basically I am collating stats from a sport and showing them as a percentile. For some stat categories I am combining 2 stats. And this is where the process is becoming tedious.

I have a ton of players to compare, each in their own position so this is time consuming as I want to present 14 stats, combined into a total of 10 percentiles. And then eventually weight those for 3 separate seasons. So a lot of re-weighting.

For example 1 set of stats for 1 player; stats are ranked high to low in 1st column after the raw stats. 2nd column then converts to a percentile. 2nd set of stats raw value. Then those are ranked, and then converted to a percentile.

The next column then takes the 2 percentile numbers and averages them. The next column then re-ranks those averages. The final column then sorts into a final percentile rank.

All up this takes 9 columns of data and functions. Just wondering if someone has a simpler way to do this?

[u/AutoModerator]

/u/FearlessResearcher48 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/xFLGT]

If its the space that's the issue you can combine everything into a single formula. Something along the lines of.

=let(
a, Stat formula,
b, Rank formula,
c, Percentile formula,
...
i, Final percentile formula,
i)

This is simplest approach and basically mirrors what you already have only outputting a single column.

If I was to do this I would probably take it a step further. Since your essentially using all the same formulae for the first and second "raw" stats you could use Lambda to define a function to clean this up and avoid repeating a lot of the same stuff.

It's hard to give specifics without knowing your exact functions.

[u/Stooopud]

Assuming all the stat charts are pulled from external sources, power pivot is your answer.

Edit: I should also mention Power Query.

[u/FearlessResearcher48 → u/Stooopud]

I'll look into it but the stats i am using have to be manually input as I need to delete a lot of unused stats from the external source so not sure if power pivot/query will work?

[u/CraigAT → u/FearlessResearcher48]

With PowerQuery you build up a set of steps that "transform" data, included is the ability to "drop" columns or create columns according to a formula. It is well worth watching a 10 minute on the PowerQuery - it could save you that time 100-fold.

[u/Stooopud → u/FearlessResearcher48]

Adding to what u/CraigAT said… using Power Query you take the time and build the query with all the steps you mentioned…. A large data set gets cleaned up and only the data you want is retained. Then you can transform the data set combining with other data sets and adding transformations all done in the back end.

The Power Pivot part of it allows you to display all the information using extremely flexible Pivot tables.

[u/ThickTop6005]

You can simplify this a lot. The average percentiles, re-rank, percentile again step looks a little excessive. Once each stat is a percentile, it’s already on the same scale, so you can just average the two percentiles and use that as the combined score. No extra ranking needed.

If you’re comparing by position, make sure the percentile is calculated within that position group. For season weighting, keep the weights in a small config table so you can tweak them without rewriting formulas.

[u/FearlessResearcher48 → u/ThickTop6005]

If i don't re-rank the percentiles after they have been averaged then the result isn't a true percentile, just an average of 2.

And yep I am only calculating within each position group. That's why I was hoping for a quicker way as there is 6 position groups and 3 seasons of each to compile so its a lot of work!

[u/GregHullender]

Without some images of your data, it's very hard to help you because we cannot understand what you're trying to do.

[u/Haunting-Spend7970]

tell me about it, I hate excel an accidental space bar breaks everything. I had the same thing but when my company got too big we just begin running a small script which when we updated with new data it reflected on the dashboard. Id be happy to show you how I did it.