+'&title='+encodeURIComponent(document.title)+'&overview='+encodeURIComponent(document.title)+'&urllanguage=en&urlaffiliate=32003&encoding=UTF-8&urlpicture=http://www.viadeo.com/v_img14/shareit/viadeoflower.gif')
The first high precision engineering chart that I would like to share is called the Excel Smith Chart. It is used by radio frequency engineers to drastically simplify complex equations (imaginary numbers) for matching circuits and transmission lines. It is so effective that it has been used since the 1930's and is still used today. Even some high powered engineering software packages use the Smith Chart as the user interface.
For an outstanding tutorial on Smith Charts and how to use them click here.
Here is a sliver of my version of the chart as an animated GIF.
As you can see it is large.
Here is the complete chart.
Click for larger version.
The images above were taken at Excel's 200% zoom setting. There is so much information on the chart that the normal 100% zoom is insufficient. I have spent a lot of time with this chart at Excel's maximum of 400%.
This release of the chart is dynamic in that you can hide or show the internal arcs. Selecting the Show Arcs Slow option allows you to see how the chart is composed. At first glance it may not be apparent, but all of the internal arcs are portions of larger perfect circles. This image may help you see that better:
Look at the arcs within the red circle and see how they are just segments of full circles extending upward and downward.
I will soon release an enhanced version that will allow for interactivity that an engineer would actually use, so this becomes a real tool. For now this is an exercise in very advanced Excel chart construction.
When given a choice, I prefer to keep mathematically derived data that is only used to draw a chart off of any worksheets. I prefer to keep this information as metadata in the form of named formulas in the Name Manager. Many of my projects shared here on Excel Hero are presented in this fashion. There is a downside however, due to a bug in Excel. There is no way to get a series to plot a gap (in a line, for example, resulting in two line segments with the gap in between) when the series is based on a named formula. When using a worksheet, a blank cell in a range does the trick. As a result, each line segment needs its own formula. My Smith Chart has 356 data series, and each data series requires two named formulas, one for the X values and one for the Y values. And of course there are other named formulas for support. Obviously I used VBA to produce them! Have a look at the subroutine, AddArcs().
The primary region of the chart is the circle containing the arcs. Notice that there are three sets of circles here. The first set is the circles that stay within the main circle and get smaller and smaller going to the right. This set represents the REAL number component of the chart.
The next two sets represent the IMAGINARY number components of the chart, and all of these circles are clipped by the main circle. The first set of these go up and away from the horizontal axis with circles getting smaller and smaller. The last set of circles goes down and away from the horizontal axis. The horizontal axis itself is the infinitely large IMAGINARY number circle that sits in between the ones going up and the ones going down.
The first challenge is to figure out the mathematical relationship for the size of the circles in all three sets. Well, actually just the first two because set three is just a mirror opposite of set two.
Early on I decided to make the main circle a Unit circle, which means that it has a radius of 1, and I placed it at the Cartesian coordinates of (0,0).
After a little fiddling, I determined that the formula for the size of the circles in set 1 (the REAL number circles) was:
=(1+n*0.01)^-1
where n is a possible circle number starting at the left, just inside the main circle.
Likewise I determined that the relationship for the size of the circles in set 2 (the IMAGINARY number circles going up) was:
=(10*n^-0.5)^2
Where n is a possible circle number starting just above the horizontal axis.
I say possible circle number because the chart can show many more arcs then what it does shows now. In fact they are infinite. My version of the chart shows only a small sampling of circles sizes (radii) that these formulas define.
So which circles to show? By visually emulating the Wikipedia example, I chose to include the first 20 radii from these formulas (n=1, n=2, n=3, ...n=20). Following that I included every other number for the next 15 circles in each group (n=22, 2=24, n=26, ...n=50). Then I included every fifth number for the next 10 circles (n=55, n=60, n=65, ...n=100). Then every tenth number for the next 10 circles (n=110, n=120, n=130, ...n=200). Then every twentieth number for the next 15 circles (n=220, n=240, n=260, ...n=500). Then every hundredth number for the next 5 circles (n=600, n=700, n=800, ...n=1000). Then every two-hundredth for the next 5 circles (n=1200, n=1400, ...n=2000). And finally every thousandth for the final 3 circles (n=3000, n=4000, n=5000).
I used this sampling of n for both formulas, and hence all three sets of circles.
Drawing a circle with named formulas is simple enough:
For the X values:
=r*COS(t)
For the Y values:
=r*SIN(t)
where r is the radius and t is an array of equally spaced values from 0 to 2π. What you get is not really a circle but a polygon with the number of sides equal to the number of values in this array. But if you set the series to Smoothed Line, anything above 70 points will make a nice circle. I'm using 100 points for each circle in this chart.
To produce t from a name formula, I am using:
=(ROW(OFFSET('1'!$A$1,,,n))-1)*(2*PI()/(n-1))
where n = the number of points I want. Again, 100.
But for the most part, we are interested in arcs, not the full circles. This means that we need to know the starting point and extent (length) of each arc in radians.
I had hoped to find a general formulaic solution to the ANGULAR INTERSECTION PROBLEM depicted in the above graphic for any two intersecting circles so that all formulas could be named (and hidden), but alas the solution remains elusive (with several math gurus helping me). I came close with the law of cosines, but acute angles from the center of Circle B had unacceptable rounding errors - meaning the starting points of the arcs were not precisely at the intersection.
So I had to ferret these out manually by painstaking trial and observation and then record the starting and extent angles in worksheet cells. I am hoping that I will soon be able to find the formulaic way to do this and get rid of all of the worksheet clutter for the arcs. Notice that some of the tolerances required to hit the intersection points are at the 5th decimal place.
The formula for t above will start at radian 0, so all we need to do is add a value to each n inside of t to control the starting point angle. Similarly we just need to multiply the result by another value to control the extent. So the formula for t for the arcs becomes:
=(START+(ROW(OFFSET('1'!$A$1,,,n))-1)*(EXTENT*PI()/(n-1)))
And finally for the Y values for the Set 3 circles we just need to subtract π from the START value to get the mirror image.
Notice that with this strategy we can start an arc at any arbitrary angle around its parent circle, with any precision we like. This is the primary reason I chose an XY Scatter chart instead of a Radar chart.
Now how to chart 356 data series on a chart? Answer: you don't. Here we are in 2010 and still have the silly limitation of 255 maximum series in a chart. Of course if Excel plotted named formula nulls properly, we could do all the arcs in just two series, one for the darker arcs and one for the lighter ones.
The only option is to move to two charts placed one over the other, the top one having a transparent background. Silly really. But it works. For the most part the lighter lines are on the bottom chart. All of the darker lines are on the top chart. There were more than 255 lighter lines - so a few joined the top chart.
Now there is no such thing as radial aligned text in Excel. Nor is there a radial axis. I constructed the radial axes from circles and lines. The radial labels are rotated individually.
I would like to thank Robert Mundigl of the outstanding Clearly and Simply blog for taking the time to meticulously help me track down and fix a VBA problem with European versions of Excel that are set to use a comma instead of a decimal point. Not an easy thing to work with when attempting to make a workbook work across versions. I may write a blog post on it. It's absolutely crazy. Thank you very much Robert!
I would also like to thank Rafael Nicolas Fermin Cota for gallantly attempting to solve the angular intersection problem. I believe he soon will! Thanks Nico.
The file has been tested in Excel 2002/2003, 2007, and 2010. Saving it in .xls format causes it to triple in size, so here's both versions.
*** UPDATE ***
I hope to update this chart very soon with interactivity from an engineering perspective. If you would like to be notified when I do, please sign-up for my newsletter!
BTW, if you have or know of an interesting high precision engineering chart that you would like to see included in this series, please let me know. It may be something that I will want to include as well!
I'm planning something very special for the Excel Hero Newsletter in the next couple of weeks, so please make sure you are subscribed!
If you liked this article, please share it!
Beautiful work Daniel
Hui...
Jaw dropping stuff as usual. How on earth do you manage to get anything else done?!
Awesome . . .
Fantastic work. I did notice two labels were incorrect, cells R14 and R15 should be 1.0 not 0.1
@Hui, Gordon, Gerald Higgins -
Thank you very much! I'm quite proud of this chart.
@Joe Mako -
Thanks for pointing that out. You know after awhile you start to go cross-eyed!
After you brought that to my attention I noticed the same problem in cells R42 and R43.
I have fixed those labels in both the .xls and .xlsm downloads.
Regards to all,
Daniel Ferry
excelhero.com
Daniel,
wonderful article, wonderful visualization. I am working with Microsoft Excel for 20 years now, but I am still impressed by what the real experts (like you) are able to accomplish. Great job! I am proud of having contributed a little something at least for your readers over here in good old Europe.
@Robert -
You are very kind, and incredibly modest.
Thanks again for your intrepid efforts to resolve the decimal point versus the comma VBA issue! Now this can be enjoyed by all!
Regards,
Daniel Ferry
excelhero.com
Daniel, Robert,
Because of your blogs I'll have a tough time waking up in the morning. Again. But this stuff is just amazing. Thanks for sharing it.
@Pawel -
Thank you for the compliment!
I can write a VBA time-clock that even works in Poland if you like...
Regards,
Daniel Ferry
excelhero.com
plz answer, how to draw Qn vline in zy smith chart ? give me link to down load zy smiyh chart with Qn line,
HI
Really interesting work... Any progress with the enhanced interactive version.. ?
Nice work.. you really are squeezing the most out of Excel.
Check this out.. might make you cry though.
http://local.wasp.uwa.edu.au/~pbourke/geometry/2circle/
D
@Daren -
Thanks. Yea, when I was creating the Smith Chart, I found that page, and I studied it for an hour.
The problem is, it produces the Cartesian coordinates of the two intersection points. That is NOT what I need.
I need the angle relative to the 2nd circle's center of the top point in radians, and then I need the angle of the extent of the arc, which ends at the lower intersection point.
Excel Hero reader and contributor, Hui, actually figured it out! And I will be updating the Smith Chart so that it is purely mathematical and does not use the observation constants of the first version.
Regards,
Daniel Ferry
excelhero.com
This looks perfect for what I need.
One silly question though, How can I add my measured data to it.
Thanks Again
Regards
Adam
Hi Daniel,
I found this page while looking for a solution for the 'arc length' problem that you mention in your post. With Pythagoras and the law of sines, I got this result:
alpha = arcsin( 1 / sqrt( 1 / sqr(sigma) ) )
where alpha is half the arc angle and sigma the imaginary part of the normalized impedance. I.e. for the green circle 'B' above sigma is 1 and alpha is 45 degrees, so the arc length is 90 degrees.
This works because we know the distance between the circle centers which is sort(1 + 1/sqr(sigma)), from the radius of the blue circle (1) and the radius of the green circle (1, but generally 1/sigma) and Pythagoras' formula. From that, we can use the law of sines and get the above formula.
Hope you find this useful :-)
greetings
Andreas