Excel AppNote: Generating Smooth Curves in Charts (WE0820)

• Microsoft Excel for Windows, version 4.0

SUMMARY

The Application Note "Generating Smooth Curves in Charts" (WE0820) 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 Product Support Services

THE TEXT OF WE0820

  Microsoft(R) Product Support Services Application Note (Text File)
              WE0820: GENERATING SMOOTH CURVES IN CHARTS

                                                   Revision Date: 8/93
                                                       1 Disk Included

| 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 (C) 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 do 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 WE0820 disk includes two add-in macros, INTERPOL.XLA and BLEND.XLA, that work together to generate these interpolated values based on x and y data ranges that you specify. The INTERPOL.XLA 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.XLA 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.XLA will automatically be
  updated.

INSTALLING AND USING THE MACROS

To install the add-in macro files

1. In File Manager, copy the BLEND.XLA and INTERPOL.XLA files from the enclosed WE0820 disk to the LIBRARY subdirectory on your hard disk (this subdirectory is located in the directory where you installed Microsoft Excel).

2. In Microsoft Excel, choose Open from the File menu. Select the INTERPOL.XLA file and choose the OK button.

To specify options in the Data Interpolate dialog box

When you choose Interpolate from the Data menu, the Data Interpolate 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     Specify 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 this box         Enter the following
   ---------------------------------------

   Number Of           The number of additional
   Intermediate        data points between your
   Data Points         original data points that
                       you will need to generate
                       a smooth curve

   Select this option   To do this
   -------------------------------

   Include Original     Include the xy values on
   Points               which your interpolated
                        data is based

   Create Chart         Create a chart with a
                        smooth curve

    NOTE: You can use the BLEND.XLA macro independent of INTERPOL.XLA
    to return data points along your smooth curve. You can use the
    Paste Function command on the Formula menu to paste this data to
    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
    CTRL+SHIFT+ENTER.

USING THE INTERPOL.XLA AND BLEND.XLA 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

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:

1. From the Data menu, choose Interpolate. In the startup message box, choose OK.

2. 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.
   

3. Choose OK.

       |     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

Based on these values, INTERPOL.XLA 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 non- singular 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 <b>, or to the right of <e>, then 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 <b> and <c>, then the closer the point is to <b>, 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 <b>, only curve 1 is used; at point <c>, only curve 2 is used; and halfway between <b> 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

The following file(s) are available for download from the Microsoft Software Library:

 ~ WE0820.EXE (size: 31738 bytes) 

   ARTICLE-ID: Q119591
   TITLE     : How to Obtain Microsoft Support Files from Online Services

   http://www.microsoft.com/worldwide/default.htm