Josep -
The circle's easy. In column A, list angles from 0 to 360 (or 90 if
you're just doing the first quadrant). I skipped 2, so my numbers went
0, 2, 4, etc. In column B put the formula for X and in column C put the
formula for Y
Cell B2 (X): =COS(A2/180*PI())
Cell C2 (Y): =SIN(A2/180*PI())
Fill these down as far as you have angles in column A. The top few rows
look like this:
Angle X Y
0 1 0
2 0.999390827 0.034899497
4 0.99756405 0.069756474
6 0.994521895 0.104528463
8 0.990268069 0.139173101
10 0.984807753 0.173648178
Select the two columns with X and Y data and make an XY Scatter chart,
using a line with no markers style.
Which value do you want an arrow for? If you do each degree, you won't
be able to resolve the arrows. Do you need an arrowhead?
If there's one point you need a line from the origin to the point, set
up this range in your sheet:
0 0
<x> <y>
(whatever your x and y values are for the point). Add an XY Scatter
series to the chart, with data in columns, and it will start at 0,0 and
end at x,y.
There are two ways to get an arrow. The easier way is just to add a few
segments to the above line from the origin to the point. Since we used
columns A-C for the circle, let's move to column E-G for the arrow data.
I assume X and Y are based on an angle, as above. I'll use 30 degrees
for this illustration.
[E] [F] [G]
[1] Angle X Y
[2] 0 0 0
[3] 30 0.8660 0.5
[4] 32 0.7632 0.4769
[5] 30 0.8660 0.5
[6] 28 0.7947 0.4225
Here are the formulas in these cells:
E4: =E3+2, E5: =E3, E6: =E3-2 (so you only have to change E3)
F3: =COS(E3/180*PI()) G3: =SIN(E3/180*PI())
(copy F3:G3 and paste into F5:G5)
F4: =(COS(E4/180*PI()))*0.9 G4: =(SIN(E4/180*PI()))*0.9
(copy F4:G4 and paste into F6:G6)
Copy F2:G6, select the chart, and use Paste Special off the Edit menu to
add a new series, categories in first column. Voila, there's your arrow.
You can adjust the factor of 0.9 in the formulas in F4, F6, G4, G6, and
the angle increment of 2 in E4 and E6, but these looked good to me.
If your X and Y are not based on an angle, put them into F3:G3 anyway,
then put this formula into E3, which calculates the angle based on X and Y:
=ATAN2(F3,G3)/PI()*180
That's the easy way to get the arrow. It's also the way that updates
automatically.
The hard way involves using VBA to convert the X,Y axis values of the
endpoints of your arrow into the X',Y' pixel coordinates of the
endpoints, in the coordinate system VB understands, from the top left
corner of the chart area. Then VBA draws the arrow connecting the
points. If anything changes (the X,Y coordinates, the scale of the axes,
the size and shape of the chart, the size and shape of the plot area,
the day of the week), the arrow will no longer line up where it was
drawn, and you'll have to delete it and draw a new one.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
Hi,
I have a XY scatter graph. Maximum value X equal 1, maximum value Y equal
1. How could I put a cercle of radius 1 and get an arrow form with origin
(0,0) for each value?. thanks.
Regards,
Josep Miquel
