17- Working with basic
functions
171- What are functions?
The first step in inserting a function is
to choose a cell to contain the function result. Once you select a cell,
display the Insert Function dialogue box by clicking the fx button next to the
formula bar. You can also choose the Insert Function button from the Formulas
Ribbon.
As
discussed in lesson 1.2, the Insert Function dialogue box lets you select
functions by category via a drop down list. The Help on This Function link is
also available if you need clarification on the use of a selected function.
Once
you choose a function you can click OK to move to the next step: the Function
Arguments box.
For this example the PMT function has been
selected from the financial category. The PMT function will calculate the
payment amount for a loan based on the arguments you provide.
In the upper part of the box, you will see
a series of text fields, one for each possible argument to the function. If you
click on an argument field, a brief description of the argument will appear at
the bottom of the dialogue box. In the previous image, you can see the
description for the Rate argument.
You can also see argument names to the
left of the argument fields. The ones in bold type (Rate, Nper, and Pv) are
required arguments. As for the arguments without bold type names, Excel 2007
will enter a default value when required if the argument fields are left empty.
You can enter raw data directly onto the
argument fields, or if the data is stored in your worksheet, you can type the
appropriate cell references. You can also click on an argument field and then
click on the cell that contains the data to enter it without typing. You can
navigate between fields in the dialogue box by pressing the Tab key.
Here you can see the completed PMT
function arguments. Cell references for the cells that contain the data can be
seen in the argument fields. The raw data itself can be seen just to the right
of the argument fields. The Rate field contains C6/12 because the payments are
to be made monthly, requiring us to divide the yearly rate of 6% (in C6) by 12
for a monthly rate. The monthly payment of $430.33 will be visible in the cell
that was selected to contain the formula.
This process lets us insert functions into
a worksheet without having to manually type them. The resulting function is
exactly the same as if we typed =PMT (C6/12, B7, D16, 0) directly into the
formula bar. By using the Insert Function feature, you do not have to worry
about parentheses and commas. You can also clearly see the argument
descriptions and how many arguments are required.
172- Autosum
SUM
is a common Excel function used to find the total of a range of cells. Excel
has a shortcut button, called AutoSum that can insert the formula for you.
1)
Click a cell next to the column or
row of numbers you want to sum.
2)
Click the Home tab and click the AutoSum button in the Editing group.
The
SUM function appears in the cell and a moving dotted line appears around the
cell range that Excel thinks you want to sum. If the range is not correct,
click and drag to select the correct range.
Click
the AutoSum button list arrow to choose from other common functions,
such as Average.
3)
Press the <Enter> key to confirm the action.
The
cell range is totaled in the cell. If you change a value in the summed range,
the formula will automatically update to show the new sum.
173- Minimum
Returns the smallest number in a set of values.
Syntax
MIN(number1,number2,...)
Number1, number2, ... are 1 to 255 numbers for which you want
to find the minimum value.
Remarks:
·
Arguments can either be numbers or names, arrays, or references that
contain numbers.
·
Logical values and text representations of numbers that you type
directly into the list of arguments are counted.
·
If an argument is an array or reference, only numbers in that array or
reference are used. Empty cells, logical values, or text in the array or
reference are ignored.
·
If the arguments contain no numbers, MIN returns 0.
·
Arguments that are error values or text that cannot be translated into
numbers cause errors.
·
If you want to include logical values and text representations of
numbers in a reference as part of the calculation, use the MINA function.
Example:
The example may be easier to understand if you copy it
to a blank worksheet.
1)
Create a blank workbook or worksheet.
2)
Select the example in the Help topic.
Do not select the row or column headers.
Selecting an example from Help
3)
Press CTRL+C.
4)
In the worksheet, select cell A1, and press CTRL+V.
5)
To switch between viewing the results and viewing the
formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
174- Trim
Place your cursor in the empty column next to the
Month on the first row of
Where we want to adjust the data. Click on the Text
Function and from the fly‐out
Choose Trim. This will trim away all the unwanted
spaces. When you click on Trim
The following box will open up. Now click on the wrong
cell with the wrong text,
Excel 2007 will automatically enter the function in
the cell ref.
From this box we can read the Text says Removes all
spaces from a text string
Except for single spaces between words. This is the
function that we want
Press OK. Your formula will then be inserted into the
first cell. We could do this for
Each of our rows but let’s say you had a spreadsheet
with 50 rows, it would take
Some time. So we are going to copy the function.
Click on the cell where the formula is and drag down
on the small box. Then let go,
Excel 2007 will have entered the data. So now
everything is fine except the data is
In the wrong column, select the right column right
click on your mouse and choose
Copy, place your cursor where you want the new data to
go and click on paste
Special. A box will open up, we want to choose the
Values radio as that is what we
Want, to add the values of the function.
Now all that remains is to select the column which we
do not want, right click on our mouse and choose delete. We now have a perfect
table with the column and data aligned as we want it to be.
There are many more text functions, to many to go into
all of them in this lesson,
So experiment with them. Remember the function box
will give you the
Information so you can see if that is the right
function you want to use. Or, If you
Hover over the function you will usually see a screen
tip, if you have customized
Excel 2007 to show them, telling you want the function
does. Let’s do another text
Function now, using the same table.
175- Left
A box opens up for us to enter our data of what string
we want to use to shorten
The letters for. Choose A3 in the text box. Choose 2
in the number box. Click OK.
Here
is the completed table.
Back To Top Back To Excel Index Page (Open Exercise file)
|
||||||||||||||||||||||
