Congratulations to Doug Jenkins, who writes the Newton Excel Bach, not (just) an Excel Blog blog. His entry UDFs for trigonometric functions was selected as winner in the Contest for Excel Models in Science and Engineering.

Doug’s winning entry was selected at random from among the following six entries:

• Lorentz Attractor - Timothy Bard
• Diffusion-Limited Aggregation - Timothy Bard
• Section Properties - Doug Jenkins
• UDFs for trigonometric functions - Doug Jenkins
• UDFs for elastic analysis of concrete - Doug Jenkins
• Estimating unpumped aquifer water levels - Keith Halford

I originally announced a signed copy of Bernard Liengme’s Guide to Microsoft Excel 2002 for Scientists and Engineers only for US and Canada entrants, but a quick review of shipping tells me I can afford to send Doug the same prize. Be patient, Doug, the package will take about two weeks to get down under.

A dynamic chart can be constructed using dynamic ranges that change in size as data is added or removed. This technique is very powerful: you can define flexible ranges limited only by your ability to write an appropriate Refers To formula.

One imitation of this approach is that it can only account for dynamic numbers of points in a series, but not dynamic numbers of series in a chart. Using VBA you can work around this limitation.

Let’s start with a simple data range as shown below. Following best practices, the data is arranged with series in columns, the category labels (X values) are in the first column, the series names are in the first row, and the top left cell of the range is blank. Using the Define Names dialog, we can define a name using these parameters:

Name: ChtSourceData
Refers To: =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)+1,COUNTA(Sheet1!$2:$2)+1)
Read more »

In Contest - Excel Models for Science and Engineering I announced a contest for scientific and engineering uses of Excel. People can enter up to three candidate Excel models (up to six if they blog about the contest) by posting a link to the model in a comment to that post or to this one. The models have to be freely available, and have to at least seem to do something. I’m not judging on the merits of the models: a contest winner will be chosen at random from the submitted models.

The deadline for entries is fast approaching: two more days.
All entries must be posted by midnight, Friday, July 4, 2008, EDT.

The contest winner will receive a copy of Excel MVP Bernard Liengme’s Guide to Microsoft Excel 2002 for Scientists and Engineers for US/Canada entrants or a $25 Amazon gift certificate by email. If you live elsewhere but still want the book, and I can ship it for about the cost of the gift certificate, let me know, and I’ll ship it.   This is the Excel 2002 edition of the book. The Excel 2007 edition will not be out until this fall, but any lessons learned in the Excel 2002 version are applicable to Excel 2007, though some of the specific commands may have changed.

GetOpenFilename and GetSaveAsFilename are useful dialogs to use within Excel VBA procedures when your procedure needs to know the filename of a file to open or save. I have encapsulated each into functions that add to their functionality, and make then simpler to use in my procedures.

Note that both GetXxxxFilename functions return file names. They do not open or save any files, merely provide a mechanism to ask the user for the names of the files that the calling procedure will process later.

The GetOpenFilename and GetSaveAsFilename functions as I use them are given by the following syntax; these arguments are optional, and I’ve left out optional arguments that I don’t use. I define a variant named vTemp to accept the return value of the function.

vTemp = Application.GetOpenFilename( _
    FileFilter:=sFilter, Title:=sTitle, MultiSelect:=bMulti)

vTemp = Application.GetSaveAsFilename( _
    FileFilter:=sFilter, InitialFileName:=sName, Title:=sTitle)

Read more »

In Data Visualizations Related to Gas Prices, Tony Rose of Support Analytics Consulting shows a series of visualizations he’s culled from the web, all related to gasoline prices. One infographic stuck out, partly for its overuse of chart junk, and partly for Tony’s interpretation. Here is the original chart, which Tony took from www.thebiblog.com, followed by Tony’s comments, which I’ve condensed.

Here is a visual of what makes up the price for gas in 2004, when the average price was $1.85 per gallon versus 2008 where we are now paying roughly $4.03 per gallon on average. . . . One fact that I would have expected to see below is a dramatic increase in the distribution cost of gas between 2004 and 2008, which actually decreased. Seems like there should be an almost perfect correlation between distribution costs and the price of gas, right? Maybe the impact is hidden due to the category being both distribution and marketing.

Read more »

In Bad Graphics - Stacked Pyramid Chart, I critiqued a popular infographic display type, the pyramid chart. In this post I will repeat the favor for funnel charts. These are not the funnel charts which are also called tornado charts, and in some circles are used to construct population pyramids (see Tornado Charts and Tornado Charts and Dot Plots elsewhere on PeltierTech.com). These are stacked 3D abominations which sometimes follow a flawed analogy to a physical funnel, just like the pyramid charts in my previous example follow a flawed analogy to an actual pyramid.

The first example of a funnel chart comes from the FusionCharts Free Chart Gallery.

This example shows the flawed analogy I’ve alluded to, which indicates that some quantity goes from a large number to a small number, starting with website visits, only a fraction of which result in downloads, and only a fraction of those result in inquiries about purchase, etc. It seems to me that a physical funnel should make everything that goes in the top come out the narrower bottom. If it didn’t work that way, your kitchen would become a disaster. Read more »

