Hands on Spreadsheets: Functions and formulas

A spreadsheet function is a built-in mini macro designed to save you time and the computer’s memory. The first function, and still the most widely used one, is SUM.

When totalling the contents of a range of cells, instead of using =A2+A3+A4 and so on down to A100, you can simply enter =SUM(A2:A100). Instead of entering =SUM(A2:A100)/99 you can use =AVERAGE(A2:A100).

Functions that apply to a range such as this are not limited to a group of cells on one worksheet. If a workbook has 12 worksheets labelled Jan to Dec, you can total all the entries in, say, cell G19 using =SUM(Jan:Dec!G19).

This is known as a 3D formula. Instead of being limited to a two-dimensional worksheet, it drills down through all the worksheets in the workbook.

There are Excel functions designed for people with diverse interests: engineers, financial analysts, time-keepers, statisticians and more. A few functions are only available if you use the Analysis Toolpak.

To add these, choose Excel Options and select the Analysis Toolpak from the list of Add-Ins. Some functions have been added with each new version of Excel, while others are undocumented and included to make Excel compatible with other spreadsheets, such as Lotus 1-2-3 or Quattro Pro.

This column is about the functions and formulas containing them, which can help solve everyday problems.

How about figuring the odds of being successful in a lottery? If each lottery number contains three numbers, each of which can be between 0 and 99, then the permutation function tells you the odds of winning are 970,200 to 1. Just enter =PERMUT(100,3).

Of course, that’s only if the three numbers have to be in a certain order to win. The number of permutations is much larger than the number of combinations.

In probability theory, a combination refers to any arrangement of the three numbers, not just a specific one. In that case, =COMBIN(100,3) tells you the odds of winning are improved to 161,200 to 1.