Microsoft Knowledge Base
Excel AppNote: Generating Smooth Curves in Charts (ME0858)
Last reviewed: April 3, 1997
Article ID: Q105858
4.00 MACINTOSH Appnote softlib kbappnote
The information in this article applies to:
- Microsoft Excel for the Macintosh, version 4.0
The Application Note "Generating Smooth Curves in Charts" (ME0858) includes two add-in macros that use the interpolation method to generate smooth curves in charts.
You can obtain this Application Note from the following sources:
You can obtain this Application Note from the following sources:
- Microsoft's World Wide Web Site on the Internet
- The Internet (Microsoft anonymous ftp server)
- The Microsoft Network (MSN)
- Microsoft Download Service (MSDL)
- Microsoft FastTips Technical Library
- Microsoft Product Support Services
For complete information, see the "To Obtain This Application Note" section at the end of this article.
THE TEXT OF ME0858
Microsoft(R) Product Support Services Application Note (Text File)
ME0858: GENERATING SMOOTH CURVES IN CHARTS
Revision Date: 9/93
1 Disk Included
The following information applies to Microsoft(R) Excel for the Macintosh(R), version 4.0
-------------------------------------------------------------------- | INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY | | ACCOMPANY THIS DOCUMENT (collectively referred to as an | | Application Note) IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY | | KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO | | THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A | | PARTICULAR PURPOSE. The user assumes the entire risk as to the | | accuracy and the use of this Application Note. This Application | | Note may be copied and distributed subject to the following | | conditions: 1) All text must be copied without modification and | | all pages must be included; 2) If software is included, all files | | on the disk(s) must be copied without modification [the MS-DOS(R) | | utility DISKCOPY is appropriate for this purpose]; 3) All | | components of this Application Note must be distributed together; | | and 4) This Application Note may not be distributed for profit. | | | | Copyright 1993 Microsoft Corporation. All | | Rights Reserved. | | Microsoft and MS-DOS are registered trademarks and Windows | | is a trademark of Microsoft Corporation. | --------------------------------------------------------------------
OVERVIEW
This Application Note includes two add-in macros that use the interpolation method to generate smooth curves in charts.
INTRODUCTION
In Microsoft Excel 4.0, when you create an xy (scatter) chart, the series of xy data points generally does not form a smooth curve. Using a method called interpolation, you can generate a series of values based on a set of xy data points that, when plotted, appear as a smooth curve that connects your xy data points. Interpolation achieves this smooth-curve effect by joining together several smaller curves called splines.
The enclosed ME0858 disk includes two add-in macros, INTERPOLATE and BLEND, that work together to generate these interpolated values based on x and y data ranges that you specify. The INTERPOLATE macro uses a custom dialog box to prompt you for your input range information and allows you to specify how you want your data to be returned. Based on your specifications, BLEND calculates and returns the data points that will generate a smooth curve through the given arrays of x and y values. You also have the option to create a chart that displays the new values in a curve and the original values as data points.
NOTE: When plotted, your x and y values can be in almost any form for example, your values can form one or more loops or rotate through 90 degrees, and so on). In addition, if the original x and y values change, the data returned by BLEND will automatically be updated.
The following information explains how to install and use these add-in macros and then discusses their theoretical basis.
INSTALLING AND USING THE MACROS
To Install The Add-In Macro Files
- In the Finder, copy the BLEND and INTERPOLATE files from the enclosed ME0858 disk to the MACRO LIBRARY folder on your hard disk (this folder is located in the directory where you installed Microsoft Excel).
- In Microsoft Excel, choose Open from the File menu. Select the INTERPOLATE file and choose the Open button.
When you open the INTERPOLATE file, the BLEND file is automatically loaded and the Interpolate command is added to the Data menu.
To Specify Options in the Data Interpolate Dialog Box
When you choose Interpolate from the Data menu, the Data Interpolate dialog box appears, prompting you for certain information. In this dialog box, you are requested to enter information about your existing data, and you are given options for how you want to present the data that the macro generates. Once you have entered the appropriate information in the dialog box, the macro will return a two-column vertical array of x and y values. These values are the additional data points you'll need to make a smooth curve.
The following tables explain what you should enter in each box and discuss the specific options you can select to control how the new data is displayed.
In this box Enter the range of ------------------------------------------ Known X Values Your known x values Known Y Values Your known y values
NOTE: Your data must consist of at least four x values and four y values; these values can be in rows or columns.
In this box Enter the following
--------------------------------------------------
Output Reference The first cell in which the
interpolated data will be
pasted
NOTE: If you select a cell that contains data or is located above data that will be overwritten, you will receive an alert message. When you choose OK in this alert dialog box, you will be returned to the Data Interpolate dialog box to make another cell selection.
In the Number Of Intermediate Data Points box, you can specify the number of intermediate points you think you will need to generate a smooth curve the initial value displayed for the number of intermedi ate points is calculated based on several factors, including screen resolution, printer resolution, and "smoothness" of the original data. In most cases, ten intermediate data points should be enough to generate a smooth curve.
In this box Enter the following
------------------------------------------------
Number Of The number of additional
Intermediate Data data points between your
Points original data points that
you will need to generate
a smooth curve
To control how your data will be displayed, use the options in the Options section of the dialog box.
Select this option To do this
---------------------------------------------------------
Include Original Include the xy values
Points on which your inter-polated
data is based.
Create Chart Create a chart with a smooth curve.
When you select both of these options, the add-in macros work together to create a chart with two data series: one for the smooth curve that has a line style but no data point markers and one for the original data that has data point markers but no line style.
NOTE: You can use the BLEND macro independent of INTERPOLATE to return data points along your smooth curve. You can use the Paste Function command on the Formula menu to paste this data into your sheet. The function takes three arguments: the range containing your x values, the range containing your y values, and the number of intermediate points you want. To enter the formula, you must have at least two cells selected (either columns or rows) and you must enter it as an array formula by pressing COMMAND+RETURN.
USING THE INTERPOLATE AND BLEND MACROS WITH SAMPLE DATA
The following sample data
| A | B ------------- 1 | 1 4 2 | 2 2 3 | 3 2 4 | 4 4 5 | 5 3
Figure 1-Sample Data
when plotted in a chart will resemble the following:
<Chart Deleted> Figure 2-Sample Data Plotted in a Chart
To generate a new xy (scatter) chart with a smooth curve that is based on the data in cells A1:B5, do the following:
- From the Data menu, choose Interpolate. In the startup message box, choose OK.
In the dialog box that is displayed, do the following:
a. In the Known X Values box, type "$A$1:$A$5" (without the
quotation marks).
b. In the Known Y Values box, type "$B$1:$B$5" (without the
quotation marks).
c. In the Output Range box, type "$C$1" (without the quotation
marks).
d. In the Number Of Intermediate Points box, leave the value
set to 10.
e. Under Options, verify that Include Original Points and
Create Chart are both selected.
- Choose OK.
The following table shows the first 15 of 45 rows of the resulting data (the total number of rows will vary with the number of x and y values and the number of intermediate points that you specify).
| C | D | E ------------------------------- 1 | 1 4 4 2 | 1.090909 3.735537 3 | 1.181818 3.487603 4 | 1.272727 3.256198 5 | 1.363636 3.041322 6 | 1.454545 2.842975 7 | 1.545455 2.661157 8 | 1.636364 2.495868 9 | 1.727273 2.347107 10 | 1.818182 2.214876 11 | 1.909091 2.099174 12 | 2 2 2 13 | 2.090909 1.917355 14 | 2.181818 1.85124 15 | 2.272727 1.801653
Figure 3-Table of Intermediate Data Point Values
Based on these values, INTERPOLATE automatically creates the following chart:
<Chart Deleted> Figure 4-Interpolated Curve
In Figure 4, the line shows the interpolated curve, and the data point markers show the original xy values.
THEORETICAL BASIS FOR THIS METHOD OF CREATING SMOOTH CURVES
This particular implementation of curve smoothing is defined as the construction of a smooth curve by the piecewise addition of linearly blended second order polynomials (Burger and Gilles 1992).
An explanation of this method is as follows: given any three nonsingular points, it is possible to define a unique parabola that will pass through all three of them (Burger and Gilles 1992). (See Figure 5.)
<Chart Deleted> Figure 5-A Unique Parabola That Passes Through Three Given Points
To generate a curve through several points, parabolas are calculated for each set of three points (see Figure 6).
<Chart Deleted> Figure 6-The Unique Parabolas That Pass Through Each Set of Three Points
If the interpolated point we want to find lies to the left of or to the right of <e>, the parabolic equation that passes through these points is used (See Figure 7).
<Chart Deleted> Figure 7-The End Conditions
For each intermediate section of the curve, the two parabolas that pass through each point are added together in proportion to how close they are to the original points (see Figure 8).
<Chart Deleted> Figure 8-Blending Curves 1 and 2
For instance, if the desired point lies between and <c>, the closer the point is to , the more of curve 1 is used; and conversely, the closer the point is to <c>, the more of curve 2 is used. At point , only curve 1 is used; at point <c>, only curve 2 is used; and halfway between and <c>, the interpolated point is the average of curves 1 and 2.
Continuing in this way, you can construct a smooth curve (see Figure 9).
<Chart Deleted> Figure 9-The Resulting Smooth Curve
REFERENCES
Burger, P., and Gilles, D. Interactive Computer Graphics, Functional, Procedural and Device Level Methods, 276-277. Palo Alto: Addison Wesley, 1992.
TO OBTAIN THIS APPLICATION NOTE
Application Notes are available by modem from the Microsoft Download Service (MSDL), which you can reach by calling (206) 936-6735. This service is available 24 hours a day, 7 days a week. The highest download speed available is 14,400 bits per second (bps). For more information about using the MSDL, call (800) 936-4100 and follow the prompts. To obtain ME0858, download ME0858.SEA. ME0858.SEA is a compressed, self-extracting file. After you download ME0858, run it to extract the file(s) it contains.
On the Internet, Application Notes are located on the Microsoft anonymous ftp server, which you can reach by typing "ftp ftp.microsoft.com" (without the quotation marks) at the ">" command prompt.
If you are unable to access the source(s) listed above, you can have this Application Note mailed or faxed to you by calling Microsoft Product Support Services Monday through Friday, 6:00 A.M. to 6:00 P.M. Pacific time at (206) 635-7080. If you are outside the United States, contact the Microsoft subsidiary for your area. To locate your subsidiary, see the Microsoft World Wide Offices Web site at:
http://www.microsoft.com/worldwide/default.htm
KBCategory: kbappnote Last reviewed: April 3, 1997 |