In Contest - Excel Models for Science and Engineering I announced a contest for scientific and engineering uses of Excel. People can enter up to three candidate Excel models (up to six if they blog about the contest) by posting a link to the model in a comment to that post or to this one. The models have to be freely available, and have to at least seem to do something. I’m not judging on the merits of the models: a contest winner will be chosen at random from the submitted models.

The deadline for entries is now one week away. All entries must be posted by midnight, Friday, July 4, 2008.

The contest winner will receive a copy of Excel MVP Bernard Liengme’s Guide to Microsoft Excel 2002 for Scientists and Engineers for US/Canada entrants or a $25 Amazon gift certificate by email. If you live elsewhere but still want the book, and I can ship it for about the cost of the gift certificate, let me know, and I’ll ship it.   This is the Excel 2002 edition of the book. The Excel 2007 edition will not be out until this fall, but any lessons learned in the Excel 2002 version are applicable to Excel 2007, though some of the specific commands may have changed.

When people think of an Excel combination chart, they usually think of a chart with lines and columns, or something similar that they found on the list of “Built-In Custom” chart types (I just love that oxymoron). However, line and XY series are types that combine to make decent charts. The line chart’s Date Scale category (X) axis provides the only proper date formatting in all of Excel, while the XY chart type provides a great deal of flexibility missing from line charts.

The chart below shows six months of daily stock data, using a line chart, and six months of an imaginary monthly market index. Note two features of this chart.

First, the date axis ticks are displayed at the first of every month (the vertical gridlines are usually unnecessary, but I’ve added them here to help illustrate the axis scaling). This can only be done using a line chart; in an XY chart, you have to pick a single value for the tick spacing, so you cannot accommodate the unequal days in a month.

Second, the markers for the market index are not plotted on the same dates as the stock price data. This could be done in a line chart, if you didn’t mind a lot of blank cells, but using an XY chart type for this series makes it simple without having to precondition the data.

As far as the appearance of the series is concerned, you can use XY or line types interchangeably. Either chart type accommodates lines or no lines, markers or no markers. You don’t have to use a “Line” chart just because you want your markers to be connected by a line. The difference between Line and XY charts is in how the X values (dates) are presented on the chart.
Read more »

I’ve decided to follow up to my recent Close Races post. My last chart in that post was a dot plot, and the lines were dark enough to detract from the plotted points (below left). I’ve lightened the lines (below right) to reduce this effect, while keeping the lines to help viewers trace the series. The lighter lines seem to help a bit. I’ve also replaced the teal/cyan (hard to view”) with a plum shade for Clinton.

Read more »

In Close Races, Kaiser of Junk Charts writes about the “racetrack” charts used in the New York Times to compare candidates’ performances in different demographic categories. Specifically, the charts compare votes received by Clinton and Obama in cities, suburban regions, and rural areas. The New York Times charts are donut charts, or pie charts with different circumferential bands representing the different demographic regions. A portion of the New York Times chart is shown here:

I got this chart from Kaiser’s post, and he got it from a “recent issue of New York Times magazine”. The problems with this kind of chart have been described in Kaiser’s recent post as well as earlier posts on the Junk Charts blog. The problems stem from the data being depicted as angular measures, when the relative arc length and area of the ring segments varies greatly even for segments with similar angles. Read more »

I’m jumping on the blog contest bandwagon. The intent is to highlight scientific and engineering applications of Microsoft Excel, by sharing models that users have built.

In Physics Lesson, I showed how to construct a simple yet robust model to describe a physical phenomenon. This is a departure from most Excel applications, which involve finance or shopping lists. In the newsgroup thread which inspired my blog entry above, another poster followed up with some links to other examples from physics and engineering, and I’ve added another:

Physics

- Pendulum
- Particle Analysis
- Wave
- Projectile Trajectory

Engineering

Free Excel/VBA Spreadsheets for Heat Transfer (and Fluid Mechanics, PDE’s, Thermodynamics and Numerical Methods, too)
Professor Robert J. Ribando
School of Engineering and Applied Science
University of Virginia

Newton Excel Bach, not (just) an Excel Blog
Among other topics, Doug Jenkins covers engineering applications of Excel, including structural analysis.

I can’t personally vouch for the accuracy of these models, of course, but at first glance they have struck me as being well considered and well constructed.

Contest

Do you have an Excel model that you’ve developed yourself, for use in scientific or engineering applications? This could involve demonstration of a phenomenon, as in the model I posted in Physics Lesson. It could involve calculations about the performance of an engineered product, as Doug has performed in Newton Excel Bach. Is your model available for public download on the Internet? If so, respond to this post in a comment, with a link to the model. Your model will be entered in the contest.
Read more »

A visitor to the Microsoft newsgroups wanted his area chart to show a different color for positive and negative values. This is easy enough to do in a column chart. One technique is simply to use the Invert if Negative formatting option, the other it to make a conditional chart with one series for positive values and the other for negative values. Neither of these techniques works for area charts, but it’s possible to adjust the data to make an area chart with two series, one for positive and one for negative.

Del Cotter has written up a very similar approach in Excel area chart with colour invert if negative. It’s the first I’ve seen of his Information Ocean blog, but I’ve added its feed to my reader, and I’ll keep up with it.

Read more »