move the point horizontally if it lie on top of the other point

  • Thread starter Thread starter Sheela
  • Start date Start date
S

Sheela

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
 
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
 
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


.
Click to expand...
 
Back
Top