Ranking and Percentile Formulas
Rank items fairly and handle ties.
The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.
Create ranking formulas for my data. App: [SPREADSHEET_APP]. Data layout and what to rank: [DATA_LAYOUT]. How ties should be treated: [TIE_RULE]. Groups, if ranking within groups: [GROUPS]. Sample data (fake): [SAMPLE_DATA]. Return formulas with explanations in a table: Goal | Formula | Result on sample data | Notes. Cover: simple rank (RANK, RANK.EQ) and rank averaging ties (RANK.AVG); dense rank (COUNTIFS or SUMPRODUCT approach); rank within group; top N list with ties handled (LARGE with INDEX/MATCH or FILTER/SORT); bottom N; percentile rank (PERCENTRANK) and quartiles (QUARTILE); percent of total and cumulative share; ranking with multiple criteria (primary then secondary, using a helper column or SORTBY); and rank with blanks and zeros excluded. Add: a tie-breaking method using a small secondary value (row number or date) so that ranks are unique when needed; an explanation of the difference between rank and percentile; and visual formatting suggestions (data bars, top 10 highlighting). Provide five test cases including ties and an empty row, with expected output. Note that the ranking method must match the fairness rule people expect.
Fill in before use
Optional: type your details and the prompt above updates. Nothing is saved or sent.
Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.