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?

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?

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?

Hi hot dogs

That's a very nice solution, that works if the data is in an ordered
series.
But unfortunately, it fails if the data is in random order
Try it with 1 to 20 in random order in cells A1 to A20 and you will see
what I mean.
Also, the top of the range for scaling, depends upon the count of data
items, rather than within a scale of 1-20 as the OP requested.
--
Regards

Roger Govier


"hot dogs" wrote in message
...
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?