Redistribute values into a range between 200 and 400

[dbischa]

Hi,

I need to be able to take a range of values, that will almost always not be sequential, and redistribute those values such that the cell to the right of each value is given a number between 200 and 400 based on the relative magnitude of the original value. The original values will have a range between 1 and 50, but the minimum and maximum values will usually not always be 1 and 50). It is important that the highest value will be given a new value of 400 and the lowest value is given a value of 200. All values in between need to be evenly distributed between these values based on the spread of the original values. It is also important that the code can deal with any number of original values between 1 and 200 (these are students' scores and there could potentially be between 1 and 200 students).
For example:

original values: new values:
22 ~270
40 ~380
14 200
46 400
39 ~375
32 ~310

The above "new values" (other than 200 and 400) are just guesses at what value they should be.

Any help will be appreciated. Thanks

 

[pgc01]

Hi
Welcome to the board

What are the operations you usually perform when you calculate the students' grades manually?

 

[Andrew Poulsom]

How about?

=((A1-MIN(A$1:A$6))/(MAX(A$1:A$6)-MIN(A$1:A$6))*200)+200

 

[dbischa]

To be honest, I don't know. We usually use a separate software package to do it but the output almost always needs to be manipulated because it just isn't the way we want it to be. I thought it would be a good idea to get a spreadsheet that already contains all of my students' raw data to calculate the 200 to 400 values then I should be able to add some simple excel functions to manipulate the end results using the students' raw data. For example, two students might have an identical score out of 50 but if you look deeper into their data one performs better than the other in one criteria, so the spreadsheet could check for this and adjust as needed. I am confident I could do this last bit on my own but I need some help with code that could do the initial redistribution.

Additionally, the old fashion way was to find a long corridor and just "space" the students out and then give them a number. Not very reliable.

 

[dbischa]

Thanks Andrew! Works great!
I new there must have been something simple, but for the life of me I couldn't work it out.

I ended up putting it into an iferror function because if there are empty rows in the 200 available it doesn't work.

Thanks again.