If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
move the point horizontally if it lie on top of the other point
I am trying to plot a line chart with a primary axis as
category X axis (there are 4 categories) and a value Y axis. I don't want any line to connect the points. I just have symbols to show the values. The chart works fine. The problem is in each category there are some same values and they show as a single point on the graph, because one lie on top of the other. How do move the points horizontally if there is more than one point at the same place. I want to show that there are more than one points. Thanks Sheela |
#2
|
|||
|
|||
move the point horizontally if it lie on top of the other point
You can simulate the effect with a XY Scatter chart. Here's how.
Suppose there are two data sets in A4:B18 (I know you have four, and I'm sure you can adapt these instructions for 2 to your own needs). A B 1 1 1 2 1 2 2 3 2 3 3 4 3 5 4 5 5 6 5 7 5 7 8 8 10 Suppose we decide to locate the first category at around x=5, the other at around x=10. Enter these numbers in R4:S4. Also, suppose we set the gap for otherwise overlapping points at 0.2. Enter this number in R2. Now, in R5, enter the formula =$R$4+$R$2*COUNTIF($A$5:A5,A5). Copy R5 to R6:R15. In S5, enter =$S$4+$R$2*COUNTIF($B$5:B5,B5). Copy S5 to S6:S18. In U5, enter the formula =R5-$R$2*(COUNTIF($A$5:$A$15,A5)-COUNTIF($A $5:A5,A5)+1). Copy U5 to U6:U15. In V4, enter =A4. Copy V4 to V5:V15 In X5, enter =S5-$R$2*(COUNTIF($B$5:$B$18,B5)-COUNTIF($B$5:B5,B5)+1). Copy X5 to X6:X18. In Y4, enter =B4. Copy Y4 to Y5:Y18. Plot U4:V15 in a XY Scatter chart. Then, select and drag X4:Y18 onto the XY Scatter chart. In the resulting dialog box, ensure you specify that the data are in columns and that the first column contains x values. Adjust R2, R4, S4, and format the chart for aesthetic appeal. -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I am trying to plot a line chart with a primary axis as category X axis (there are 4 categories) and a value Y axis. I don't want any line to connect the points. I just have symbols to show the values. The chart works fine. The problem is in each category there are some same values and they show as a single point on the graph, because one lie on top of the other. How do move the points horizontally if there is more than one point at the same place. I want to show that there are more than one points. Thanks Sheela |
#3
|
|||
|
|||
move the point horizontally if it lie on top of the other point
Thanks so much. This helps a lot.
sheela. -----Original Message----- You can simulate the effect with a XY Scatter chart. Here's how. Suppose there are two data sets in A4:B18 (I know you have four, and I'm sure you can adapt these instructions for 2 to your own needs). A B 1 1 1 2 1 2 2 3 2 3 3 4 3 5 4 5 5 6 5 7 5 7 8 8 10 Suppose we decide to locate the first category at around x=5, the other at around x=10. Enter these numbers in R4:S4. Also, suppose we set the gap for otherwise overlapping points at 0.2. Enter this number in R2. Now, in R5, enter the formula =$R$4+$R$2*COUNTIF ($A$5:A5,A5). Copy R5 to R6:R15. In S5, enter =$S$4+$R$2*COUNTIF($B$5:B5,B5). Copy S5 to S6:S18. In U5, enter the formula =R5-$R$2*(COUNTIF($A$5:$A$15,A5)- COUNTIF($A $5:A5,A5)+1). Copy U5 to U6:U15. In V4, enter =A4. Copy V4 to V5:V15 In X5, enter =S5-$R$2*(COUNTIF($B$5:$B$18,B5)-COUNTIF ($B$5:B5,B5)+1). Copy X5 to X6:X18. In Y4, enter =B4. Copy Y4 to Y5:Y18. Plot U4:V15 in a XY Scatter chart. Then, select and drag X4:Y18 onto the XY Scatter chart. In the resulting dialog box, ensure you specify that the data are in columns and that the first column contains x values. Adjust R2, R4, S4, and format the chart for aesthetic appeal. -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... I am trying to plot a line chart with a primary axis as category X axis (there are 4 categories) and a value Y axis. I don't want any line to connect the points. I just have symbols to show the values. The chart works fine. The problem is in each category there are some same values and they show as a single point on the graph, because one lie on top of the other. How do move the points horizontally if there is more than one point at the same place. I want to show that there are more than one points. Thanks Sheela . |
#4
|
|||
|
|||
move the point horizontally if it lie on top of the other point
You are welcome. Glad to be of help.
-- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Thanks so much. This helps a lot. sheela. {snip} |
Thread Tools | |
Display Modes | |
|
|