WORKSHEET FORMULAS. Excel Training Level 2 Lesson 5. Excel 97-2003

In this lesson we will look at the thing that Excel does best and that is calculations. Excel has over 300 built in functions that can perform simple additions through to some very obscure engineering functions. Sadly most Excel users never get much past the simple addition, division, subtraction and multiplication. I guess most people do not enjoy math and so have no real desire to revisit it once they have left school.

While I admit I was one of those students that dreaded math, I now tend to look at it from different angle than most. That is, if there is a program that can basically do it for me then I'm all ears! I am certainly no mathematician when it comes to formulas, but I very rarely get stuck in writing an Excel formula. The reason is simply because I know how to use Excel in a way that enables me to perform calculations that I couldn't do if my life depended on it if I only had pen and paper. What I will endeavour to show you in this lesson is not how to perform calculations, but rather how to get Excel to do it for you.

· For every open parenthesis there must be a closing parenthesis. This is not to say that all Excel formulas must have parenthesis.

In Excel the term formula refers to a formula or function(s) in its entirety, whereas the term function refers to only one particular function. For example: =IF(A1=20,"yes","no") is the formula, while the IF is the function used in the formula.

So we are on the same wave length I have included the text from Excels help on the terminology commonly used.

Operators that Excel Recognises

Operators specify the type of calculation that you want to perform on the elements of a formula. Microsoft Excel includes four different types of calculation operators: arithmetic, comparison, text, and reference.

To perform basic mathematical operations such as addition, subtraction, or multiplication; combine numbers; and produce numeric results, use the following arithmetic operators.

Arithmetic operator	Meaning	Example
+ (plus sign)	Addition	3+3
– (minus sign)	SubtractionNegation	3–1–1
* (asterisk)	Multiplication	3*3
/ (forward slash)	Division	3/3
% (percent sign)	Percent	20%
^ (caret)	Exponentiation	3^2 (the same as 3*3)

You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value, either TRUE or FALSE.

Comparison operator	Meaning	Example
= (equal sign)	Equal to	A1=B1
(greater than sign)	Greater than	A1>B1
< (less than sign)	Less than	A1<B1
>= (greater than or equal to sign)	Greater than or equal to	A1>=B1
<= (less than or equal to sign)	Less than or equal to	A1<=B1
<> (not equal to sign)	Not equal to	A1<>B1

Use the ampersand (&) to join, or concatenate, one or more text strings to produce a single piece of text.

Text operator	Meaning	Example
& (ampersand)	Connects, or concatenates, two values to produce one continuous text value	"North" & "wind" produce "Northwind"

Reference operator	Meaning	Example
: (colon)	Range operator, which produces one reference to all the cells between two references, including the two references	B5:B15
, (comma)	Union operator, which combines multiple references into one reference	SUM(B5:B15,D5:D15)

If a formula contained both a multiplication and a division operator Excel would calculate them from left to right. The same would apply for subtraction and addition. We can change the order in which Excel does its calculations by closing the relative function in parenthesis. Let's say we had the formula =10-10*10 the result would be -90 (negative 90). If we then used =(10-10)*10 the result would be 0 (zero). In other words we have forced Excel to change its natural order of calculation. Excel is quite happy to do this.

Arguments

Most of Excels formulas take what are known as Arguments. An Argument is defined as:

The values that a Function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numeric values, text values, cell references, ranges of cells, names, labels, and nested functions.

Avoid Typing

When we write a Function or Formula we would normally refer to a cell or a range of cells for it's argument. In these cells would be the text or numbers that we want to use in our calculations.

