Changing values to 0, 1, 2 or 3 based on certain threshold values

Hi all,

I have a large data set consisting of GDP data for about 105 countries. I need to classify each of those countries into an income category (low income, lower-middle income, upper-middle income and high income countries).

The income classification is based on certain thresholds. For example:

Low income country - Between 0 and 2000$ per capita
Lower middle income - Between 2000 and 7000$ per capita
Upper middle income - Between 7000 and 12000$ per capita
High income country - 12000$ and above per capita

This results into three thresholds:
x1 - 2000$
x2 - 7000$
x3 - 12000$

At the moment, all the countries are listed with their full GDP per capita, for example 5000$. Instead, I want all countries to be classified with a 0 (low income), 1 (lower middle income), 2 (upper middle income) or 3 (high income).

So a country with a per capita income of 5000$ should be ranked as 1 (lower middle income), as their income is between the thresholds x1 (2000$) and x2 (7000$).

I need a macro to do this, since I will have do these rankings about 10.000 to 100.000 times for different Thresholds sets. Meaning:
Now the thresholds are 2000$, 7000$ and 12000$, but I will do the same for (Threshold set 1) 2250$, 7250$ and 12250 & (Threshold set 2) 2500$, 7500$ and 12500$ & (Threshold set 3)....& (Threshold set 10.000)...

So, in conclusion:
Problem/Question: Have to change data to 0, 1, 2 and 3s based on threshold values
In need of: A macro that will quickly change the data, since this process has to be repeated at least 10.000 times.
Purpose: I will use all 10.000+ calculations to test which of the sets has the highest correlation with a benchmark threshold set.

Please find attached my Workbook with two sheets (Data & Thresholds).

So far, I have the following macro:

Please Login or Register  to view this content.

The problem with this code is that it only replaces the value with 0, 1, 2 or 3 if the GDP value is exactly the same as the threshold value, whereas I need it to change when (for example) it is smaller than or equal to the threshold value.

Hopefully you can help with this (at least for me) complicated issue.

Thanks!

You could do this with just a formula if you like. This formulas in the attached look up the GDP values in the Rankings look up table on the other sheet. You can change the thresholds in the table and the formulas will update.

Ranking.xlsx

Perfect, this is exactly what I was looking for! Thanks!

Now I will see how to do this the easiest for about 10.000 to 100.000 sets of thresholds and calculate the correlation with the benchmark ranking.

This is at least a great step in the right direction Thanks again..