Excel Functions for the Daily User

CHAPTER ONE

cover

PREFACE

Good day!

Welcome to my latest offering, Microsoft Excel Functions for the Daily User.

Many books have been written on Microsoft Excel. However, not many emphasizes solely on Microsoft Excel’s built-in functions. This book does just that. The basics of Excel’s functions are introduced initially, covering functions such as SUM, MIN, MAX, AVERAGE, and the logical function IF. Readers are then guided to the more advanced functions, namely the Lookup and Reference functions. Examples of the VLOOKUP, HLOOKUP, OFFSET, INDIRECT, CHOOSE, and others are covered with many practical examples on how these functions can be applied. Common error messages are also explained and why it occurs.

Instead of relying on text instructions to accomplish a task, this book shows you how and when the function is used. Along the way, the examples illustrated with graphics wherever possible, is used to enhance the understanding of the reader. Most of the examples are explained in detail and the resulting answer.

Learning something new should always be exciting. That is the precise reason why this book would suit most people who know the basics of Microsoft Excel and want to tap on the power of its built-in functions to help in decision making.

This book was written using Microsoft Excel XP. However, most of the functions covered in this book can also be used in Microsoft Excel 97 and 2000.

So, do not wait to try the functions! Switch that PC and move on with the examples!

Palani Murugappan

[email protected]

Part 1:Introduction

Excel Functions – An introduction

Excel consists of hundreds of built-in functions. When you install Microsoft Excel, these functions are installed too (with the exception of the Add-In functions).

So, what then is a function? A function in Excel is a predefined formula that returns a specific result. It mostly performs calculations that would otherwise be difficult in doing it manually. Without the use of functions, the normal way of performing calculations is using the arithmetic operators such as plus sign ( + ), minus sign ( - ), multiplication or asterisk sign ( * ), division or forward slash ( / ), exponentiation or caret sign ( ^ ), and the percentage sign ( % ). Examples of these signs are illustrated below. Assume cells A1 and A2 have the values 6 and 3 respectively.

With the built-in functions, you do not have to manually construct formulas using the above operators. For example, instead of manually calculating the sum of two or more numbers, you could use the SUM function to calculate the result. Similarly, to find the average of two or more numbers, you could use the AVERAGE function. These are simple examples only. More examples on the other functions available and how they will be used are explained later.

Before proceeding to any of the examples, it should be noted that this book was written using the functions available in Microsoft Excel XP. As such, some of the screen layouts may be different in accessing the Insert Function (Excel XP) and Paste Function (Excel 97 and 2000) dialog boxes. However, despite the different versions of Excel, all produce the same result. It is only the manner of accessing the functions that differ.

To understand how functions work, a simple exercise of calculating the summation of two numbers is explained. In this example, the SUM function is selected. Assume cell A2 has the value 6 and cell A3 the value 3. Select cell A4. This is where the value of the SUM function will be pasted.

Before proceeding with the selection of the SUM function, one should know that functions in Excel fall into several categories. To view these categories, click the Insert Menu. Select Function.

Alternatively, you could click the Insert Function ( ) button located on the Formula bar to activate the Insert Function dialog box. Both of the procedures will open the Insert Function dialog box as illustrated below.

Note that in the above Insert Function dialog box, the last function used will be displayed on the top of the Select a function list. In this case, it is the SUM function. Your screen may show another function.

To view the other function categories, click the Or select a category drop down list. This will produce the following categories as illustrated.

Notice that for every function selected, there is a description of that selected function. For example, if the SUM function is selected, a brief description is outlined below the Select a function list as illustrated.

A point to note is that all functions consists of two elements i.e. the function name and an argument list. In the above SUM function, the function name is SUM. The argument list is number1, number2, etc. The argument list usually includes one or more operands enclosed in parentheses. Depending on the argument, it can be numeric or text constant, or a single reference or multiple cell references.

Coming back to the Insert Function dialog box, upon selecting the function, click the button. The Function Arguments dialog box is displayed.

Observe that in the above Function Arguments dialog box, Excel has selected the range of the two cells and calculated the summation value of the cells A2 and A3. The argument list, in this case the values of 6 and 3 is selected as the first argument i.e. Number1. The final value of 9 is displayed as the Formula result.

Also, observe the Formula bar. The selected function, SUM, is displayed with the argument list i.e. (A2:A3). This is illustrated below.

Click the button to close the Function Arguments dialog box. The final result of 9 is displayed in cell A4 as illustrated.

The above example illustrates the basis of using Excel functions. Some of the other points that you should know of functions is that all functions must be preceded by an equal sign ( = ). Next, the function name and arguments can be typed in either uppercase or lowercase characters. Multiple arguments must be separated by commas. Note that certain functions do not require arguments. However, it is still necessary to include and empty argument list i.e. ( ).

Order of operators

Before performing any calculations, you should know the order of the operators. For example, assume you have the following data in cells A1, A2, and A3 as illustrated.

If you want to calculate the result of 6 + 3 x 2, the normal mathematical way of interpreting it is reading the numbers from left to right and performing the calculations one at a time. The answer to this is 18. However, in Excel, this method does not hold. Assume you had typed the formula as the way it is read in cell C1 i.e. this would be interpreted as =A1+A2*A3.

Observe that the value 12 is returned in cell C1, which is not quite the expected answer. To get the right answer, use parentheses or brackets. Thus, the above formula should read as =(A1+A2)*A3. Type this formula in cell C2 and observe the result. It should be as illustrated below.

The example above shows that if the brackets are not specified, Excel calculates the result of A2*A3 and adds this value to cell A1, which gives a final value of 12. Thus, it is important for you to know the order of the operators.

Another example using the above data is calculating the negated value of