So we could write the formula: =Sum(A1:A10) which would tell Excel to add together all numeric entries in the range A1:A10. (Text entries are ignored). A good habit you should form (if you haven't already) is to never type your cell references, use the mouse pointer instead. So in the above example you would type: =Sum( then holding down your left mouse button select cells A1:A10. This will eliminate any possible typos. While there is not much chance of a typo with such a simple reference, there is if the formula was:

=SUM([Book3]ExternalFormulasReport!$IQ$1:$IR$10) and to get really messy you might have:

=SUM('C:\My Documents\Doodlings\[Book3.xls]ExternalFormulasReport'!$IQ$1:$IR$10)

In all three examples we only typed =Sum( . To get a file name in your formula, simply open the file and then Window back to the file you want the formula in and type the equals sign followed the function name, then open parenthesis, then go back to Window and select the sheet and cell(s) you want. The complete file name and path will be automatically placed in when you close the referenced Workbook. You could of course use this method when referencing a particular range on a particular Worksheet within the same Workbook (less the opening of another file and Window).

While this is a relatively simple method it can at times be a bit awkward if the formula is long and/or you need to keep going backwards and forwards from one Workbook/Worksheet to another. The method I use when presented with this is to simply write the formula in the Workbook (or on the Worksheet) I will be referencing, then Cut and Paste it into the Workbook/Worksheet I want the result in. This way Excel does all the Workbook and/or Worksheet referencing for me.

Nesting

As you become more proficient with Excel and it's formulas you will most likely use what is called Nesting. This means that you are using the result of one formula as the argument in another. For instance you may want count all the cells in the range A1:A10 that are equal to the SUM of range B1:B10, so you could use:

In this case we have used the result of the SUM function as our second argument in the COUNTIF function, so we have nested the SUM function within the COUNTIF function. We can nest a function within any function that takes an argument, but the result of that function must return the same type of result the argument uses. In other words we could not nest a function that only returns Text into the argument of another function that must have a number.

There is also a limit to the amount of levels we can nest functions within each other and that limit is seven.

Logical

By far the most commonly used function in Excel is the IF function. But it is also nearly always used with another function nested within it. There are five other types of logical functions and they are AND, NOT, FALSE, TRUE and OR. As with the IF function these are generally used with another function nested within them, or they themselves are nested within a function. There is nothing complicated about the IF function it simply returns TRUE or FALSE as it's result.

Would return TRUE only if both A1=10 and A2=20. Any other combination would return FALSE. It is very unlikely you would use the AND function on it's own like this. You would normally nest it within an IF function, like this:

The NOT function can be used to reverse the result of the IF or another logical function, eg;

The OR is very similar to the AND except that while the AND requires both arguments to be TRUE in order to return TRUE, the OR only needs one argument to be TRUE for it to return TRUE. Both the AND and the OR function can except no less than two arguments and no more than 30.

You will find yourself using the logical functions a lot in Excel to determine whether a condition(s) is/are TRUE and then acting accordingly.

Errors

Without doubt, as you write formulas you will at times generate an error as a result, instead of your expected result. Knowing what the error means will go a long way to helping you identify the problem.

#DIV/0!

This one is nice and simple, it tells us we are trying to divide a number by zero. This is a no no in math. You would get this if you tried to divide a number by an empty cell, as well as a cell containing zero. This is because an empty cell has a value of zero.

#N/A

This is a very common error in Lookup formulas, eg; VLOOKUP, HLOOKUP etc. It is telling us that no match can be found. You will also get this error if the list or table you are looking in contains #N/A.

#VALUE!

This one will occur if you have used the wrong type of argument in a Formula. If cell C4 contained a text entry then using =2+C4 would result in a #VALUE! error. This is because Excel was expecting an number and not text.

#REF!

This one will occur when or if a cell reference in not (or no longer) valid. Let's say you have a simple formula like: =A10 in any cell. You then deleted the cell (not the content) you would end up with the #REF! error.

#NUM

This one is not so common and is similar to the #VALUE! error. In other words you have used the wrong type of argument for a formula.

#NULL

The #NULL error will occur when you have used the intersection of two areas that do not intersect. For example: =SUM(A1:B10 D10:D10) Would result in #NULL as we have forgotten the comma, which is used as our union operator.

#NAME?

This will occur when Excel does not recognise the text in a formula. In most case it is telling you that you have misspelt the name of a function.

At times we may expect to receive errors in our functions, but we do not want to view them as they are not very pleasing to the eye and on top of that they can cause errors in other cells that may have them included in their reference.

We can suppress the errors returned by a formula by using one of Excels Information functions. These functions are generally used to determine the content of a cell or range of cells BEFORE performing a particular function. We would nest our formula within one of the Information functions.

Lets say we know that cell A1 could at times be empty or have a zero in it, so we want to be able to suppress the #DIV/0! error we would get if we try to divide cell A1 into another cell or number.

the ISERR will return TRUE for any error type, except #N/A. To return TRUE for any error type we could use:

Making use of the error type functions is certainly not the only way we can prevent errors. For instance for this particular case we can also use:

Here we have used the ISNUMBER function together with <> (the "not equal to" sign) nested within the AND function, which in turn is nested within the IF function. In all examples we have replaced any possible errors with "" (Empty text). If your formula was one of the Lookup and Reference type you should only suppress the #N/A! error. You would do this by nesting your lookup formula with the ISNA error function. Remember through, if you suppressed all errors you may never know if you have a problem

A word of warning when suppressing errors. Leave it until the end, otherwise you may spend a lot of time trying to track down any other errors that you are not expecting. When you do finally suppress errors (or possible errors) try and limit it to what you expect, don't be tempted to take a blanket approach and suppress all errors. Remember the error is trying to tell you something, so use them to your advantage.

Excel tries to help with formula writing

When you write a formula within Excel it will give you a lot of assistance if you know what to look for! For instance, we know that for every opening parenthesis we must have a closing one. This it not a problem when we only have one formula or have only nested one function within another. However, when you start nesting many formulas or nest formulas within other nested formulas it can get very confusing.

We know that we have four open parenthesis so we must logically have four closing parenthesis, but which closing parenthesis goes with which open parenthesis? You could take a educated guess and try maybe:

but Excel is not going to like this and display a message box telling us our formula contains an error. This is because we have put our closing parenthesis in the wrong places. Well let's step through and type our formula one bit at a time, but this time we will watch for Excels' leads.

· Type: =if(and( If you look closely you should see that the second parenthesis is red.

· Select cell A1 and type: =20,not( This time our parenthesis should be blue.

Now we have completed putting in all our functions, so we can start closing our parenthesis.

· Watch closely and type ) you should the parenthesis turn brown and momentarily bold itself along with the parenthesis after isnumber. This is Excel telling you, that you have just closed off the ISNUMBER function.

· Type another ) and it will turn blue and again momentarily bold itself along with the parenthesis after not

· Type ) once more and we have now closed off our functions back to the

AND.

· Finally, we must always close off any nested function with a closing parenthesis, so we put in our final arguments for the IF function and then close with a ) and it will match up with the open parenthesis after the IF.

Now we can push ENTER and it will accept our formula without any problems. We could even leave off the very last parenthesis and Excels' Autocorrect feature would complete it for us. Another good habit to form is to use lower case for the function names. This is because Excel will recognise the function names and capitalize them for us. This comes in handy when you type a long nested formula with lots of different functions, if you misspell a function name Excel will not capitalize it and you will know at a glance where your problem lies. Try it with the above formula, using all lower case and misspell isnumber.

If you are faced with the rather complicated task of having to write a long and/or deeply nested formula, sometimes it pays to break it into 'bite size chunks' first. Although our nested formula is by no means a long or deeply nested formula (in the scheme of things) it could be constructed by writing it in 'bite size chunks'. Once we know the conditions we need met before we have our formula return yes we can build our formula in separate cells.

· Highlight the text not(isnumber(A2)) in the formula bar and then right click and select Copy then push Enter.

· Select cell C1 and type =and(A1=20,) now in the formula bar right click immediately after: 20, and before ) and select Paste.

