Macro to Loop through range, change values

Hi all,

I have a problem that's been confusing me. I have a few hundred columns that all contain numerical data in rows 2 - 41. What I want to do is convert my data into a nominal scale, replacing the numbers with either a 1, 2, 3, 4. The 1, 2, 3, or 4 designates the data that was contained in that cell as either in the first quartile, second, third, or fourth.

In the attached sheet, you can see that I have the quartiles broken out for each range. So, for example, in column B, all values from the minimum of 1.7 to the first quartile of 322.8 would be replaced with a 1. Everything from the first quartile 322.8 to the second quartile/median of 869.2 would be replaced with a 2, and so on until everything in that column is either a 1, 2, 3, or 4.

My data is always in row 2 through 41 (always 40 items). The only changes are that the different columns will have different numbers and therefore different quartile parameters. I'd like something that would say, "If cell value is between the min and first quartile, then "1", etc."

Can someone point me in the right direction? I'm trying to come up with something myself but I can't solve how to express this. Thanks for reading.

-Student1990


Ranking.xlsx

hi, try this:

Please Login or Register  to view this content.

Thank you! That helped me see how to set this type of thing up.