WHAT ARE FUNCTION?
A function is a built-in formula that takes a series of value, uses it to perform an operation, and return the result of the operation. It can be used as a part of a formula i.e. we can create formula which contain different functions along with numbers and cell-addresses.
11 CATEGORIES OF FUNCTIONS
Excel has eleven categories of function. They are as follows :
Though the number of function available in each of the above categories is extremely large, we will be using only a few functions in our day-to-day work.
PART OF A FUNCTION
Functions usually have two parts the function name and the arguments. The arguments have to be enclosed n a pair or round brackets. The function which has been used, usually performs a set of actions to arrive at a Value. It return this Value. So, We can use a function in place of a value because the function used will return a value, Earlier, we had seen an example of a function called =SUM(..„).
Have are some other simple functions :
=AVERAGE () Returns the average of the value passed as arguments.
=MAX ()
=MIN ()
=COUNT () Returns the Number of not null entries passed as arguments.
=NOW ()
The above examples are simple function which do some simple statistical operations. The arguments for these functions are to be specified in the brackets. These arguments could be a series of numbers, or they could be cell addresss or range addresses. We can also use range names as arguments.
Let us conside a simple example of a function. This is a mathematical function called SQRT (). it fiend the square root of the argument and returns it. So, if a cell contains the formula =SQRT(144). It will display the value 12, whereas the formula bar will display the formula i.e. =SQRT( 144). Instead of the number 144, we could also have given a cell address. Some functions do not require any arguments. An example of this type is the =NOW() function, which returns the system date and time.
ENTERING A FUNCTION
Now that we know what a function is made up of, we can go on to examine how functions can be entered. There are two ways of entering a function. They are as follows :
Typing the function manually.
Pasting the function using the FUNCTION WIZARD
TYPING A FUNCTION
Entering a function is on different from entering any other type of data. First, we select the which should contain the function. After that, we type the “=” sign. Then we type the function and the arguments.
A function can be as simple as the one below :
=SUM (A1:A10)
We can also have complex functions like the one mentioned below :
=IF (SUM(A1:A10)-SUM(B1:B0), “EQUAL”, “NOT EQUAL”)
FUNCTION WIZARD
Another way we can enter functions into our Worksheet is using the function wizard. Earlier we had seen how we could paste Range Names to quickly create powerful formulae. Letting Excel help us enter functions be pasting them ensures that they are spell correctly and reminds us of arguments and order in which the arguments are expected.
Typing a function and pasting it both begin in the same way : we first select the cell where we want the function to calculate a result. To paste a function, we choose the Function….com made from the Insert Menu or by clicking on the Function Wizard Tools on the Standard Toolbar. This displayed in the Function Wizard Category Box. We can scroll through the Function Name box, which contains the list of functions in the selected category, select the function we want.
To enter the function, click Next or press Enter. Excel! Enters in the formula bar an equal sign, the function name, and a set of parentheses. Excel then moves to the Second Function Wizard dialog box. The second Function Wizard dialog box contains one edit box for each argument of the function you selected. A insertion point appears in the edit box of the argument you are entering.
The current value of the function appears in the upper right corner of the dialog box. When you click Finish or average sales in each month.
Lets use some of these functions mentioned earlier in our sample Worksheet, to calculate the Maximum, Minimum and average sales in each month.
Type in labels maximum, Minimum and average in the cells A11 to A13. To calculate the maximum sales for Jan, select the cell B11 and click in the function wizard tool. Select the function called MAX and click on the button called next. In the second step of the function wizard we have to specify the value from which the maximum value has to be extracted In our example since the cells with the sales for month of Jan are in a range, we can specify the range address. The range address can either be typed in or selected by using the click and drag technique.
Once the range has be entered, just click of the Finish button. The function is pasted in a active selected cell. In a same way calculate the Minimum and Average sales for Jan using MIN () and AVERAGE () functions. The arguments will be the same range address.
THE IF () FUNCTION
Finally, we will discuss an important Logical function. Logic mainly deals with conditions. Conditions can either evaluate to true or false. On the evaluation of conditions we do certain tasks, We can do things conditionally by using logic in Excel, too The Excel function which allows us to use logic is IF (). Its syntax is shown below:
IF (logical_test, value_if_true, value if false).
Let us see an example of the function.
=IF(SUM(A1:A10)=SUM(B1:B10),’’
The logical_test argument or the condition in this formula is SUM (A1:A10)=SUM(B1:B10). If it evaluates to True, the value_if_true, the label EQUAL is returned. On the other hand, if the logical_test evaluates to False, the value_if_fales the label NOT EQUAL is returned. In formulae, a label has to be enclosed in a pair of quotes.
In our sample work sheets, lets use the IF () function to calculate the commission earned. Based on the Quarterly sales of each product. If the quarterly sales>1500 the commission will be 10% of the quarterly sales, otherwise, commission will be zero. Select the cell F5 where the function has to appear and selected the function IF () using the function wizard. In the box for Logical_Test type in the condition as E5>1500 (the cell E5 contains the figure for the quarterly sales for the first product). In the value_if_true box the formula will be given as E5*10% and in the value_if_fales box the value will be 0. In the cell F5 the function will evaluates as follows:
=IF (E5>1500, 10%*E5, 0)
It should be understood that we can have nested ifs (one IF within another). It is also possible to have functions as the value_if_true and value_if_fales. This can be used to conditionally assign values to cells. But to selected a function within another function we have to use the Function Wizard button, next to the appropriate argument box from within the Function wizard Dialog Box.
If in the above example the commission was dependent in the following two conditions- Qtr Sales>1800: commission =20% of Qtr Sales Qtr Sales>1300: commission= 10% of Qtr Sales
When we select the IF () function wizard box, the value_if_false box will contain a nested IF() function which will be given using the function wizard button next to this argument box.
In the cell for commission the formula evaluates as follows:
=!F (E5>1800,20%E5,IF(E5>1300,10%
AUTO FORMAT AND OUTLINING
AUTOFORMAT
As against manual formatting, Excel provides us with an automatic formatting feature celled Auto Formatting. It presents samples from which we can pick the appearance we desire for our spreadsheet. Excel’s Auto Format feature uses predefined combinations of number formats, fonts, cell alignments, borders, patterns, shading, column widths, row height and the like. These are called Table Formats. On choosing a Table Format, Excel applied the format to the whole Worksheet or to the like. These are called Table Format. On choosing a Table Format, Excel applied the format to the whole Worksheet or to the selected parts. AutoFormat is smart enough to locate and highlight subtotal and totals for us. We can use these formats as given or overrule some of their characteristics manually, because automatic formatting is sometimes difficult to undo, it is a very good idea to save Worksheet before experimenting with the automatic formats.
APPLYING FORMATS
To use AUTO FORMAT these steps :
to our liking, (if you click an option to
accordingly.)
If we execute the Auto Format command without selecting the range to be formatted, Excel will look for what it thinks is a logical range of cells to format. It will highlight the proposed range for us to confirm. If we want to format a different range, then we cancel Auto Format, select the desired range and then try again.
Different formats are appropriate for different kinds of Worksheets. For example, the Classic formats are excellent for simple columns of numbers with totals and subtotals (Which receive special formatting). The financial formats highlight the correct information in the appropriate. The List-type formats use colours and shading to make long lists of number more readable. The 3D format creates a 3-dimensional effect. If the contents of a Worksheet appear in different levels of importance (i.e. Outline). Auto Format can different levels differently or otherwise distinguish between them.
OUTLINING
The outlining feature provided by Excel is an unique and extremely powerful one. It allows us to have multiple levels of information in a Worksheet. Using outlines, we can together related which are to be subtotalled, have totals etc. Making an outline gives us the advantage of normally seeing the overall picture while allowing us to see details only when necessary. In other words, whenever we do not need to see the details, we can hide them. Excel allows us to outline upto eight levels. Outlining does not in any way affect the formulae in the Worksheet, So, if at any time we feel that the outline is not good enough in either appearance or effect, we can just remove it and have a normal Worksheet.
Outline can be created automatically as well as manually. The outline command allows us to create an outline automatically. The effect of this command can be observed in the Worksheet shown below. Though at first sight the symbols seem intimidating, there is nothing very awesome involved. We will use this ample Worksheet to understand the concept too. To understand outlining, we will first have to know some important terms.
LEVEL
The word level refers to the hierarchy or the data in our spreadsheet. We can have up to eight outline levels in each row or column of a spreadsheet. There are two types of levels : Summary (highest-1) and detail (lowest-8) Summary levels are comprised of detail rows or columns or of lower summary levels. Which are known as Subordinate summary levels.
SUMMARY
A summary level of information summarizes data (e.g. a sub-total) it is usually most convenient to have summarizes below a set of details in the case of rows and on the right of the details in the case of columns. A level is designated as a summary level by Excel if the row or columns a total, or average, or any other summary calculations based on data in derail rows or columns.
DETAIL
Detail in the lowest point on the outlining hierarchy. Detail levels are typically rows or columns of
data.
COLLAPSE
When we collapse a level, we temporarily hide detail or subordinate summary level. The collapsed rows or columns are not deleted from our spreadsheet, but hidden from view.
Rows or columns that contain summary or subordinate summary data, display the collapse symbol (- ), indicating that the detail levels can be hidden. If a row or column shows the expand symbol ( + ), it should be understood that there are hidden detail or subordinate summary levels. We can also tell if an outline is collapsed by looking at the row and column border, if the row numbers or column letters and not contiguous, it means that data is collapsed.
EXPAND
When we expand a level, we display formerly hidden detail levels or subordinate summary levels. Rows or columns that are summary levels display the expand symbol (+), indicating the HIDDEN detail or subordinate summary levels.
CREATING AN OUTLINE AUTOMATICALLY
To create an outline automatically, from the Data Menu, Choose Group and Outline Command, and then choose Auto Outline. If we do not select a range, the entire Worksheet gets outlined. So, if we want only a portion of the Worksheet to be outlined, we should mark that portion as a range. For the outline to get creates correct, the formulae should be consistent in direction. Normally the summary formula is in the row below the rows of detail or in the left of the detail columns. If the position of the summary formula is to be changed, then the Setting option , from the Group and Outline command, under the Data Menu, will have to be selected. The outline dialog box shown below gets displayed. The option and the command buttons available are discussed below.
Automatic styles
Applies built in cell styles for the summary rows and commands of outline. Styles are applied rows and columns.
Summary Rows below Detail
Specifies the location of summary rows in the outline.
Summary Columns to Right of Detail
Specifies the location of summary columns in the outline.
Create
Automatically assigns outline levels based on the formulae in your Worksheet If the Automatic Styles check box is selected, Microsoft Excel applies the built-in cell styles as well.
Apply styles
Applies row and column level styles to the selected outline, or the part of an outline, that does currently have them.
VIEWING THE OUTLINE
Once the Worksheet has been outlined, we will see outlining symbols is along the edges of the Worksheet. If we click on the level marker 1 (of either the row or the column), the highest level i.e. the most summarized version will get displayed. If we click on the last level marker, the Worksheet i.e. including all details, will get displayed clicking on any intermediate level of detail onwards to the highest level of summary. When we have the appropriate levels displayed, we can collapse certain level further and Expand creation levels to get the exact desired picture of the Worksheet.
MANUAL OUTLINING
While automatic outlines are much easier to create, we can make use of the Group and Ungroup commands to create an outline manually is to select the details rows or columns and to Group them. After having Grouped all the details we have to Collapse them so that so that the subordinate summary levels of the same level appear together. Then we can select all these levels and Group them and so on till the entire outline is ready.
Note once items have been collapsed, all formatting, cutting, pasting and similar activities hidden details.
REMOVING AN OUTLINE
It is easy to remove an outline from a Worksheet. From the Data menu, choose Group and Outline and then Clear Outline. You remove the column or row levels (or both) from an outline by ungrouping all of the outline’s levels to the highest level.
SUPPRESSING THE OUTLINE DISPLAY
When you outline a Worksheet, Excel displays some additional symbols above and to the left of the row and column headings. These symbols indicate the structure of your outline, and you can use them to change the level of detail the Excel displays. However, these symbols do take up screen space. If you want to suppress them, choose the Option command from
the Tools Menu, click the View Tab and deselect the Outline Symbols option. To redisplay the outline symbols, reselect the Outline Symbols Option from the option dialog box.
A function is a built-in formula that takes a series of value, uses it to perform an operation, and return the result of the operation. It can be used as a part of a formula i.e. we can create formula which contain different functions along with numbers and cell-addresses.
11 CATEGORIES OF FUNCTIONS
Excel has eleven categories of function. They are as follows :
| Most Recently Used | |
| All | |
| Financial | |
| Date & Time | |
| Math & Trig. | |
| Statistical | |
| Lookup & Reference | |
| DaTabase | |
| Text | |
| Logical | |
| Information |
Though the number of function available in each of the above categories is extremely large, we will be using only a few functions in our day-to-day work.
PART OF A FUNCTION
Functions usually have two parts the function name and the arguments. The arguments have to be enclosed n a pair or round brackets. The function which has been used, usually performs a set of actions to arrive at a Value. It return this Value. So, We can use a function in place of a value because the function used will return a value, Earlier, we had seen an example of a function called =SUM(..„).
Have are some other simple functions :
=AVERAGE () Returns the average of the value passed as arguments.
=MAX ()
=MIN ()
=COUNT () Returns the Number of not null entries passed as arguments.
=NOW ()
The above examples are simple function which do some simple statistical operations. The arguments for these functions are to be specified in the brackets. These arguments could be a series of numbers, or they could be cell addresss or range addresses. We can also use range names as arguments.
Let us conside a simple example of a function. This is a mathematical function called SQRT (). it fiend the square root of the argument and returns it. So, if a cell contains the formula =SQRT(144). It will display the value 12, whereas the formula bar will display the formula i.e. =SQRT( 144). Instead of the number 144, we could also have given a cell address. Some functions do not require any arguments. An example of this type is the =NOW() function, which returns the system date and time.
ENTERING A FUNCTION
Now that we know what a function is made up of, we can go on to examine how functions can be entered. There are two ways of entering a function. They are as follows :
Typing the function manually.
Pasting the function using the FUNCTION WIZARD
TYPING A FUNCTION
Entering a function is on different from entering any other type of data. First, we select the which should contain the function. After that, we type the “=” sign. Then we type the function and the arguments.
A function can be as simple as the one below :
=SUM (A1:A10)
We can also have complex functions like the one mentioned below :
=IF (SUM(A1:A10)-SUM(B1:B0), “EQUAL”, “NOT EQUAL”)
FUNCTION WIZARD
Another way we can enter functions into our Worksheet is using the function wizard. Earlier we had seen how we could paste Range Names to quickly create powerful formulae. Letting Excel help us enter functions be pasting them ensures that they are spell correctly and reminds us of arguments and order in which the arguments are expected.
Typing a function and pasting it both begin in the same way : we first select the cell where we want the function to calculate a result. To paste a function, we choose the Function….com made from the Insert Menu or by clicking on the Function Wizard Tools on the Standard Toolbar. This displayed in the Function Wizard Category Box. We can scroll through the Function Name box, which contains the list of functions in the selected category, select the function we want.
To enter the function, click Next or press Enter. Excel! Enters in the formula bar an equal sign, the function name, and a set of parentheses. Excel then moves to the Second Function Wizard dialog box. The second Function Wizard dialog box contains one edit box for each argument of the function you selected. A insertion point appears in the edit box of the argument you are entering.
The current value of the function appears in the upper right corner of the dialog box. When you click Finish or average sales in each month.
Lets use some of these functions mentioned earlier in our sample Worksheet, to calculate the Maximum, Minimum and average sales in each month.
Type in labels maximum, Minimum and average in the cells A11 to A13. To calculate the maximum sales for Jan, select the cell B11 and click in the function wizard tool. Select the function called MAX and click on the button called next. In the second step of the function wizard we have to specify the value from which the maximum value has to be extracted In our example since the cells with the sales for month of Jan are in a range, we can specify the range address. The range address can either be typed in or selected by using the click and drag technique.
Once the range has be entered, just click of the Finish button. The function is pasted in a active selected cell. In a same way calculate the Minimum and Average sales for Jan using MIN () and AVERAGE () functions. The arguments will be the same range address.
THE IF () FUNCTION
Finally, we will discuss an important Logical function. Logic mainly deals with conditions. Conditions can either evaluate to true or false. On the evaluation of conditions we do certain tasks, We can do things conditionally by using logic in Excel, too The Excel function which allows us to use logic is IF (). Its syntax is shown below:
IF (logical_test, value_if_true, value if false).
Let us see an example of the function.
=IF(SUM(A1:A10)=SUM(B1:B10),’’
The logical_test argument or the condition in this formula is SUM (A1:A10)=SUM(B1:B10). If it evaluates to True, the value_if_true, the label EQUAL is returned. On the other hand, if the logical_test evaluates to False, the value_if_fales the label NOT EQUAL is returned. In formulae, a label has to be enclosed in a pair of quotes.
In our sample work sheets, lets use the IF () function to calculate the commission earned. Based on the Quarterly sales of each product. If the quarterly sales>1500 the commission will be 10% of the quarterly sales, otherwise, commission will be zero. Select the cell F5 where the function has to appear and selected the function IF () using the function wizard. In the box for Logical_Test type in the condition as E5>1500 (the cell E5 contains the figure for the quarterly sales for the first product). In the value_if_true box the formula will be given as E5*10% and in the value_if_fales box the value will be 0. In the cell F5 the function will evaluates as follows:
=IF (E5>1500, 10%*E5, 0)
It should be understood that we can have nested ifs (one IF within another). It is also possible to have functions as the value_if_true and value_if_fales. This can be used to conditionally assign values to cells. But to selected a function within another function we have to use the Function Wizard button, next to the appropriate argument box from within the Function wizard Dialog Box.
If in the above example the commission was dependent in the following two conditions- Qtr Sales>1800: commission =20% of Qtr Sales Qtr Sales>1300: commission= 10% of Qtr Sales
When we select the IF () function wizard box, the value_if_false box will contain a nested IF() function which will be given using the function wizard button next to this argument box.
In the cell for commission the formula evaluates as follows:
=!F (E5>1800,20%E5,IF(E5>1300,10%
AUTO FORMAT AND OUTLINING
AUTOFORMAT
As against manual formatting, Excel provides us with an automatic formatting feature celled Auto Formatting. It presents samples from which we can pick the appearance we desire for our spreadsheet. Excel’s Auto Format feature uses predefined combinations of number formats, fonts, cell alignments, borders, patterns, shading, column widths, row height and the like. These are called Table Formats. On choosing a Table Format, Excel applied the format to the whole Worksheet or to the like. These are called Table Format. On choosing a Table Format, Excel applied the format to the whole Worksheet or to the selected parts. AutoFormat is smart enough to locate and highlight subtotal and totals for us. We can use these formats as given or overrule some of their characteristics manually, because automatic formatting is sometimes difficult to undo, it is a very good idea to save Worksheet before experimenting with the automatic formats.
APPLYING FORMATS
To use AUTO FORMAT these steps :
- Enter the data in your Worksheet. Select the range to be formatted and then issue the AutoFormat
- The dialog box that is displayed shows 16 different predefined formats. The Option command in the
to our liking, (if you click an option to
accordingly.)
- Select a format from the Table Format list box (a sample output is displayed in the Sample box) and click on OK. The selected format will be applied to your Worksheet.
If we execute the Auto Format command without selecting the range to be formatted, Excel will look for what it thinks is a logical range of cells to format. It will highlight the proposed range for us to confirm. If we want to format a different range, then we cancel Auto Format, select the desired range and then try again.
Different formats are appropriate for different kinds of Worksheets. For example, the Classic formats are excellent for simple columns of numbers with totals and subtotals (Which receive special formatting). The financial formats highlight the correct information in the appropriate. The List-type formats use colours and shading to make long lists of number more readable. The 3D format creates a 3-dimensional effect. If the contents of a Worksheet appear in different levels of importance (i.e. Outline). Auto Format can different levels differently or otherwise distinguish between them.
OUTLINING
The outlining feature provided by Excel is an unique and extremely powerful one. It allows us to have multiple levels of information in a Worksheet. Using outlines, we can together related which are to be subtotalled, have totals etc. Making an outline gives us the advantage of normally seeing the overall picture while allowing us to see details only when necessary. In other words, whenever we do not need to see the details, we can hide them. Excel allows us to outline upto eight levels. Outlining does not in any way affect the formulae in the Worksheet, So, if at any time we feel that the outline is not good enough in either appearance or effect, we can just remove it and have a normal Worksheet.
Outline can be created automatically as well as manually. The outline command allows us to create an outline automatically. The effect of this command can be observed in the Worksheet shown below. Though at first sight the symbols seem intimidating, there is nothing very awesome involved. We will use this ample Worksheet to understand the concept too. To understand outlining, we will first have to know some important terms.
LEVEL
The word level refers to the hierarchy or the data in our spreadsheet. We can have up to eight outline levels in each row or column of a spreadsheet. There are two types of levels : Summary (highest-1) and detail (lowest-8) Summary levels are comprised of detail rows or columns or of lower summary levels. Which are known as Subordinate summary levels.
SUMMARY
A summary level of information summarizes data (e.g. a sub-total) it is usually most convenient to have summarizes below a set of details in the case of rows and on the right of the details in the case of columns. A level is designated as a summary level by Excel if the row or columns a total, or average, or any other summary calculations based on data in derail rows or columns.
DETAIL
Detail in the lowest point on the outlining hierarchy. Detail levels are typically rows or columns of
data.
COLLAPSE
When we collapse a level, we temporarily hide detail or subordinate summary level. The collapsed rows or columns are not deleted from our spreadsheet, but hidden from view.
Rows or columns that contain summary or subordinate summary data, display the collapse symbol (- ), indicating that the detail levels can be hidden. If a row or column shows the expand symbol ( + ), it should be understood that there are hidden detail or subordinate summary levels. We can also tell if an outline is collapsed by looking at the row and column border, if the row numbers or column letters and not contiguous, it means that data is collapsed.
EXPAND
When we expand a level, we display formerly hidden detail levels or subordinate summary levels. Rows or columns that are summary levels display the expand symbol (+), indicating the HIDDEN detail or subordinate summary levels.
CREATING AN OUTLINE AUTOMATICALLY
To create an outline automatically, from the Data Menu, Choose Group and Outline Command, and then choose Auto Outline. If we do not select a range, the entire Worksheet gets outlined. So, if we want only a portion of the Worksheet to be outlined, we should mark that portion as a range. For the outline to get creates correct, the formulae should be consistent in direction. Normally the summary formula is in the row below the rows of detail or in the left of the detail columns. If the position of the summary formula is to be changed, then the Setting option , from the Group and Outline command, under the Data Menu, will have to be selected. The outline dialog box shown below gets displayed. The option and the command buttons available are discussed below.
Automatic styles
Applies built in cell styles for the summary rows and commands of outline. Styles are applied rows and columns.
Summary Rows below Detail
Specifies the location of summary rows in the outline.
Summary Columns to Right of Detail
Specifies the location of summary columns in the outline.
Create
Automatically assigns outline levels based on the formulae in your Worksheet If the Automatic Styles check box is selected, Microsoft Excel applies the built-in cell styles as well.
Apply styles
Applies row and column level styles to the selected outline, or the part of an outline, that does currently have them.
VIEWING THE OUTLINE
Once the Worksheet has been outlined, we will see outlining symbols is along the edges of the Worksheet. If we click on the level marker 1 (of either the row or the column), the highest level i.e. the most summarized version will get displayed. If we click on the last level marker, the Worksheet i.e. including all details, will get displayed clicking on any intermediate level of detail onwards to the highest level of summary. When we have the appropriate levels displayed, we can collapse certain level further and Expand creation levels to get the exact desired picture of the Worksheet.
MANUAL OUTLINING
While automatic outlines are much easier to create, we can make use of the Group and Ungroup commands to create an outline manually is to select the details rows or columns and to Group them. After having Grouped all the details we have to Collapse them so that so that the subordinate summary levels of the same level appear together. Then we can select all these levels and Group them and so on till the entire outline is ready.
Note once items have been collapsed, all formatting, cutting, pasting and similar activities hidden details.
REMOVING AN OUTLINE
It is easy to remove an outline from a Worksheet. From the Data menu, choose Group and Outline and then Clear Outline. You remove the column or row levels (or both) from an outline by ungrouping all of the outline’s levels to the highest level.
SUPPRESSING THE OUTLINE DISPLAY
When you outline a Worksheet, Excel displays some additional symbols above and to the left of the row and column headings. These symbols indicate the structure of your outline, and you can use them to change the level of detail the Excel displays. However, these symbols do take up screen space. If you want to suppress them, choose the Option command from
the Tools Menu, click the View Tab and deselect the Outline Symbols option. To redisplay the outline symbols, reselect the Outline Symbols Option from the option dialog box.
No comments:
Write comments