Document Sample
Purpose Powered By Docstoc

This assignment is designed to give you practice in

     Implementing a standard numerical integration algorithm in VBA
     Manipulating charts
     Working with a partner (if applicable)

Numerical Integration

Numerical methods use sampling techniques to approximate calculations
that are difficult or impossible to do analytically. An important class
of numerical methods are those used to approximate definite integrals.
They work by dividing the region to be integrated into thin panels,
approximating the function between the panel ends with a simpler
function, and adding up the areas.

The simplest of these, the   Trapezoidal Rule, was described in the week 8
lecture (the 9am Wednesday   class). For this assignment you will
implement a more accurate,   though still straightforward algorithm,
called Simpson's Rule, and   (to be eligible for maximum marks), display
the function on a chart.

The trapezoidal rule uses trapeziums for each panel, thus using straight
lines for the function approximation. Simpson's Rule uses quadratics,
which is substantially better for most continuous functions. As the
references [below] show, it only requires a linear combination of
function evaluations at equally spaced points.

Data Sheet

We have provided a template that contains just one data sheet and no
It has named cells where the user specifies the function (as a normal
worksheet formula in x), the two bounds and the degree of granularity
(Simpson's rule requires an even number of panels, so specify half the
number of panels here).

You will need to write a subprogram that is called from a button that
you should add to the sheet. The subprogram manipulates the cells using
their names (listed on the sheet itself for convenience). As well as
obtaining the bounds from the cells named lob and hib, the program can
write a value to cell x, then apply the nifty built-in method

and obtain the function value at that point.

The final value for the integral is to be written to the cell named
integ. This is also a suitable place for any (short) error message if
the bounds are inappropriate. The number of panels should be limited to
200 (max entry value = 100). If the specified number is not sensible,
substitute the maximum.

To improve the usefulness of the application, the function can be shown
on a chart. This does not mean that your program has to create a new
chart every time the Integrate button is pressed. Instead, do the
following (just once):

  1. In adjacent columns somewhere off to the right generate (x,y) data
     representing a simple sampled function.
  2. Turn on the Macro Recorder
  3. Create ax X-Y scatter chart from that data, place it on the sheet.
  4. Make as many adjustments as you like, so it fits neatly, is as
     legible as possible, etc. Include titles and axis labels.
  5. Stop the Macro Recorder.

While you won't directly run the resulting macro, parts of it will show
you how to adjust the range that the chart gets its data from, how to
update titles and so on. Now, when you implement Simpson's rule, for
each functional evaluation you can write to the sheet at the locations
that the chart is linked to, though you'll have to adjust the length of
the range (using VBA) to suit the number of panels. As long as you've
created the chart to use auto-scaling (the default), the x and y scales
will adjust neatly according to the data.


This assignment is to be completed in groups of either one or two people
from the same lab class. Each group will be required to assess the work
of three other groups from different lab classes in the week 12 lab.
Marks will be reserved for this purpose.

You must register the pair with your tutor no later than the week 10 lab
class. Everyone else will be working as a single person.

You must submit the workbook on-line via the Assessments - Manage
Assigns link on the class page, which includes the web form at Choose ENGG1811 and

Only one member of a pair should submit the file, which must be called
ass2_simpson.xls, as it is stored against the group rather than the
individual. Remember to rename the template after you download it.

Testing and Assessment
Use functions with known integrals for testing: quadratic or cubic
polynomials, sine or cosine, exponentials over a short range. Include
both positive and negative values provided the function is well-defined
in the region (i.e., not for functions like 1/x). You will not get exact
answers except for low-order polynomials, but expect at least 6
significant figures for 100 panels if the function is smooth.

Your submission will be assessed by people in three other lab groups,
during the week 12 lab class. They (and you) will use an objective
marking sheet, so we expect the marks to be quite close. If so, you will
receive the median mark, otherwise a tutor will re-mark it and their
assessment will be used. If you don't turn up to the lab or complete the
assessment (without documented reason) your own mark will receive an 8%
deduction. Tutors will only mark submissions where there isn't a

The approximate allocation of marks (out of 40 so every category is
integral) is

   20      Accuracy of integral using common functions
      3    Bounds errors and defaults for granularity
      6    Chart reliability
      5    Appearance and usability (including the button)
      6    Coding quality (indenting, procedures, constants, identifiers)
   40      Total mark (rescaled to 10% of overall assessment)
      2    Bonus features or enhanced information

Up to 2 bonus marks may be awarded if the submission provides additional
functionality or information to the user. These include (but are not
limited to)

         Using error bars to show the bounds on the chart (not easy, but
          very effective)
         Including the function formula in the chart title
         Detecting when the function is undefined at some point in the

A very readable page on numeric integration (including Simpson's Rule)
is by Lloyd Allison at Monash University. For more formal treatments see
Wolfram Mathworld (equation 9) and the ubiquitous Wikipedia. Note that
the version of the rule to be used is sometimes referred to as the
extended or composite rule, rather than the one that uses a single

Further Information

Use the forum to ask general questions about the assignment, and keep an
eye on it for updates and responses. You can also use it to publicise a
test case that you think shows the algorithm to particularly good or bad

25-Sep Corrected method used to update the fx cell (previously used

Shared By: