Excel Scientific and Engineering Cookbook

Chapter 7. Mathematical Functions

Introduction

Recipe 1.10 introduced you to Excel’s built-in mathematical functions . These functions include things such as taking square roots, computing sines and cosines, inverting matrices, and performing summations. Excel’s mathematical functions are pretty much the bread and butter of many of the computations discussed throughout this book. You can think of these as calculator functions, in that they offer the same functionality you’d expect from a scientific calculator. In this regard, these functions are essential for scientific and engineering calculations in Excel. This chapter covers many of Excel’s built-in mathematical functions; others are treated in different chapters where they seem to fit better in specific contexts.

7.1. Using Summation Functions

Problem

You’re performing calculations that require summations of large amounts of data (for example, for least-squares curve fitting) and would like to use built-in functions that make such summation computations easy.

Solution

Use Excel’s built-in summation functions such as SUM, SUMSQ, SUMPRODUCT, and SUMX2MY2.

Discussion

Excel includes several built-in functions that make performing summations of large (or small) amounts of data very easy. Perhaps the most common sum function is SUM, which simply adds all values contained in a range of cells. For example, =SUM(B2:B24) adds all the values contained in cells B2 to B24. The range of cells does not have to be contiguous either. For example, ...