
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 ALL formulas in Excel there are two rules that MUST be followed.
·
All formulas must begin
with an = (equal sign)
·
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.
The text below is from the Excel help file:
Calculation operators in formulas
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.
Arithmetic operators
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 
311 
* (asterisk) 
Multiplication 
3*3 
/ (forward slash) 
Division 
3/3 
% (percent sign) 
Percent 
20% 
^ (caret) 
Exponentiation 
3^2 (the same as 3*3) 
Comparison operators
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 
Text concatenation operator
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 operators
Combine ranges of cells for calculations with the following operators.
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) 
End of MS Excel Help file
When Excel performs a calculation it does so in the following order:
· Exponentiation
· Multiplication
and Division
· Subtraction
and Addition
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 =1010*10 the result would be 90 (negative 90). If we then used =(1010)*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.
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.
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(ExternalFormulasReport!IQ1:IR10) or worse still:
=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.
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:
=COUNTIF(A1:A10,SUM(B1:B10))
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.
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.
=AND(A1=10,A2=20)
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:
=IF(AND(A1=10,A2=20),"Yes","No")
This says, if A1=10 and A2=20 then say Yes otherwise say No.
The NOT function can be used to reverse the result of the IF or another logical function, eg;
IF(NOT(AND(A1=10,A2=20)),"Yes","No")
In this case if both A1=10 and A2=20 the result would be No.
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.
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.
The type of errors you can expect are:
#DIV/0!
#N/A
#VALUE!
#REF!
#NUM!
#NULL
#NAME?
Lets look at each of these in turn and see what they are trying to tell us!
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.
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.
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.
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.
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.
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.
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.
We could use:
=IF(ISERR(20/A1),"",20/A1)
the ISERR will return TRUE for any error type, except #N/A. To return TRUE for any error type we could use:
=IF(ISERROR(20/A1),"",20/A1)
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:
=IF(AND(ISNUMBER(A1),A1<>0),20/A1,"")
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.
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.
Consider this example:
=IF(AND(A1=20,NOT(ISNUMBER(A2))),"yes","no")
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:
=IF(AND(A1=20,NOT(ISNUMBER(A2)),"yes","no"))
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.
· Select
cell B1 and then click in the formula bar.
· 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.
· Type
isnumber( Our parenthesis should be
brown.
· Select
cell A2
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
· Now
type a ,yes, no
·
Type )
once more and we have now closed off our functions back to the
· 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
· Select
cell B1 and type =not(isnumber(A2)).
· 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.
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.
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:
·
Going to Insert>Function
·
Push Shift + F3
·
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.
We will use the Insert Function dialog to help us write the above function.
· 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.
· Click
OK.
· Make
sure you are clicked in the Logical_test box.
· 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
· Click
the Collapse tool and select A2 then click the
· 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.
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
any cell type =If
· Now
push Ctrl+Shift+A
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!!
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.
· Put
the number 20 in cell A1 and in A2 put ="1"
· In
any cell enter =IF(AND(A1=20,NOT(ISNUMBER(A2))),"yes","no").
· 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
· 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
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.
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 submenu, 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.
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.
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.
Removes tracer arrows from one level of dependents on the active worksheet.
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.
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.
Removes all tracer arrows from the worksheet.
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.
Inserts a comment at the insertion point.
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.
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.
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.
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").
· First
of all, Cut and Paste the formula into cell H30.
· 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.
Let's see how the Auditing toolbar can help us!
· 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.
· Once
back in the formula cell, click Remove Precedent Arrows.
· 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.
· Make
sure you are back in cell A1 and click Remove Dependent Arrows.
· Now
with H30 selected click on the Trace Error button. Excel will
draw a red arrow and immediately take you to the error
cell.
· Click
Remove All Arrows
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).