excel line chart to circle - not pie chart

[Tweetybird]

Greetings,
I am trying to use excel to indicate pipe wall measurements in a circle. Sort of a cross sectional view of ID and OD measurements.
Just drawing a blank, pie chart does not seem to work. maybe polar chart?
basically trying to bend a line chart into a circle.
any help or direction would be most helpful. Also if this is the wrong platform or forum, i apologize
Lee

 

[Agent666]

Can't you just plot coordinates on a X/Y scatter graph for both the internal and external diameters, to show as a circle you would need to ensure the plot area is adjusted so its square otherwise it will look like an ellispe of course but that seems like the easiest approach. Setup equations to be based on wall thickness and outside diameter, then you can change to suit quite easily to make it dynamic.

An alternative might be to look at using a Donut graph, like a pie graph but with a hole in the middle (it can be found under pie graph when inserting a graph).

 

[Strong]

Try the Radar Plot (requires equal-spaced locations) or search and get a Polar Plot Plug-in or get specific instructions.

Walt

 

[SkipVought]

Used a Scatter chart with lines.

[pre]
ID [highlight #FCE94F]1.8[/highlight]
OD [highlight #FCE94F]2.2[/highlight]

deg xi yi xo yo
1 =COS($A5*PI()/180)*ID/2 =SIN($A5*PI()/180)*ID/2 =COS($A5*PI()/180)*OD/2 =SIN($A5*PI()/180)*OD/2
2 0.899451744 0.031409547 1.09932991 0.038389446
3 0.898766581 0.047102361 1.098492488 0.057569552
...
360
[/pre]

The sheet is protected to allow selection of the [highlight #FCE94F]OD/ID values[/highlight] only.

Here's the event code I used to keep the PlotArea size square and the x & y scale equal...

Private Sub Worksheet_Change(ByVal Target As Range)
    
    With ActiveSheet.ChartObjects(1).Chart
        ActiveSheet.Unprotect
        With .PlotArea
            .InsideHeight = .InsideWidth
        End With
        
        With .Axes
            .Item(1).MaximumScale = [OD] / 2
            .Item(1).MinimumScale = -[OD] / 2
            .Item(2).MaximumScale = [OD] / 2
            .Item(2).MinimumScale = -[OD] / 2
        End With
        ActiveSheet.Protect
    End With
End Sub

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[robyengIT]

Skip : can you post the unprotected file so that I can learn how to plot and increase the OD till 4 ? Thanks any way

 

[SkipVought]

The workbook is not protected, just the sheet. I uploaded the workbook without the sheet protection.

But just enter 4 in the OD, B2, protection or not.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[IDS]

A few comments on skip's post:

- You don't need 360 segments. If you plot as a curved line 10 degree increments (or pi/18)looks fine.

- The macro for setting the limits of the axes is great, but you don't really need it for this particular application. Just drag the graph to look circular and the auto-scaling will keep the x and y axes with the same limits.

- For applications where you do need auto-scaling (anything where the ratio X range/Y range may vary) an alternative without using VBA is to calculate the max and min X and Y values to give the right proportions, then plot a line on a new series from MinX, MinY to MaxX, MaxY, and set the line and markers to hidden.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Agent666]

I've found sometimes there is a difference based on what you see on screen and what is printed with respect to how 'square' shapes or charts are. Circles look slightly squashed vertically on screen when being perfectly round on the printed page. So dragging it to look square on screen can mean it prints stretched vertically.

From memory if you want it square on the printed page, using a macro similar to SkipVoughts solution is the best (though might have it round the wrong way, and dragging is the best way to tweak the printed view, its been a while since I had to deal with this particular issue quirk of excel). From memory it can depend a lot on the default font used under the 'normal' style, something to do with the way different fonts are scaled for printing.

As an aside Jon Peltiers blog dealt with auto sizing of chart axes lately based on a cells value and might be of some interest Link

 

[Denial]

Some time ago, I downloaded some VBA that would ensure "equal scaling" for the X and Y axes of an xy-plot.[ ] I made a few changes to it (hopefully improvements), and posted it on this site as faq770-1901.

 

[Strong]

No response from "Tweety" yet.

Here is a link to generate a Polar Plot from descrete measurement values:

https://peltiertech.com/polar-plot-excel/

Walt

 

[SkipVought]

Thufferin’ thuccotash.

[pre]
v v v v v v v v
Wish we did-n’t have to meet sec-ret-Lee. ;-)
[/pre]
^{Jimmy Rogers}

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Agent666]

So I've been doing a bit of a test on this recently as I required a 'as near as practical' square grid after printing in a recent spreadsheet I developed and thought I'd report back in case anyone else is interested in the future.

Maybe no one else is seeing the same effect, but I've come to the conclusion that setting the plotarea.width = plotarea.height or plotarea.insideheight = plotarea.insidewidth is not enough to get a square grid or circle printing circular. All the examples linked to here ultimately use one of these methods.

I found if I printed to PDF and then measured the grid that the width needed to be almost constantly 1.04-1.06 times wider, so you can simply scale the width component by this factor to produce a true square printout.

I can visibly see that its slightly out on the screen without the correction, and its pretty obvious when printed with a small enough grid. I suspect its something to do with the screen resolution, aspect ratio of monitors, DPI and what font you have defined in the normal style and possibly even your printer. This would obviously make it quite hard to produce a generic correction factor for all monitors/printers/setups depending on what is contributing to and causing the slight scale error.

I'm unsure if the scale factor is universal or different for each users specific setup as I only have one computer in the house to test on

Scaling by 1.05 is about good enough for my purposes, however I'd be interested if anyone has any suggestions to get the 'scale fudge factor' for scaling programatically depending on what it is ultimately linked to.

 

[SkipVought]

It all depends on your particular printer.

I remember somewhat recently printing patterns for my wife, that contained a 1” reference square.

I had to play with the scaling in some way that I’ve since forgotten, to get a 1” square printed. Trial and error feedback.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Agent666]

FYI, I've just noted as well that it also matters what zoom you are at when you print, different aspect ratio 'squares' if you print at 100% vs 130%/160% zoom, etc. Least you can eliminate that variance with the before print event.