Conditional formatting as a function of rank or average (ideally I learn how to do both)

I have a spreadsheet that I want to conditional format by rank or as a scale of how above or below average they are, top 20% characters in each rank, top 40% etc. with 300 characters or so. Currently I only know how to do conditional formatting as a function of percentage of the highest value, picture included. Is there a way to instead to have it scale based on the rank (top 1 top 2, top 3...median...bottom 30 etc.) instead of the value itself? I need the cells themselves to show the value while the colours/arrows/whatever else to show the rank. 2025-02-02_13h46_18.pngAttachment 888925

Alternatively I want to set the average as the middle of the scale

Hello thatismyfeet and Welcome to Excel Forum.
Perhaps this will be of some help.
For dark green: =d5>=D$2-(D$2-D$1)*0.2
For light green: =d5>=D$2-(D$2-D$1)*0.4
For yellow: =d5>=D$2-(D$2-D$1)*0.6
For pink: =d5>=D$2-(D$2-D$1)*0.8
For red: =d5<D$2-(D$2-D$1)*0.8
The five rules are applied to the range =$D$5:$P$314
Let us know if you have any questions.

Glad that you found a solution that works for you.
It would be helpful to others who may have the same issue if you would post a sample file illustrating how you solved it.
Thank You for marking the thread as 'Solved'.
I hope that you have a blessed day.