Hi, I'm new to QV and currently struggling with a background colour issue.
I am looking at a spreadsheet of football statistics and want to standardise performance for differing amount of minutes played. E.g. some players play X number of minutes with X number of shots, whilst others have played Y number of minutes with X number of shots.
I want to compare performance based on amount of shots per 90 minutes played and I have done so using the formula:
Num(Sum(Shots)/Sum(Minutes Played/90), '##.00')
This worked how I wanted and I have my standardisation. Now my issue is that I want colour code these for visual representation. e.g. top 25% coloured blue, 2nd 25% colour green, 3rd 25% coloured white and bottom 25% coloured red. I managed to figure out a formula to colour code based on the If and Fractile function but it calculated the code based on the initial base value (Total Shots) rather than the standardised value (Shots / 90 minutes).
The formula I got to work was.
If(Sum(Shots)>Fractile(Total Shots, 0.75), Color(1), If(Sum(Shots)>Fractile(Total Shots, 0.5), Color(2)), If(Sum(Shots)>Fractile(Total Shots, 0.25), Color(3)), If(Sum(Shots)<Fractile(Total Shots, 0.25), Color(4)))
But it needs changing to take into account the Shots/90 value rather than total shots.
Unfortunately, the data is quite sensitive so can't provide an example on the set I am using but any help is much appreciated.
