Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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? |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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? |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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? |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
self-sizing adv.filter criteria range | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Wrap Text Across Columns & Rows | Excel Discussion (Misc queries) | |||
Duplicate Range Names by worksheet | Excel Discussion (Misc queries) | |||
Help with using range names in sum function | Excel Worksheet Functions |