Altering a range of numbers but keeping a defined mid point

[sniglet]

Ideally this would be done in a single cell, without referring to another table of numbers. The major problem as I see it is having a non-linear conversion ...

Say I have three defined values; a MIN, MAX and an AVERAGE. Is it possible to change this range of numbers to have a new MIN and MAX value, but retaining the AVERAGE and squashing/stretching the number ranges above and below e.g.:

Original range	New range defined values	What I need
48	31	31
49		33.89
50		36.78
51		39.67
52		42.56
53		45.45
54		48.34
55		51.23
56		54.12
57	57	57
58		62
59		67
60		72
61		77
62		82
63	87	87

The result of the third column is what I need from the output value e.g. if I input the value 61, the value returned is 77.

Help!

Nick

[sniglet]

So, I've had a quick go at coming up with something that may spur a few ideas ...

Please Login or Register  to view this content.

The problem is, what can I use in the (???) operation to make this work. Not even sure if this is the direction I should take.

[MrShorty]

Within roundoff error, it looks to me like you have a simple proportionality (newmin-ave)/(oldmin-ave)=(newx-ave)/(oldx-ave), then solve for newx. When above the average, replace the mins with max.

[sniglet]

That works perfectly. I've attached a spreadsheet of the working result, for anyone that's interested.

Thanks!
Nick