Hi Rainer

Firstly define some Names to hold the following named values - Low,
High, Midpoint and End
InsertNameDefineName First Refers to $D$1
Repeat above for other names referring to E1,F1 and G1

Let Low be equal to the lowest value you want in your series, in this
case 1 and enter that in D1
Let High be equal to the highest value you want in your series, in this
case 20 and enter that in E1
Let Midpoint be equal to the Midpoint you want in your series, in this
case 0 and enter that in F1
Let End be equal to the Endpoint you want in your series, in this case 1
and enter that in G1 (you can ignore the -1 as the opposite End of the
series will be the negative of the other)

With your data series starting in A1, enter in B1

=(midpoint-(A1-AVERAGE( first,last)) /
(IF(A1<AVERAGE(first,last),first,last)-AVERAGE(first,last))*
(midpoint-end))*(IF(A1<AVERAGE(first,last),-1,1))

copy down as far as there is data in column A

Changing the values of your 4 named values will change the scaling
accordingly.

--
Regards

Roger Govier


"Rainer" wrote in message
...
Hi Guys,
A lengthy question i'll throw out there.

What I've got is 20 numbers each which have a reference range that
applies
to them individually (i.e. 20 numbers each with their own range) I
want these
all graphed on one chart and currently I work the numbers to find the
ratio
from the norm that it is.
For example the range for a number is 1 - 20. What i want, so i can
graph it
decently is that 1 is equal to -1, 20 is equal to +1 and then 10.5(the
mid
value) is equal to 0.

Most importantly this formula to change the numbers is that it will
work for
any number in any range.

Thank you, Rainer

Currently i have a formula which makes 1=-1, and 20=+1 but then the
middle
value does not work properly. I think i'm going about this in the
wrong way.
Anyone have any shortcuts to what i want?