"What are some of the most popular Microsoft Excel Formulas and how can they be used?"
Microsoft Excel is a very useful tool that can be used to accomplished a whole host of tasks. Before you can use Microsoft Excel though there are a few basic things to note. Every activity is done in cells. A cell is an intersection between a row and a column.
Another key element to take note of when using Microsoft Excel with its wide array of formulas is that of ranges. A range is a group of cells running horizontally or vertically that are treated as a unit.
a. (B3:B7) (Vertical range)
b. (C3:C7) (Vertical range)
c. (B7:D7) (Horizontal range)
A formula is an instruction which is used to perform calculations (using data contained in cells) as well as to display the end results. A formula may also be used to display a result based on an existing condition within one cell or a range of cells. Some of the features of formulas are:
a. A formula is entered into the cell in which the result is to be displayed;
b. A formula must begin with an equal sign (=) ;
c. A formula includes arguments(such as cell references, text or numbers) and operators:
i Addition (plus sign)
ii Subtraction -
iii Multiplication *
iv Division /
v Exponential ^ (raising to a power)
Using the diagram illustrated below.
NB.In the image above the function =B7^C7 will result in 625. The content in cell B7 will be raised to the power of the content of cell C7. Thus, 25 raised to the power of 2.
The results will be displayed in any cell where you place the formula.
A function is a predefined formula in Microsoft Excel that can automatically calculate the results, perform worksheet actions or assist with decision making based on the information provided in the work sheet. Examples of functions (using the figure below) are the:
SUM function Example =sum(B2:B5)
AVERAGE function Example =average(B2:B5)
MIN function Example = min(B2:B5)
MAX function Example = max(B2:B5)
COUNT function Example = count(B2:B5)
To add the figures that are in the Income section there are several ways that one can choose to do this.
Three of these methods are:
1. Add cells individually
(then press enter)
2. Use the sum function =Sum(B2:B5) (then press enter)
3. Highlight the figures in the range B2:B6 (where B6 will be the cell where the answer will appear), then click on the auto sum button on the toolbar
auto sum button
For additional functions click on the drop-down arrow beside the AutoSum symbol.
AutoSum drop-down arrow, Popup menu
To add the items in the Expenses column then one of the three methods illustrated above can also be used.
If you were to use the sum operation to handle values that were not in a straight line (in a single range), such as with the meat quantities illustration below, you could insert different ranges within the formula that are separated by commas.
Overview of functions
This function is used to find the average of a set of values within a specified range. The general format/syntax of this function is
In the image above the formula to find the average expenses would be =average(B2:B5).
MAX (maximum) function
This function is used to find the largest value in a set of values in a row or column.
Using this example, the formula to find the average expenses would be =max(B2:B5).
Min (minimum) function
This function is used to find the smallest value in a set of values in a row or column. The simple formula in which this function be used would of course be =min(B2:B5).
This function states the number of entries that have been made in its argument list.
COUNT is specifically used for the counting of numbers. However, if you wished to count words or letters you'll have to use the COUNTA function instead. So this function would be =COUNTA(A11:A22) if you wished to count the number of products on the list.
Lets take a look at these formulas again. So far we have been using these formulas utilizing a single range of values. However, if we don't wish to use the entire list we can pick out the items in which we are interested. To do this we would need to use several ranges separated by commas within the argument section of the formula. Lets look at the meat quantities in the illustration below.
Other Interesting Formulas
The "IF" function is one of a number of very versatile formulas that can be used to execute a wide variety of actions. The general syntax of this function is
=if(condition is true, DO this, or Do that)
NB. Each section is separated by a comma.
With reference to the image below.
Examples of conditions include:
B6>B13 (The content of cell B6 is greater than the content of cell B13)
B6B13 (The content of cell B6 is not equal to the content of cell B13)
The IF function can be used to either carry out a calculation or to display a word, sentence, letter or even a number based on the state of a condition. Therefore the IF function can execute an action depending on whether a condition is true or false.
How can we use these conditions?
If the condition B6>B13 is true then we can set up a particular calculation:
B6 - B13 (The content of cell B13 will be subtracted from that of B6 ) and the result can be displayed in cell B15. It can be actually done like this:
=if(B6>B13, B6 - B13, 0)
NB. Once again notice how each section is separated by a comma. Failure to include this comma in this formula will cause an error.
On the other hand if it is not true that the content of B6 is greater than that of B13, then 0 (zero) will be displayed in the assigned result cell.
Instead of performing a calculation using the same formula B6>B13, you could set up an operation to display the words "Profit" (IF the content of B6 is greater than the content of B13) or the word "Loss" (if it is false that the content of B6 is greater than that of B13). For example, If B6 contains the value 1000 and B13 contains 500, then the condition B6>B13 would be true, and the word "profit" can be set to be displayed in any empty cell.
It can be actually done like this:
=if(B6>B13, "Profit", "Loss")
Here are some other applications of the "IF" function.
i. If we wish to calculate 15% of the profit if it is less than 20000, or 25% if it is greater than 20000, you can do it like this:
=if(B15<20000, B15*0.15, B15*0.25)
ii. If you wish to add 15% to the profit if it is less than 20000 or add 25% if it is greater than 20000, you can do it like this:
=if(B15<20000, (B15*0.15) plus sign B15, (B15*0.25) plus sign B15)
NB. In the formula above insert the actual plus sign wherever you see the words "plus sign".
All the examples above have one thing in common, they all involve the use of one condition in order to execute one of two possible actions. However, it is possible to create a formula that evaluate multiple conditions in order to execute one of a number of possible actions. We can do this by using "Nested IFs".
Lets now look at it.
If you would want to display some statements based on the amount of profit calculated. For example if you wish the words "less than 100" if the profit is less than 100, "less than 200" if the profit is less than 200, "greater than 200" if the profit was greater than 200.
It can be done like this:
The VLOOKUP is in essence a search function that enables you to vertically search for a value in a column of a table array. If a match is found, then a value will be returned from another column in this same row. The basic syntax of this function is as follows:
The illustration below illustrates a simple setup of a table on which the VLOOKUP function can be used.
Common characteristics of this operation are that:
a. Column F in the image immediately below is used to display the result of the search.
b. There must be an array (a table that is searched in order to return its content to the result area). In the case of the diagram below the array would be A4:B6. You need however to use anchors in order for the operation to be carried out within the set limit. To do this you may need to insert the array as $A$4:$B$6. (The period (.)at the end is not included).
How can we now actually execute a VLOOKUP search?
1. The first step is to click the cell in which you desire the result of your search to be displayed.
2. In Microsoft Excel 2007 in the menu bar click on Formulas;
3. Click on the Lookup and Reference button
4. After clicking on the Vlookup option you will encounter the next popup window.
5. Type in the:
a. Lookup_value – Click on the first cell of the column for which the search will be based. In relation to the illustration below this would be cell D4.
b. Table_array - As stated earlier this is a smaller table ( that can be located anywhere in your worksheet), on which the search will be conducted. This table supplies the results of the search. The array in this case would be A4:B6.
c. Col_index_num – This indicates which column in the array will contain the results. In the case below we would be referring to column 2. This table array can consist of two or more columns. Therefore you choose the column that contains the data you want to be returned.
6. When these boxes have been correctly filled then click on OK.
7. To complete the VLOOKUP operation simply click on the fill handle at the bottom right and corner of the first “result cell”, and then drag it downward.