· You should have: =and(A1,not(isnumber(A2))). Now Copy this from the formula bar, again leaving off the equal sign and push Enter

· In cell D1 type: =if(). Right click between the open and closing parenthesis in the formula bar and select Paste.

· You should now have: =if(and(A1,not(isnumber(A2)))) and the mouse insertion point will be flashing between our second last and last closing parenthesis.

· Type a comma and then type our result if we want if our IF function is TRUE ie; "yes". Type another comma and then our result we want if the IF function is FALSE. Push Enter.

This is a method I use frequently when constructing a complicated or deeply nested formula, it works very well, but as with most things it does take practice.

Formula Palette or Insert Function

The method I have described above it not the simplest way to write a nested formula, but instead is designed to show you what is going on and hopefully help you understand how nesting works.

We strongly suggest you practice writing small to medium nested formulas. This way to force yourself to understand how Excel wants it's nested formulas. Once you have understood this you can move on to a very simple way that will have Excel do all the opening and closing parenthesis for you. We do this with the Insert function. This is where Excel has nearly every single formula that it can accept.

Insert Function

The Insert Function was known as the Paste Function in older versions of Excel. This feature is used to insert or paste a selected function into the chosen cell.

There are three methods we can use to show the Insert Function dialog and which one you use is purely optional. The three methods are:

· Click the Insert Function icon to the left of your Formula bar (Fx), or for older version users, click the Paste Function icon on your Standard toolbar.

Once activated you will see the Insert Function dialog pop up in front of you. Depending on which version of Excel you are using, these heading names may vary slightly in this dialog box.

