Excel formula tiering values in data set based on country values are in

Question

I need to be able to use a table and look at one column of values and tier these in the following way:

Top 20% is Tier 1, Tier 2 next 30%, Tier 3 bottom 50%, tier 4 no performance data.

EG if there are 25 values for a country, then the top 20% values will be the top 5 values (25*0.2)

This needs to be done for each country individually, AND the values and number of values for each country differ (see table below).

Country	Rate
Argentina	0.04
Argentina	0.11
Argentina	0.00
Argentina	0.14
Argentina	0.00
Argentina	0.04
Argentina	0.09
Argentina	0.13
Argentina	0.40
Argentina	0.00
Argentina
Argentina
Australia	0.66
Australia	0.30
Australia	0.37
Australia	0.38
Australia	0.37
Australia	0.33
Australia	0.32
Australia	0.12
Australia	0.19
Australia	0.10
Australia	0.11
Australia	0.00
Australia	0.00
Australia	0.00
Austria	1.47
Austria	1.05
Austria	0.50
Austria	0.14
Belgium	0.07
Belgium	0.37
Belgium	0.11
Belgium	0.05
Belgium
Belgium
Brazil	0.39
Brazil	0.31
Brazil	0.74
Brazil	0.35
Brazil	0.15
Brazil	0.60
Brazil	0.24
Brazil	0.22
Brazil	0.23
Brazil	0.22
Brazil	0.29
Brazil	0.14
Brazil	0.24
Brazil	0.05
Brazil	0.10
Brazil
Brazil

E.G:

I have done this manually for Canada:

Country	Rate	Tier
Canada	0.27	2
Canada	0.13	3
Canada	0.06	3
Canada	0.00	3
Canada	0.00	3
Canada	0.00	3
Canada	0.00	3
Canada	1.33	1
Canada	0.76	1
Canada	0.33	1
Canada	0.32	1
Canada	0.28	1
Canada	0.19	2
Canada	0.24	2
Canada	0.23	2
Canada	0.19	2
Canada	0.16	2
Canada	0.16	2
Canada	0.15	3
Canada	0.15	3
Canada	0.13	3
Canada	0.10	3
Canada	0.08	3
Canada	0.15	3
Canada	0.05	3

Any and all help would be massive appreciated :)

Answer 1

Another solution using a table:

=LET(countryData,FILTER([Rate],([Country]=[@Country])*([Rate]<>"")),
    rankedValue,IFNA(PERCENTRANK.INC(countryData,[@Rate]),0),
    XLOOKUP(rankedValue,{0.8;0.5;0},{1;2;3},"",-1))