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 :
|
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 ()
Returns the highest value from the ones passed as arguments.
=MIN ()
Returns the lowest Value from the ones passed as arguments.
=COUNT () Returns the Number of not null entries passed as arguments.
=NOW ()
Return the current data and time.
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),’’
EQUAL”,’’NOT EQUAL”)
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%
E5,0))
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
command from the Format Menu.
- The dialog box that is displayed shows 16 different predefined formats. The Option command in the
dialog box expands to display the Formats To Apply section which allows us to tailor auto formatting
to our liking, (if you click an option to
deselect a format type, (the Sample display adjust
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.
HOW EXCEL MAKES AUTO FORMATTING DECISIONS
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.
