Functions
in Excel are the in-built method of calculation, which can be invoked anytime
the user wants to make use of it. e.g SUM for addition, MAX for maximum and IF
– for conditional statement known as logical function.
STATISTICAL FUNCTIONS
SUM FUNCTIONS:This is used to sum the values
in a list e.g to calculate the total for first Row of the above table follow
these steps:
vPlace
the cursor at cell E3
vType
= Sum(B3:D3) and press Enter Key
AVERAGE FUNCTION:This
is used to find the average of the values in a list. To calculate Average for
Cell B3…D3 follow the following steps:
vPlace
the cursor at cell F3
vType
= AVERAGE(B3:D3) and press Enter Key
POSITION FUNCTION: This is used to get the
rank of the values in a list. To calculate the POSITION for Cell B3…D3. Follow
the steps:
vPlace
the Cursor at E3
vType
= RANK(B3, $E$3:E7) and Enter
COUNT FUNCTION:This is used to count the number of items in a
list e.g. =COUNT(B3:D3)
MAXIMUM FUNCTION: This is used to get the largest value in a list
e.g =MAX(B3)
MINIMUM FUNCTION: This is used to get the smallest value in a list
e.g =MIN(B3:D3)
PRODUCT FUNCTION: This is used to get the
product of two or more numbers e.g =Product(B3:D3)
MATHEMATICAL FUNCTIONS
SQUARE ROOT: This is used to get the square root of one or more numbers e.g =SQRT(B3:D3)
ROUND FUNCTION: This is used to round or
approximate figures e.g =ROUND(62.2533,1) and press enter key we will get 62.3
that is been rounded to 1 decimal place.
Calculation
is the process of computing formulas and then displaying the result as values
in the cells that contain the formulas.
It
is very easy to perform Arithmetic & Logical operations or calculations in
Microsoft Excel. In Microsoft Excel, figures are not referenced but the cell.
It is wrong to calculate the value you have in a cell by referring to that
value. You can only refer to the cell where you have the value. For you to know
refer to a Cell, you must first of all call it by the column follow by the row
e.g A1, B6, H7, C4, etc.
OPERATORS
Operators
are the symbols or signs used for calculations in Microsoft Excel. Below are
some operators that can be used in MS-EXCEL.
COMMON OPERATORS
^
-
+
/
%
*
>
<
>=
<=
=
<
>
:
,
There
are two methods of calculation in Microsoft Excel. These are:
TITLE BAR:This is a bar at the top of an application or
document that indicates its Name.
MENU BAR:This is a list commands displayed below the title bar of an application which when click draws down a pull down menu.
STANDARD TOOLBAR: A bar containing the buttons that when clicked produces instant actions like Bold, Italic, Underline etc.
COLUMNS These are the vertical lines that run across worksheet from the top to bottom, labeled alphabetically i.e A B C D E F… IV. There are 256 Columns in Microsoft Excel.
ROWS: These are the horizontal lines that runs across the worksheet from left to right, labeled numerically i.e. 1 2 3 4 5 6 ………………… 65536
FORMULA BAR: As you type in data into the active cell, it appears in the formula bar, which you can also use to edit your cell content.
CELL:The intersection of each Column and Row is referred to as a Cell. Each cell can hold a single piece of information known as “ENTRY”. When you select a cell on a worksheet, the cell becomes active and that’s what is called “ACTIVE CELL” in Microsoft Excel.
NAME BOX:The reference name or identifier for a particular cell is known as the cell address.
RANGE: A range is a collection of two or more cells in a row or column within a worksheet.
WORKSHEET: This is the main Microsoft Excel workspace. A single sheet contains a maximum of 65536 Row and 256 Columns. Microsoft Excel displays a particular sheet you are working on by showing a tick border around the sheet name. that’s what is referred to as “ACTIVE SHEET” in Microsoft Excel.
SHEET TAB:Allows you to switch between worksheet and also shows you the worksheet number and/or name.
SCROLL BAR: A gray horizontal/vertical line that enables the mouse to move around the screen. It contains the scroll arrow and tab.
WORKBOOK: In Microsoft Excel, documents are called workbook or books unlike Microsoft Word where we have things like “Document 1, Document, Document 3 etc”. Workbooks keep the sheets (pages) you need together in the same file. The workbook can contain different charts, sheets, images/pictures etc. you can switch between sheets by clicking the sheet number or name tabs at the bottom of a workbook. In summary, workbook is the collection of worksheets. There are about 255 worksheets in a workbook. A workbook is also called a spreadsheet in Microsoft Excel. It is the printable document that you use to store and manipulate data.
CELL POINTER:It is the rectangular bar in the worksheet that moves around the screen. It sometimes indicates the position of your cell at any point in the worksheet. It also makes a cell active. Remember that whatever you type must appear in the active cell.
Electronic
Spreadsheet is a financial management system use in calculating and analyzing
numerical data. It is a windows application program or software that provides
users with a powerful spreadsheet features and environment. It is referred to
as an electronic spreadsheet because it produces the facilities that you have
in a document on an electronic system (i.e the computer).
MICROSOFT EXCEL
Microsoft
Excel is a grid of numbered rows and columns intersection in cells. It can be
defined as any documentation area that consists of series of rows and columns,
which is suitable for any form of data analysis. Example of a spreadsheet is
the account ledger book.
Microsoft
Excel can be referred to as a peace of electronic spreadsheet. Meaning it’s a
package, software or an application under electronic spreadsheet but electronic
spreadsheet is generally used for all the Financial Management Packages.
Another typical example of an electronic spreadsheet package is Lotus 1-2-3.
ADVANTAGES OF ELECTRONIC
SPREADSHEET OVER ITS MANUAL TYPE
Computerized
ledger has some advantages over its manual type, which includes the following:
Easy
insertion of formulas into the spreadsheet for automatic calculations
Easy
editing of entries
Instant
creating and printing of graphs for pictorial representation of data
Organization
of numerical data into rows and columns
Provision
of financial, statistical and mathematical functions
Find the lowest and highest values with the MIN and MAX functions. Use with the IF function, to create MIN IF and MAX IF formulas.
MIN Function
To find the lowest value in a range of cells, use the MIN function. For example, this formula will find the lowest value in cells H2:H17
=MIN(H2:H17)
MAX Function
To find the highest value in a range of cells, use the MAX function. For example, this formula will find the highest value in cells H2:H17
=MAX(H2:H17)
MIN IF Formula
Although Excel has a SUMIF function and a COUNTIF function, there is no MINIF function. To create your own MINIF, you can combine the MIN and IF functions in an array formula.
In this example, we'll find the lowest value for a specific product in a sales list with multiple products. The formula will be entered in cell D2, then copied down to D5.
First, enter the MIN and IF functions, and their opening brackets:
=MIN(IF(
Next, select the product names in the sales list, and press the F4 key, to lock the reference.
=MIN(IF($G$2:$G$17
Type an equal sign, and click on the cell with the product name criteria. This reference will not be locked.
=MIN(IF($G$2:$G$17=C2
Type a comma, then select the quantity cells in the sales list. Press the F4 key, to lock this reference.
=MIN(IF($G$2:$G$17=C2,$H$2:$H$17
To finish the formula, type two closing brackets, and then press Ctrl+Shift+Enter to array-enter the formula.
=MIN(IF($G$2:$G$17=C2,$H$2:$H$17))
In the formula in the Formula Bar, shown above, you can see that curly brackets were automatically added at the start and end of the formula, because it was array-entered.
If you don't see those curly brackets, you pressed Enter, instead of Ctrl + Shift + Enter. To fix it, click somewhere in the formula bar, and press Ctrl + Shift + Enter.
Then, copy the formula down, to the rows below, to see the minumum for each of the products.
MAX IF Formula
Although Excel has a SUMIF function and a COUNTIF function, there is no MAXIF function. To create your own MAXIF, you can combine the MAX and IF functions in an array formula.
In this example, we'll find the highest value for a specific product in a sales list with multiple products.
First, enter the MAX and IF functions, and their opening brackets:
=MAX(IF(
Next, select the product names in the sales list, and press the F4 key, to lock the reference.
=MAX(IF($G$2:$G$17
Type an equal sign, and click on the cell with the product name criteria. This reference will not be locked.
=MAX(IF($G$2:$G$17=C2
Type a comma, then select the quantity cells in the sales list. Press the F4 key, to lock this reference.
=MAX(IF($G$2:$G$17=C2,$H$2:$H$17
To finish the formula, type two closing brackets, and then press Ctrl+Shift+Enter to array-enter the formula.
=MAX(IF($G$2:$G$17=C2,$H$2:$H$17))
In the formula in the Formula Bar, shown above, you can see that curly brackets were automatically added at the start and end of the formula, because it was array-entered.
If you don't see those curly brackets, you pressed Enter, instead of Ctrl + Shift + Enter. To fix it, click somewhere in the formula bar, and press Ctrl + Shift + Enter.
Then, copy the formula down, to the rows below, to see the minumum for each of the products.
MAX IF With Multiple Criteria
In the previous example, we found the highest quantity for a specific product, so there was just one criterion -- the product name.
You can also use the MAX IF technique with multiple criteria, by including additional IF functions in the formula. For example, if the data includes a customer name, we could find the highest quantity for each product, for a specific customer.
The customer name is entered in cell D1. In cells C4:C7, the product names are listed.
Enter the following formula in cell D4, and array-enter it, by pressing Ctrl+Shift+Enter. Then, copy the formula down to cell D7.
The formula checks column H for product names that match the entry in cell C4.
Then, it checks column I for customer names that match the name in cell D1.
For those rows, it finds the highest amount in column J.
Get Latest Price for Specific Product
If you have a list of product prices and dates, you can use the MAX/IF technique to find the latest pricing date for a specific product. Then, use SUMIFS or SUMPRODUCT to get the price for that product, on that date.
In this example, there is a price list in cells A1:C9.
To find the latest price for a specific product, start by using MAX and IF, to get the latest date for that product. The product name -- Pens -- is entered in cell A12.
To find the latest pricing date for that product, enter the following formula in cell B12, and press Ctrl + Shift + Enter:
=MAX(IF($A$2:$A$9=A12,$B$2:$B$9))
Next, to find the price for that product, on that date, enter the following SUMIFS formula in cell C12:
=SUMIFS($C$2:$C$9,$A$2:$A$9,A12,$B$2:$B$9,B12)
The SUMIFS function is available in Excel 2007, and later versions. For earlier versions of Excel, you can use the SUMPRODUCT function:
How to use a VLOOKUP formula to get data from an Excel list. Troubleshoot VLOOKUP when things go wrong. Step-by-step videos and free workbooks with VLOOKUP examples.
VLOOKUP Video
Watch the steps for creating a VLOOKUP formula in the Product Price lookup video, show below. The written instructions are below the video. Get the Product Price Lookup sample file to follow along with the video (download file #4).
Select a Location for a Lookup Table
It's a good idea to store each lookup table on a separate worksheet in the workbook. Then, as you add and delete rows in the lookup tables, you won't accidentally add or delete rows in any other table.
In this example, the lookup table is on a sheet named Products.
Create a Lookup Table
Lookup formulas can work vertically, looking for values down a column, or they can work horizontally, looking for values across a row. In this example, the information will be stored vertically, with values down a column, and later we'll use a VLookup formula to do a vertical lookup.
Enter the headings in the first row
The first column should contain the unique key values on which you will base the lookup. In this example, you can find the price for a specific product code.
If you have other data on the worksheet, leave at least one blank row at the bottom of the table, and one blank column at the right of the table, to separate the lookup table from the other data.
Note: To make it easier to refer to the table, you can name the range. There are instructions here: Naming a Range▲TOP
VLOOKUP Function Arguments
The VLOOKUP function has four arguments:
lookup_value: What value do you want to look up? In this example, the product code is in cell A7, and you want to find its product name.
table_array: Where is the lookup table? If you use an absolute reference ($A$2:$C$5), instead of a relative reference (A2:C5), it will be easier to copy to formula to other cells. Or, name the lookup table, and refer to it by name.
col_index_num: Which column has the value you want returned? In this example, the product names are in the second column of the lookup table.
[range_lookup]: Do you want an exact match? Is an approximate match okay? If you use TRUE as the last argument, or omit the last argument, an approximate match can be returned. This example has FALSE as the last argument, so if the product code is not found, the result will be #N/A. (Note: Excel is rather forgiving, and will accept 0 instead of FALSE, and 1 instead of TRUE.)
Create a VLookup formula
Once you have created the lookup table, you can create other formulas in the workbook, and pull information from the product list. For example, to prepare an invoice, you can enter a product code, and formulas will get the product name or price from the product table.
In this example, the invoice is created on a sheet named Invoice. The VLOOKUP formula should find an exact match for the product code, and return the product name.
To create the VLOOKUP formula that calculates the product price, follow these steps:
Select the Invoice sheet
Enter product code A23 in cell A7
In cell B7, start the VLOOKUP formula: =VLOOKUP(
Click on cell A7 to enter its reference.
Type a comma, to end the first argument
Select the Products sheet
Select cells A2:C5 (the product list)
Press the F4 key, to change the cell references to absolute. The formula should now look like this: =VLOOKUP(A7,Products!$A$2:$C$5
Type a comma to end the second argument.
Type a 2, which is the column in the lookup table that contains the Product name.
Type a comma to end the third argument.
Type FALSE, to specify that an exact match for the product code is found, and add the closing bracket.
The formula should now look like this: =VLOOKUP(A7,Products!$A$2:$C$5,2,FALSE)
Press the Enter key to complete the formula. The product name will be displayed.
Note: To return the product price, create a VLOOKUP formula that refers to column 3 of the lookup table. For example, enter the following formula in cell C7: =VLOOKUP(A7,Products!$A$2:$C$5,3,FALSE)▲TOP
VLOOKUP Formula for Range of Values
In some situations, an approximate match is preferred, so several values will return the same result. For example, when grading student papers, all papers with a grade of 85 or over should receive an A grade.
To view the steps for creating this formula, please watch the VLOOKUP video shown below. The written instructions are below the video.
In this example, the lookup table is created on a sheet named Grades. To create the lookup table, enter the minimum score for each grade in column A. Enter the matching Grade in column B. Sort the Scores in Ascending order.
Cells A2:B6 were named GradeList.
The scores are entered on a sheet named Report Card, where a VLOOKUP formula calculates the grade.
On the Report Card sheet, in cell B4, enter the score 77.
In cell C4, enter the VLOOKUP formula: =VLOOKUP(B4,GradeList,2,TRUE)
Press the Enter key, and the grade for English -- B -- is returned.
In the screen shot below, the formula has been copied down to row 6, and the you can see the formula in cell C6.
Combine VLOOKUP With MATCH
Instead of typing the column number into a VLOOKUP formula, use the MATCH function to find the correct column in the lookup table. This has a couple of benefits:
Makes the formula flexible, so it's easier to copy the formula across a worksheet.
Can prevent problems if new columns are added in the lookup table, or if the lookup columns are rearranged.
This video shows the steps, and there are written instructions for another example, below the video.
VLOOKUP With MATCH for Order Details
In this example, a VLOOKUP formula will return the order details from a lookup table, based on the order ID number. Here is the lookup table, named tblOrders.
NOTE: This example is in Sample Workbook #1, on the sheet named OrdersMATCH.
Here is the worksheet with the VLOOKUP formulas. We want the Region, Order Date and Order Amount for each order, so 3 VLOOKUP formulas are needed.
If the column numbers are typed in the formula, a different formula is needed in each column:
Region: =VLOOKUP($B6,tblOrdersALL,2,0)
OrderDate: =VLOOKUP($B6,tblOrdersALL,3,0)
OrderAmt: =VLOOKUP($B6,tblOrdersALL,4,0)
The MATCH Function
Instead of typing the column number in the VLOOKUP formula, we can use the MATCH function. The MATCH function finds the position of an item in a list, and returns the position number.
In the screen shot below, the MATCH formula returns 2 as the position of "Region", in the heading cells (A1:D1) for the lookup table.
=MATCH(C5,Orders_ALL!$A$1:$D$1,0)
NOTE: For this technique to work correctly, the headings on the VLOOKUP sheet must match the lookup table headings exactly. To ensure an exact match, the VLOOKUP heading cells are linked to the lookup table heading cells.
Add MATCH to VLOOKUP
To add the MATCH function to the VLOOKUP formula, just replace the typed column number
Now, instead of needing a different formula in each column, you can copy the formula across, and use the same formula in all the columns. In each column, it will refer to the heading cell in that column, and find its position in the lookup table.
NOTE: If you are filling the formula across columns with different formatting, follow these steps:
Select the cell with the formula that you want to copy
Point to the fill handle on the selected cell (small square at the bottom right corner)
Press the RIGHT mouse button, and drag across to the last cell that needs the formula
Release the mouse button, and click on Fill Without Formatting
Combine IF and VLOOKUP
You can use an IF formula with a VLookup formula, to return exact values if found, and an empty string if not found.
To see the steps for setting up the IF and VLOOKUP formula, you can watch this short video. The written instructions are below the video.
To hide errors by combining IF with VLOOKUP, follow these steps:
On the Invoice sheet, in cell A8, enter the product code A28. If the VLookup formula in cell B8 has FALSE as the fourth argument, the result is #N/A, because there is no exact match for the product code in the lookup table.
Wrap the VLookup formula with an IF formula (in this example the product list has been named), using the ISNA function to check for an #N/A error:
Press the Enter key, and cell appears blank. Because no exact match was found, the VLookup formula returned an #N/A, so the ISNA function result is TRUE. The IF formula converted this to an empty string.
If the lookup table contains any blank cells, a VLOOKUP formula will return a zero, instead of a blank cell. You can use nested IFs to handle the #N/A results, and the empty cell results. For example: =IF(ISNA(VLOOKUP(A8,ProductList,2,FALSE)),"", IF(VLOOKUP(A8,ProductList,2,FALSE)="","", VLOOKUP(A8,ProductList,2,FALSE)))
Combine IFERROR and VLOOKUP
Thanks to Chip Pearson for suggesting this formula.
In Excel 2007, a new function, IFERROR, was introduced. You could use an IFERROR formula with VLookup to check several tables for a value, and return the related information when found. In this example, three regions, West, East and Central, have order sheets. On each sheet is a named range -- OrdersW, OrdersE and OrdersC.
On a sheet named Orders, you can enter an Order ID, then use a VLOOKUP with IFERROR to check each named range, and view the information about the selected order.
On the Order sheet, in cell B6, enter a 4 as the OrderID. That order was placed in the Central region.
To simply check the East region's table, the VLOOKUP formula in cell C6 would be: =VLOOKUP(B6,OrdersE,2,FALSE)
Press the Enter key, and the VLOOKUP formula returns an #N/A, because Order ID 4 is not in the East regions order table.
Because an order could have been placed in any of the three regions, you need a formula that will check each table. If the order ID is not found in the first table, the formula should check second table. If the order ID is not in the second table, it should check the third table. If the order ID isn't in the third table, then a "Not Found" message should appear in the cell.
The IFERROR formula lets you check a value, then specify what to do if an error is found. If you use IFERROR with the existing formula, you can show "Not Found", instead of the #N/A error: =IFERROR(VLOOKUP(B6,OrdersE,2,FALSE),"Not Found")
To check all three tables, you can next IFERROR and VLOOKUP formulas:
This checks the OrdersE table and if an error is found, checks OrdersW table, then OrdersC. If the OrderID is not found in any of the three tables, the Not Found message is shown in the cell.
You can also check multiple tables in older versions of Excel, where IFERROR is not available, using a longer formula:
In some tables, there might not be unique values any column in the lookup table. For example, in the table shown below, Jacket is listed twice in column A. However, there is only one record for each jacket and size combination -- Jacket Medium in row 4 and Jacket Large in row 5.
If you need to find the price for a large jacket, a VLOOKUP based only on column A would return the price for the first jacket listed (Medium). You would be underpricing the jacket -- selling it for 60.00, instead of 65.00.
To create unique lookup values, you can insert a new column at the left side of the table, and use a formula to combine the product and size. In cell A2, the formula combines the value in B2 and the pipe character and the value in C2.
=B2 & "|" &C2
Copy that formula down to the last row of data, so each row has a unique value in column A.
Note: Instead of the pipe character, you could use another character that isn't included in your data.
Then, in a VLOOKUP formula, combine the product and size as the Lookup_value. In cell H1, the formula combines the value in F1 and the pipe character and the value in G1.
=VLOOKUP(F1 & "|" &G1,$A$2:$D$5,4,FALSE)
Note: The price is now in column 4, instead of column 3. ▲TOP
Troubleshoot the VLOOKUP formula
Your VLOOKUP formula may return an #N/A, even though the value you're looking for appears to be in the lookup table. Common causes for this are:
A common cause for this error is that one of the values is a number, and the other is text. For example, the lookup table may contain '123 (text), and the value to look up is 123 (a number).
If possible, convert the text to numbers, using one of the methods shown here: Convert Text to Numbers
If you can't convert the data, you can convert the lookup value within the VLOOKUP formula, using one of the solutions below:
1. Lookup values are Text, and the table contains Numbers
If the lookup table contains numbers, and the value to look up is text, use a formula similar to the following:
=VLOOKUP(--A7,Products!$A$2:$C$5,3,FALSE)
The double unary (--) converts text to a number, and will work correctly even if the lookup values are numbers.
2. Lookup values are Numbers, and the table contains Text
If the lookup table contains text, and the value to look up is numeric, use a formula similar to the following:
=VLOOKUP(A7 & ""),Products!$A$2:$C$5,3,FALSE) OR =VLOOKUP(TEXT(A7,"00000"),Products!$A$2:$C$5,3,FALSE)
The TEXT function converts a number to text, and will work correctly even if the lookup values are text. In the first example, the & operator creates a text string from an unformatted number. In the second example, a number formatted with leading zeros (e.g. 00123) would match a text "number" with leading zeros.
To see the steps for fixing the VLOOKUP problem when the lookup table has text values, watch this short video tutorial.
Spaces in one value, and not the other
Another potential cause for no matching value being found is a difference in spaces. One of the values may contain leading spaces (or trailing, or embedded spaces), and the other doesn't. To test the values, you can use the LEN function, to check the length of each value.
For example: =LEN(A7) will return the number of characters in cell A7. It should be equal to the number of characters in the matching cell in the lookup table.
If possible, remove the unnecessary spaces, and the VLOOKUP formula should work correctly. If you can't remove the spaces, use the TRIM function in the VLOOKUP, to remove leading, trailing or duplicate spaces. For example:
=VLOOKUP(TRIM(A7),ProductList,2,FALSE)
Other Characters
If TRIM function alone doesn't solve the problem, you can try one of the following suggestions:
Another way to fix VLOOKUP problems is with the CLEAN function, which can remove some unwanted characters from the text. There is more information on the CLEAN function in this Contextures blog post: 30 Excel Functions in 30 Days: 29 - CLEAN
Web characters in one value, and not the other
If you copied data from a web page, it may contain HTML non-breaking space ( ) characters. David McRitchie has written a macro to remove them, along with other spaces characters -- https://www.mvps.org/dmcritchie/excel/join.htm#trimall
Problems When Sorting VLOOKUP formula
A VLOOKUP formula may return the correct results at first, but then shows incorrect results if the list of items is sorted. This can occur if the reference to the Lookup value includes a sheet name. For example:
This type of reference is created if you click on another sheet while building the formula. As soon as you do that, Excel adds the sheet name to any subsequent references in the formula.
In the screen shot above, Dress is in cell B9, and cell C9 shows the correct price of $30.
However, after sorting the products A-Z, the Dress moves up to cell B5, but the formula in cell C5 continues to refer to cell B9. Because of the sheet names in the references, Excel retains the original references, instead of keeping a reference to the current row. Cell C5 is showing the price for a Sweater, instead of a Dress. ▲TOP
Fix the Problem
To solve the problem, remove any unnecessary sheet names from the VLOOKUP cell references. Here is the revised formula for cell C5:
=VLOOKUP(B5,Products!$B$2:$C$6,2,FALSE)
After the unnecessary sheet names are removed, the list can be safely sorted, and the correct results will show for each item.
Download the Sample Files
1. Get the VLOOKUP sample workbook (Excel 2007 and later). The zipped file is in xlsx format, and does not contain any macros.
2. For Excel 2003, get this version of the VLOOKUP sample file. The zipped file is in xls format, and does not contain any macros.
3. To see the problem that can occur when sorting with VLOOKUP, get this VLOOKUP Sorting problem sample file. The zipped file is in xlsx format, and does not contain any macros.
4. Get the Product Price Lookup workbook, used in the video tutorial. The zipped file is in xlsx format, and does not contain any macros.