· Under Or Select a Category select Logical then under Select a Function select IF. At the bottom of the dialog you will see a brief description of what the IF function does. You could also click Help on this Function to see extra help on the IF Function.

· As you click in the big white boxes Excel will give you a brief description of each argument. So click in each box and have a look at the argument description. The small boxes to the right of the big boxes (they have an upward diagonal red arrow) are called the Collapse tools. If you look in the Formula bar you will see that Excel has put in IF().

· Ok, we need the AND function next. To the left of your Formula bar (where the Name box normally is) you should see the word IF and a small drop arrow to the right. Click this arrow. You should see a list of ten functions, these will be the last ten functions you have used. If the function we want is there we can simply select it, otherwise click More functions... and our Insert function dialog will come back. Select Logical and then AND.

· Look in your formula bar and you should see IF(AND()) The AND and it's parenthesis are bolded because this is our active function.

· Click the Collapse tool for the Logical1 then select cell A1, then click the Collapse tool again and type =20 . Now click in the Logical2 box.

· Again click the small drop arrow to the left of the formula bar. Go to More functions... and this time select NOT. Once again click the small drop arrow to the left of the formula bar. Go to More functions... and this time select Information under Or Select a Category then select ISNUMBER and click OK.

· Click the Collapse tool and select A2 then click the Collapse tool again. In your formula bar you should see: =IF(AND(A1,NOT(ISNUMBER(A2))))

· We now need to get back to our IF function so select the word IF in the formula bar. Click in the Value_if_true box and type: Yes. Click in the Value_if_false box and Excel will automatically place quotations around our Yes. It does simply because it is text, had it been a number this would not occur. Type: No and click OK.

We have now written a simple nested function using the Insert Function. The temptation will be there to use this all the time for nested functions, but try to resist for now (on the small ones at least) as you will not get the full gist of what Excel wants. By all means use the it for harder nested functions, but do watch what Excel is doing as you go along. This is the best way to learn. We know many heavy users of Excel that can write very complicated nested formulas if they use the Insert function, but they have no idea what is happening and could not write the same formula without this aid.

Syntax

This is a word that you will see and hear often in Excel. It simply refers to the order of a functions arguments. For instance the syntax for the IF function is: logical_test,value_if_true,value_if_false. The syntax of any function cannot be altered, however this does not mean we must supply a value for all arguments in every function. Some functions have what is known as optional arguments. This means you can leave them blank. It is important to note though, that if an optional argument is between two arguments, you must still separate each argument with a , (comma).

To see what we mean type the word: Hello in cell A1 and then in any cell type: =substitute(A1,"l",,1) you can see that we have omitted the third argument in this function, but the omitted argument is still between two commas. By doing this we have let Excel know exactly which argument we have omitted. There are not too many functions that will allow this, but you should still be aware of it.

As you become more proficient with writing formulas you may only need a quick reminder of the functions syntax. To have Excel give us the memory jog do this:

In the formula bar you should see : logical_test,value_if_true,value_if_false. Our IF functions syntax.

We use this one a lot when we are writing functions we have not used for sometime. It is usually just enough to jog the memory!!

Troubleshooting

No matter how good we get at using Excels functions we will at times need to do some troubleshooting with our formulas. Excel will again assist us greatly here if we know what we need to do.

To keep things simple let's assume our =IF(AND(A1=20,NOT(ISNUMBER(A2))),"yes","no") formula is not giving us the result we expected and we don't know why.

· Our result will be yes. We expected the result no, because A2=20 and A1 is a number. (Ok, it's not really as we enclosed it in "" (quotations), but we don't know this).

· Lets step through our formula and see why. With your formula cell the active cell click on the Fx symbol to the left of the formula bar (or click Shift+F3). The Insert Function dialog will appear with our entire nested formula. The entire formula should be bolded.

· Click on the AND in the formula bar and only the AND and it's arguments will be bolded.

· If we look in the Logical1 and the Logical2 box we will see the result of the two arguments we supplied. They will both read True.

· We think Logical2 should read False though as A2 does have a number. (Remember we have reversed the result by using the NOT function)

· Click on ISNUMBER in the formula bar. We can now see that the number one is not really a number at all, it's text. We know this because we can now see the "" (quotations) around it.

· While we are here, click back on the AND. You will notice we have the result for each argument we supplied ie; =True =True. Below this we have another =True. This is the result of the entire AND after is has analysed it's arguments.

At the very bottom we have Formula result=yes. This is the result of our entire nested formula. The last two steps will apply to any function or formula and again allows us to step through our formula bit by bit.

