Creating a Triangle in a chart

[db1966]

This is a bit long winded, so apologies in advance

I'm trying to create a chart for a colleague that will show the results as a triangle (based on one of our company logo's). I've managed to come up with something by editing a Spider chart, but it's not enough! He wants the lines of the triangle to adjust in size depending on the actual data. For example, record 1 is 58%, record 2 is 29% and record 3 is 13%. The line for record 1 should be the longest, and record 3 the shortest. The results will change from month to month. I've attached an example, for what it's worth....
Any help greatly appreciated, even if it's just someone telling me it can't be done!!

Thanks

Forgot to mention, I'm using Office 2003

[Richard Buttrey]

Hi,

Sound like one for Andy Pope!

I'd just question why this radar chart rather than the more obvious pie chart. The radar chart plots lines relative to a central point. i.e. the distances of the lines from the central point are essentially your percentages. This of necessity means that the lengths of the lines are not the same proportion. I think you need some function to convert the percentages given, to distance percentages which will result in correct length lines.

It should be possible with some simple geometry to work this out so I'll toss it around. One thing we don't know at the moment is what Excel regards as the centre of the triangle - presumably it's the line that bisects the angles.

Rgds

[Andy Pope]

I tried manually to create the chart.

I drew 3 lines of the same length then reduced the their widths by the percentages you gave.

It is impossible to draw a triangle with those 3 lengths. So you would end up distorting the values in order to draw the shape.

You can use xy-scatter to draw the 3 lines once you have the formula to work out the geometery.

[Richard Buttrey]

Hi,
As Andy said, it can't be done for those proportions. No one side of a triangle can be longer than the sum of the other two sides for a triangle to exist.

Do you want to consider some other algorithm which will give you lengths that you could draw as a triangle? If you can I suspect the easier answer will lie in some VBA code which could create three lines of the requisite length, and with simple geometry work out co-ordinate positions for them and hence 'draw' a triangle - however first we need suitable lengths.

Rgds

[lecxe]

Also a possible solution is to use a scatter chart.

Assuming the lengths of the 3 sides are in B2, B3 and B4, you can use for the vertices of the triangle:

Point x: 0, 0
Point y: =B2, 0
Point z: =(B4^2+B2^2-B3^2)/(2*B2), =(B4^2-((B4^2+B2^2-B3^2)/(2*B2))^2)^(1/2)

You can draw a scatter chart with these 3 points.

You'll have then to do some work: clear the chart (no gridlines, axes, etc.), paint the sides of the triangle, format the labels so that each one refers to one side, and deal with the proportion of the axes.

Although this involves some work to set up, the good news is that you can then change the sides' sizes and you'll get a new valid triangle automatically.

[db1966]

Thanks all for your tips, I'll get my VBA for Dummies out and have a go!