 |
|
|
Download the associated Workbook for this lesson
In this lesson we will look at some specific Worksheet formulas and how they could be nested together to return a desired result.
We now know how Excel performs its calculations and how we can nest functions together so we can use the result of one function as the argument of another. We have also shown you what we believe to be a good way to break down a complicated formula into bite size chunks and then join them together. We also know how to step through a nested formula so we can track down a possible error within it or simply to see how it has obtained its result.
We have mentioned that there are over 300 functions available to Excel. A simple fact of life is you will not use anywhere near this many when producing a spreadsheet. In fact you will most likely find yourself using the same set of functions over and over again. The only difference will probably be the order in which you nest them together. What we would like to show you now is some of what we believe to be Excels most useful and popular functions.
The first thing we should tell you about Excels functions is that they are not all at your disposal by default. The reason for this is simply because most users do not use them and have no need. Remember that most users use Excel is a very expensive note book.
The other functions and features that are available to Excel are stored as what are called add-ins. These are nothing more than Excel Workbooks with a .xla extension instead of a .xls extension. These add-ins are what contain the code to allow the other functions and features to work. When an add-in is opened it opens as a hidden Workbook that can only be seen in what is called the Visual Basic Environment. We will certainly not go into any more detail than this on add-ins as it would be far beyond the scope of this lesson. We only mention this fact so that you realize there are even more features and functions at your disposal should you need them. The reason they are not all installed by default is so the file size of Excel is not blown out unnecessarily. We would also imagine it prevents the 'normal' user from becoming intimidated by Excel. So let's install some Excels add-ins.
What you need to do is this:
Go to Tools>Add-ins and check these add-ins
Analysis Toolpak
Conditional Sum Wizard
Lookup Wizard
Now click OK. You will have noticed that there are other add-ins available, but we are only interested in these ones because they can be used as tools for learning. The Analysis Toolpak will make available some extra functions which can be useful, in particular some Date and Time functions. The Conditional Sum Wizard can be used to help write Sum formulas that need to meet a set condition. The Lookup Wizard can be used to help write Lookup formulas that need to meet a set condition.
Let's look at some specific formulas. Below are some examples of the most popular formulas. In the syntax examples any bolded font will mean the argument must be supplied, while any non bolded font means the argument is optional. The examples I have supplied here are not meant to be of any practical use, they are simply to help you understand them.
Syntax =abs(number)
This function is used to return a number without it's sign.
An example of this would be: =ABS(-25) This would return 25
Syntax =address(row_num,column_num,abs_num,a1,sheet_text)
Used to return a cell address as text.
It requires that the row_num and column_num are supplied to it. The other arguments are optional.
An example of this would be: =ADDRESS(1,1) This would return $A$1. Notice that as we have not supplied any optional arguments and as so the default is for an Absolute address. The optional values for the "abs_num" argument are as below:
|
Abs_num |
Returns this type of reference |
|
1 or omitted |
Absolute |
|
2 |
Absolute row; relative column |
|
3 |
Relative row; absolute column |
|
4 |
Relative |
If we used =ADDRESS(1,1,3) It would return $A1
The fourth argument, a1, will take either 1 or TRUE for the A1 style reference. 0 or FALSE for the R1C1 style reference. The default (or if it is omitted) is TRUE (1).
The sheet_text argument is the Sheet tab name of a Worksheet. =ADDRESS(1,1,,,"Sheet2") and would return:
Sheet2!$A$1 (Notice the commas for the omitted arguments). The sheet_text would generally only be used in an external reference.
The ADDRESS function is not of much use on it's own and is generally used nested with another function or has another function nested within it.
Syntax =choose(index_num,value1,value2,...)
This function will choose an action or value determined by the index_num.
It can take up to 29 arguments (not including index_num) with value1 being compulsory.
An example of this would be: =CHOOSE(3,"dog","cat","rat") This would return "rat" as it is the third index_num or value.
This function is very handy when working with an ordered list of text or numbers. It is also generally used nested with another function or has another function nested within it.
Syntax =count(value1,value2,...)
This function counts the number of numeric cells within a range.
It ignores text. It can take up to 30 arguments.
Each argument would be a range of cells. An Example of this would be:=COUNT(A1:A10,C1:C10) This would return 10 but only if any ten of the cells contained numeric entries.
*A date is a numeric entry, regardless of its format*.
Syntax =counta(value1,value2,...)
This function counts the number of non blank cells within a range.
It counts numbers and text.
It can take up to 30 arguments.
Each argument would be a range of cells.
An Example of this would be: =COUNTA(A1:A10,C1:C10) This would return 20 but only if all the cells were non blank.
*A cell containing "" (empty text) would be considered non blank*
Syntax =countblank(range)
This function counts the number of blank cells within a range.
It ignores numbers and text.
An Example of this would be: =COUNTBLANK(A1:C10) This would return 0 (zero) but only if all the cells were non blank.
*A cell containing "" (empty text) would be considered non blank*
Syntax =countif(range,criteria)
This function counts the number of cells within a range that meet a given criteria.
It ignores all cells which do not meet the given criteria.
An Example of this would be: =COUNTIF(A1:A10,"dog") This would return 1 if the text dog was within the range A1:A10 once.
It is not case sensitive.
It would not consider a cell that contained the text dog along with another number or text as meeting the criteria.
To do this we would need to use the Wildcard character * (asterisk) eg; =COUNTIF(A1:A10,"dog*")
The criteria argument can accept a comparison operator, but
it must be expressed as text. For example to count the number of entries within the range A1:A10 that are greater then the number 10 we would use:
=COUNTIF(A1:A10,">10")
If we only wanted to count the entries that are exactly 10 we would use:
=COUNTIF(A1:A10,"=10") or =COUNTIF(A1:A10,10) both methods would consider a cell within the range A1:A10 containing ="10" (a text number) or a numeric 10 as meeting the criteria.
However, =COUNTIF(A1:A10,">10") would not consider a cell containing ="11" as meeting the criteria.
Syntax =datevalue(date_text)
Converts a text entry that represents a date to a numeric entry that Excel will recognise as a true date (serial number).
=DATEVALUE("22/12/2005") would return 38708 the numeric value of the date "22/12/2005". This function is good for working on those horrid spreadsheets that have dates entered as text. This often happens to dates that are imported into Excel from another Application.
Syntax =edate(start_date,months)
Returns the serial number of the date that is before or after start_date as set by months.
=EDATE("22/12/2005",1) would return 38739 the numeric value of the date "22/01/2006".
In other words it has added one month to the start_date.
The formula =EDATE("22/12/2005",-1) would return 38678 the numeric value of the date "22/11/2005".
*In both cases the cell would need to be formatted to the required date format. *
Syntax =eomonth(start_date,months)
Returns the serial number of the last day of the month before or after start_date as set by months.
=EOMONTH("22/12/2005",1) would return 38748 the numeric value of the date "31/01/2006".
In other words it has added 1 month to the start_date and then returned the last day of that month.
Again cells would need to be formatted to the required date format.
*You should note that for EDATE and EOMONTH if the start_date supplied is not a text date, then it would need to be the serial number of the date. I prefer the text method as you can tell at a glance the date being used*
Syntax =find(find_text,within_text,start_num)
Finds one text string within another and returns position number of the found string.
=FIND("e","Excel") would return the number 4 as "e" is the fourth letter within the text "Excel".
As you can see the function is case sensitive as it has not returned 1 (the position number of "E").
If the start_num argument is omitted as in the example the default is 1.
This means it will start looking from the first letter of "within_text"
Syntax =hlookup(lookup_value,table_array,row_index_num,range_lookup)
Looks for a value in the first row of the table_array you nominate and returns the value from the same column in a row you specify by row_index_num.
=HLOOKUP("Cost",A1:D10,5,FALSE) Would return the value 500 if the word "Cost" was within the range A1:D1 (the first row of table_array) and the number 500 was in same column as "Cost" but in the fifth row.
The optional argument "range_lookup" can take either TRUE or FALSE,
if omitted it is considered TRUE.
By using "FALSE" we are telling it to find an exact match (but not case sensitive).
When the lookup_value is text, "FALSE" is nearly always used.
If the lookup_value is a numeric value then using "TRUE" or omitting the argument will result in the argument lookup_value returning the column of the closest match.
For this reason if TRUE is used when lookup_value is a number, the first row of your table_array should be sorted in Ascending order.
This function has what is known as multiple argument lists. In other words it can be used in two different ways.
Method 1
Syntax =index(array,row_num,column_num)
Returns a value or the reference to a value from within a table or range.
=INDEX(A1:B10,5,2) This would return the value of cell B5 because we have told it to return the firth row down (5) and the second column (2) from the range A1:B10.
If the array had been A1:A10 we could have omitted the "column_num" argument and the return value would have been the value in cell A5.
As we have used a 2 column range for the array argument, we must supply the "column_num" argument.
Method 2
Syntax =index(reference,row_num,column_num,area_num)
Returns a value or the reference to a value from within a table or range
=INDEX((A1:B10,D1:E10),5,2,2)
This would return the value of cell E5.
We have nominated two separate reference arguments (A1:B10,D1:E10). When two separate ranges are used as the reference argument we must enclose them in parenthesis.
We have nominated the fifth row (5) as our row_num argument.
We have nominated the second column (2) as our "column_num" argument.
We have nominated the second area (2) for our "area_num" argument.
So the fifth row in the second column of the second area (D1:E10) is cell E5
The INDEX function is nearly always used nested with another function. It is particularly handy when a lookup is required to the left of a nominated range. The help in Excel has a lot of detail on this function and I would recommend you read this as it explains it quite well. I have tried to keep it reasonably simple here so it can be understood.
Syntax =indirect(ref_text,a1)
Returns a reference specified by a text string.
=INDIRECT(A1) would return the value of cell B1 if A1 had the text "B1" written in it.
In other words it is indirectly returning cell B1.
The "A1" is either TRUE (or omitted) or FALSE and determines the reference style used.
If cell A1 had the text "R1C2" in it we would need to use =INDIRECT(A1,FALSE) to obtain the same result.
The INDIRECT is often used nested within another function as the reference argument of that function.
Syntax =large(array,k)
Returns the k-th largest value in a set of values.
=LARGE(A1:A10,2) would return the second (2) largest value in the range A1:A10
Syntax =left(text,num_chars)
Returns the left most character(s) as nominated by "num_chars"
=LEFT("bullet") This would return the text "b". As you can see, if the "num_chars" argument is omitted the default is 1 =LEFT("bullet",2) This would return the text "bu".
Syntax =match(lookup_value,lookup_array,match_type)
Returns the relative position of a value in an array that matches a specified value in a specified order.
=MATCH("dog",A1:A10,0) would return the number 2 if the text "dog" was in cell A2. This is because "dog" is in the second row of the range A1:A10.
The optional argument "match_type" is defined as shown below from the Excel help.
Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.
If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.
If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A,...2, 1, 0, -1, -2,..., and so on.
If match_type is omitted, it is assumed to be 1.
Syntax =max(number1,number2,...)
Returns the maximum value in a range of values.
=MAX(A1:A10) would return the number 50 if 50 was the maximum number in the range A1:A10
=MAX(A1:A10,D1:D10) This would return the number 100 if the number 100 was the maximum value in the two ranges A1:A10 and D1:D10.
The MAX function can take up to 30 arguments
Syntax =mid(text,start_num,num_chars)
Returns a nominated amount of characters from a text string, starting from the position as stated by start_num
=MID("Hello",2,3) This would return the text "ell". It has extracted three (3) text characters as specified by num_chars from within the text "Hello" Starting from the second (2) character as specified by start_num
Syntax =min(number1,number2,...)
Returns the minimum value in a range of values.
=MIN(A1:A10) would return the number 10 if 10 was the minimum number in the range A1:A10
=MIN(A1:A10,D1:D10) would return the number 5 if the number 5 was the minimum value in the two ranges A1:A10 and D1:D10. The MIN function can take up to 30 arguments.
Syntax =proper(text)
Capitalizes the first letter of each word(s) in the specified text.
=PROPER("hi my name is david") This would return a text result of: "Hi My Name Is David"
Syntax =right(text,num_chars)
Returns the right most character(s) as nominated by "num_chars"
=RIGHT("bullet") This would return the text "t".
As you can see, if the "num_chars" argument is omitted the default is 1
=RIGHT("bullet",2) This would return the text "et".
Syntax =small(array,k)
Returns the k-th smallest value in a set of values.
=SMALL(A1:A10,2) would return the second (2) smallest value in the range A1:A10
Syntax =sumif(range,criteria,sum_range)
Sums a range of cells as set by the criteria you specify
=SUMIF(A1:A10,">10",B1:B10) would sum all the cells in the range B1:B10 only if the cell on the same row in column A or C is greater than 10.
=SUMIF(A1:C10,">10") would sum all the cells in the range A1:C10 that are greater than 10. As you can see if the "sum_range" argument is omitted the default sum range is the range argument.
Syntax =vlookup(lookup_value,table_array,col_index_num,range_lookup)
Looks for a value in the first column of the table_array you nominate and returns the value from the same row in a column you specify by col_index_num.
=VLOOKUP("Dog",A1:E10,3,FALSE) Would return the value 300 if the word "Dog" was within the range A1:A10 (the first column of table_array) and the number 300 was in same row as "Dog" but in the third column.
The optional argument "range_lookup" can take either TRUE or FALSE, if omitted it is considered TRUE.
By using FALSE we are telling it to find an exact match (but not case sensitive).
When the lookup_value is text, FALSE is nearly always used.
If the lookup_value is a numeric value then using TRUE or omitting the argument will result in the argument lookup_value returning the row of the closest match. For this reason if TRUE is used when lookup_value is a number, the first column of your table_array should be sorted in Ascending order.
All of the above functions are only a very small portion of the functions available to Excel. We have shown examples of what we believe to be the most popular functions. We have based this on the many years we have spent helping other Excel users all over the world. As we stated at the start of the examples they are not necessarily very practical examples, we have done this as we believe it is far more important to understand how the functions work as opposed to being able to copy and paste a useable example into a Workbook. We urge you to read the help files on any function that you may need. We realize fully that the help in Excel can seem a bit daunting at times, but it is important that you become familiar with it, as it will always your best and most accessible source of help.
We have attached a Workbook that has some of these functions put to practical use, most of which are nested within each other. There is also a Worksheet called Exercise that we would like you to try and supply the answers to. To do this we would like you to use the Conditional Sum Wizard and the Lookup Wizard. Both of these can be found under Tools>Wizard once they are installed as described at the start of this lesson.
You will find that these will often write what is known as array formulas this will be a good lead into our next lesson where we can look at ways and means to extract information from a table or database. This will cover arrays and the database functions (which we have purposely omitted in the above examples). Make use of the help in Excel to learn about the two wizards. You will find that using the wizards without any prior knowledge will make you think. Be careful you do not become reliant on them though.