If the truth be known we should have known immediately where our problem was as the 1 was NOT right aligned in the cell. ALL numbers are right aligned in a cell by default. Text is always left aligned by default. Of course somebody could have overridden the default alignment by formatting the cell. This may be a good time to point out that overriding Excels default alignment is a bad habit to form and we would strongly suggest you don't do this if possible, except maybe for headings.

Formula Auditing

There is another method that we can use to troubleshoot and this is with the Auditing feature of Excel. This feature is mainly used to try and pin point any errors that your formulas are producing. To be honest with you I have only ever used this out of curiosity and do not find it all that helpful. I put this down to the fact that when I see an error in a formula cell I have a pretty good idea what the problem is by the error type being generated. We will run through it though as you may need it at some stage.

Go to Tools>Forumla Auditing to see the Formula Auditing sub-menu, or right click in the toolbar area of your screen and select Auditing to bring up the Auditing Toolbar. We will look at the options on the Toolbar here.

Error Checking

Checks your worksheet for errors and if it finds them, brings up a dialog box showing you the type of error you have got, and giving options to fix it.

Trace Precedents

Draws tracer arrows from the cells that supply values directly to the formula in the active cell (precedents). To trace the cells that supply values indirectly to the formula in the active cell, click the Trace Precedents button again.

Remove Precedent Arrows

Trace Dependents

Draws a tracer arrow to the active cell from formulas that depend on the value in the active cell. To add additional levels of indirect dependents, click the Trace Dependents button again.

Remove Dependent Arrows

Removes tracer arrows from one level of precedents on the active worksheet. To remove the next level of arrows, click the Remove Dependent Arrows button again.

Remove All Arrows

Trace Error

If the active cell contains an error value such as #VALUE or #DIV/0, draws tracer arrows to the active cell from the cells that cause the error value.

New Comment

Circle Invalid Data

Identifies all cells that contain values that are outside the limits you set by using the Validation command on the Data menu. To see what data restrictions and messages are in effect for a cell, click the circled cell, and then click Validation on the Data menu.

Clear Validation Circles

Hides circles around cells that contain values outside the limits you set by using the Validation command on the Data menu. To see what data restrictions and messages are in effect for a cell before you remove circles, click the circled cell, and then click Validation on the Data menu.

Please note that if your are not familiar with Validation let me know and we will cover it in the next lesson. It is a very useful function.

Show Watch Window

Will allow you to add a formula that frequently generates an error to a window to keep an eye on it for any future error messages.

Evaluate Formula

Allows you to breakdown and edit a formula containing an error, effective with nested formulas. This option is an alternative to the Insert Function Dialog, which can be used equally as efficiently to break down formulas.

Let's create an error in a formula to see how the Auditing Toolbar can help us. We will use the formula =IF(AND(A1=20,NOT(ISNUMBER(A2))),"yes","no").

· Now in cell A1 enter this formula =na(). This will create the #N/A error for us. If you now look at cell H30 it too will be reading #N/A.

· Click in cell H30, the click the Trace Precedents button on the Auditing toolbar. You should now see two arrows leading into cell H30, one red and one blue. These are coming from cells A1 and A2

· To move directly to these cells, double click on either the red or blue arrow head. To move back to the formula cell, double click the round dot at the start of the arrow.

· Click in cell A1 and click the Trace Dependents button and you will have a red coloured arrow leading to the formula cell. Again you can navigate straight to the cell by double clicking on the dot.

· Now with H30 selected click on the Trace Error button. Excel will draw a red arrow and immediately take you to the error cell.

In case you don't realise, when using the Auditing toolbar a red arrow means an error. That is why when we initially clicked in cell H30 and then clicked the Trace Precedents button Excel used a red arrow coming from cell A1 (red means error) and a blue one from cell A2.

Ok, armed with this information you will eventually be able to confidently write those very long complicated Excel formulas you have no doubt seen. If somebody asks you how it works (or why it doesn't work) you won't need to make out you are suddenly very busy and run away! By the time you have worked your way through it they will either think your are a related to Albert Einstein or meet you with a blank stare, either way they will know that, you at least know what your talking about (and they will be right).

EXCEL VIDEO TUTORIALS / EXCEL DASHBOARD REPORTS

WORKSHEET FORMULAS

Excel Training Level 2 Lesson 5

<< PREVIOUS LESSON |NEXT LESSON>> | BACK TO EXCEL LEVEL 2 TRAINING INDEX

FREE EXCEL HELP