Rainer,

I think i have acheived what you require, if you set up the following on a
blank sheet to see how it works you should be able to apply it to your sheet.

Column A contains your list, i have entered 1 to 20 in cells 1 to 20.

in B1 enter 0

in B2 enter =1/((COUNT(A1:A30))-1)
where A30 is the furthest cell that the list can go down to, you could enter
A100 for example. if your list starts on a row further down the sheet have
that row number instead of A1, for example you might have A12:A100 for your
range.

in cell B3 enter =IF(A3="","",($B$2*(ROW()-1)))
Fill this formula down the length of your list, in my case down to row 30.
The -1 of ROW()-1 applies because my list starts in row 1 if your list
starts in row 4 for example enter ROW()-4 in your formula.

in cell C1 enter =IF(A1="","",((B1*2)-1))
Fill this formula down the length of your list, in my case down to row 30.

Now when ever you enter a list from cell A1 down to cell A30 in my case (or
you could stop the list at row 10 if required), column C returns the number
you require between 1 and -1. Now hide column B.

If you set this up on a new sheet as i describe you will be able to see how
it works, then you can apply this to your sheet, when applying it to your
sheet you will probably have to alter some parts of the formulae, but this
should be clear once you have it how i have it at the moment.

"Rainer" wrote:

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?