Examples
Text Functions
FIND
LEFT, RIGHT, MID
CONCATENATE
REPLACE
TRIM
LEN
UPPER, LOWER
DOLLAR
PROPER
REPT
SUBSTITUTE
BAHTTEXT
VALUE
TEXT
Enter the following data in an Excel spreadsheet:
A1: 7678.868
A2: 123.65
A3: 30/11/2008
Place the cursor on B1. On the Formulas tab, click
the Text drop down and select “TEXT”.
When the cursor is on the Value field, select cell A1
on the worksheet.
Place the cursor on the Format field, then enter :
“$#,##0.00”
Click OK
B1 has now changed to: $7,678.87
TEXT (cont.)
Place your cursor on C1. On the Formulas tab,
click the Text drop down and select “TEXT”.
When the cursor is on the Value field, select cell
A1 on the worksheet.
Place the cursor on the Format field, then
enter : “0”
Click OK
C1 has now changed to: 7,679
Click B2. On the Formulas tab, click the Text
drop down and select “TEXT”.
TEXT (cont.)
When the cursor is on the Value field, select cell A2 on
the worksheet.
Place the cursor on the Format field, then enter : “0.0”
Click OK
B2 has now changed to: 123.7
Place cursor on C2. On the Formulas tab, click the
Text drop down and select “TEXT”.
When the cursor is on the Value field, select cell A2 on
the worksheet.
Place the cursor on the Format field, then enter :
“0.00”
Click OK
C2 has now changed to: 123.65
FIND
Enter the following sentence in A1:
“King Henry ruled with an iron fist.”
Click B1.
On the formulas tab, click the “Text” dropdown and
select “FIND”
On the find_text field enter “Henry”
On the within_text field enter A1.
On the start_num field enter 1.
Click OK.
B1 becomes 6. This is because “Henry” starts on character
6.
If you enter 7 or a bigger number in the start_num field,
you get an error message on B1.
FIND (cont.)
Place the cursor on C1. Select the FIN function
from the list of Text Functions.
Enter “e” on the find_text field.
Select A1 on the within_text field.
Select any number between 1 and 7 on the
start_num field.
Click OK.
C1 becomes 7.
Now, change start_num to 8 and click OK.
C1 now becomes 15.
LEFT
Enter the following data into cell A1: 687 miles
Click on cell B1 in the spreadsheet
Click on the Formulas tab of the ribbon menu.
Choose Text from the ribbon to open the function
drop down list and click on LEFT in the list to bring up
the function's dialog box.
In the Text field, select A1 on the worksheet.
In the Num_chars field, enter 3 – you want to keep
the three leftmost characters of data.
Click OK.
The number 687 should appear in cell B1.
When you click on cell B1 the complete function =
LEFT (A1,3 ) appears in the formula bar above the
worksheet.
RIGHT
On the same spreadsheet,
Click on cell C1 in the spreadsheet
Click on the Formulas tab of the ribbon menu.
Choose Text from the ribbon to open the function
drop down list and click on RIGHT in the list to bring
up the function's dialog box.
In the Text field, select A1 on the worksheet.
In the Num_chars field, enter 5 – you want to keep
the five rightmost characters of data.
Click OK.
The word “miles” should appear in cell C1.
When you click on cell C1 the complete function =
RIGHT (A1,5 ) appears in the formula bar above the
worksheet.
MID
Enter the following data into cell A3: #687 miles
Click on cell D3 in the spreadsheet.
Click on the Formulas tab of the ribbon menu.
Choose Text from the ribbon to open the function drop
down list, and select MID.
In Text field, select cell A3
In the Start_num field, enter 2 – this shows that you don't
want to keep the first character on the left.
On the Num_chars field, enter 3 since you only want to
keep the next three characters of data from Start_num
Click OK.
The number 687 should appear in cell D3.
When you click on cell D3 the complete function = MID
( A3,2,3 ) appears in the formula bar above the worksheet.
CONCATENATE
Enter the following data into specified cells: A1 - 4, B1 – hours
Click on cell A3- the location where the results will be displayed.
Click on the Formulas tab. Choose Text Functions from the
ribbon to open the drop down list.
Click on CONCATENATE in the list to bring up the function's
dialog box.
On line Text 1 of the dialog box, type in the words "We will arrive
in ". Take note of the space after in.
On line Text 2 of the dialog box, click on cell A1.
On line Text 3 of the dialog box, press the space bar to add a
blank space.
On line Text 4 of the dialog box, click on cell B1.
Click OK.
The concatenated sentence "We will arrive in 4 hours" should
appear in cell A3.
The complete function = CONCATENATE ("We will arrive in ",
A1 ," ", B1) appears in the formula bar above the worksheet
when you click A3
REPLACE
Enter the following data into cell A1: ^& # 24,398.
Click on cell B1 in the spreadsheet
Click on the Formulas tab of the ribbon menu.
Choose Text from the ribbon to open the function
drop down list and select REPLACE.
On the Old_text field, select A1 on the worksheet
On the Start_num field, enter 1 indicate we want to
start our replacement from the first character on the
left.
On the Num_chars field, enter the number 4 on this
line to replace the first four characters.
REPLACE (cont.)
On the New_text field, type a dollar sign ( $ ) so
that we replace the four characters with the
dollar sign.
Click OK.
The amount $ 24,398 should appear in cell B1.
When you click on cell `B1 the complete
function = REPLACE ( A1 , 1 , 4 , “$" )
appears in the formula bar above the
worksheet.
TRIM
Enter the following text into cell A1: Today's oil
price is : (be sure to include extra spaces between the
words)
Click on cell B1 in the spreadsheet
Click on the Formulas tab of the ribbon menu.
Choose Text from the ribbon to open the function drop
down list and select TRIM.
In the Text field, select A1.
Click OK.
The line of text Today's oil price is: should appear in cell
B1 but without the extra spaces between the words.
When you click on cell B1 the complete function = TRIM
( A1 ) appears in the formula bar above the worksheet.
LEN
Enter the following text on A1: “Life is too bad to
drink bad wine.”
Place the cursor in B1.
Click on the Formulas tab of the ribbon menu.
Choose Text from the ribbon to open the function
drop down list and select LEN.
On the Text field, select cell A1 from the worksheet.
Click OK.
B1 becomes 34 – the number of characters on the
sentence including the full stop.
When you click on cell B1 the complete function =
LEN( A1 ) appears in the formula bar above the
worksheet.
UPPER & LOWER
Enter the following in A1 and A2 respectively:
A1: this will be changed to capital letters
A2: THIS WILL BECOME SMALL LETTERS
Place the cursor on B1 and click Text on the formulas
tab and select UPPER. On the Text field, select A1.
Click OK and B1 becomes THIS WILL BE CHANGED TO
CAPITAL LETTERS.
Place the cursor on B2 and click Text on the formulas
tab and select LOWER. On the Text field, select A2.
Click Ok and B2 becomes: this will become small letters.
DOLLAR
Enter the following to A1: 25
Place the cursor on B1.
Open the Text drop down on the formulas tab
and select DOLLAR.
On the Number field, select A1.
On the Decimals field, select 2.
Click OK.
B1 becomes R 25.00 (If the country setting is
South Africa)
When you click B1, the function =DOLLAR(A1,2)
appears on the formulas bar of the worksheet.
PROPER
Enter the following statement on A1: king
henry died a miserable death called measles
Place the cursor on B1.
Click Text on the formulas tab and select
PROPER from the drop down list of functions.
On the Text field, select A1.
Click OK.
B1 becomes: King Henry Died A Miserable
Death Called Measles
REPT
Enter the following statement on A1: “This will
be repeated several times. ”
Place the cursor on B1.
Click Text on the Formulas tab and select REPT.
On the Text field, select A1.
On the Number_times field, type 3 – the number
of times the text is to be repeated.
Click OK.
B1 now becomes: This will be repeated several
times. This will be repeated several times. This
will be repeated several times.
SUBSTITUTE
Enter the following in A1: “The price for the whole
project is R2 billion.”
Place the cursor on B1.
Click Text on the Formulas tab and select SUBSTITUTE.
On the Text field, select A1.
On the Old_text field, type “price”, the text to be
substituted.
Note that SUBSTITUTE is case sensitive.
On the New_text field, type “cost”.
On the Instance_num field, type 1 to substitute the first
occurrence of price.
Click OK.
B1 becomes: The cost for the whole project is R2 billion.
BAHTTEXT
Enter the following on A1 and A2 respectively:
1 and 100
Place the cursor on B1.
Click Text on the Formulas tab and select
BAHTTEXT.
On the Number field, select A1. Copy the
contents of this cell to B2.
Click OK.
A1 and A2 become หหหหหหหหหหหห and หหหหห
หหหหหหหหหหห respectively.
VALUE
Enter the following in A1 and A2 respectively: 10
and ten
Place the cursor on B1. Click Text on the Formulas
tab and select VALUE.
On the Text field, select A1.
Click OK.
B1 becomes 10.
Copy the contents of B1 to B2.
B2 becomes an invalid value. This is because the
VALUE function only converts numeric text to a
number.
LOGICAL FUNCTIONS
Comparison Operators
TRUE
FALSE
AND
OR
IF
NOT
Comparison Operators
Equals ( = )
Less than ( < )
Less than or equal to ( < = )
Greater than ( > )
Greater than or equal to ( > = )
Not equal to ( < > )
Comparison Operators
(cont.)
The logic test can be a comparison between
two cell references such as:
A3 > B3
A1 = G3
Or the logic test can be a comparison
between a cell reference and a fixed amount
such as:
C4 < = 100
B7 <> 0
AND
Enter the following data into cells A1 to A3: 78 ,
110 , 45.
Click on cell B1
Click on the Formulas tab. Choose Logical Functions
from the ribbon to open the function drop down list.
Click on AND in the list to bring up that function's
dialog box.
Click on cell A1 in the spreadsheet to enter the first
cell reference in the logical1 box.
Type < 100 after the cell reference in the logical1 box.
Click in the box next to logical2 in the dialog box.
Click on cell A2 in the spreadsheet to enter the second
cell reference.
AND (cont.)
Type < 100 after the cell reference in the logical2 box.
Click in the box next to logical3 in the dialog box.
Click on cell A3 in the spreadsheet to enter the third
cell reference.
Type < 100 after the cell reference in the logical3 box.
Click OK.
The value FALSE should appear in cell B1 because the
data in cell A2 is greater than 100.
The complete function = AND ( A1 < 100 , A2 < 100
, A3 < 100) appears in the formula bar above the
worksheet when you click on cell E1.
To return a value of TRUE in cell B1, type a number in
cell A2 that is less than 100.
OR
In the same spreadsheet used for AND.
Click on cell C1 - the location where the results will be
displayed.
Click on the Formulas tab. Choose Logical Functions
from the ribbon to open the function drop down list.
Click on OR in the list to bring up that function's
dialog box.
Click on cell A1 in the spreadsheet to enter the first
cell reference in the logical1 box.
Type > 100 after the cell reference in the logical1 box.
Click in the box next to logical2 in the dialog box.
Click on cell A2 in the spreadsheet to enter the
second cell reference.
OR (cont.)
Type > 100 after the cell reference in the logical2 box.
Click in the box next to logical3 in the dialog box.
Click on cell À3 in the spreadsheet to enter the third
cell reference.
Type > 100 after the cell reference in the logical3 box
Click OK.
The value TRUE should appear in cell C1 because the
data in cell A2 is greater than 100.
The complete function = OR ( A1 > 100 , A2 > 100 ,
A3 > 100) appears in the formula bar above the
worksheet when you click on cell C1.
To return a value of FALSE in cell C1, type a number in
cell 2 that is less than 100.
IF
Enter 51 into cell A1.
Click on cell B1.
Click on the Formulas tab. Choose Logical Functions
from the ribbon to open the drop down list.
Click on IF in the list to bring up the function's dialog
box.
On the Logical_test line in the dialog box, click on cell
A1. After this type the greater than symbol ( > ) and
then the number 50.
On the Value_if_true line of the dialog box, type “Pass”.
On the Value_if_false line of the dialog box, type “Fail”.
Click OK.
IF (cont.)
The value “Pass” should appear in cell B1, since
the value in A1 is greater than 50.
To change the result in cell B1, change the
number in cell A1 to 49 and press the Enter
key.
The value “Fail” should now be present in cell
E1 since the value in A1 is now less than 50.
If you click on cell B1, the complete function =
IF ( A1 > 50, “Pass” , “Fail” ) appears in the
formula bar above the worksheet.
NOT
Enter the following in A1 and A2: True and
False
Click B1 and click Logical Functions from the
Formulas tab and select NOT.
On the Logical field, select A1.
Click OK.
B1 now becomes False, because True is
negated.
Copy B1 to B2.
B2 becomes True because False is negated.
Statistical Functions
AVERAGE
AVERAGE IF
LARGE
SMALL
MIN
MAX
MODE
MEDIAN
RANK
AVERAGE
Enter the following data into cells A1 to A6:
11,12,13,14,15,16.
Click on cell A7.
Click on the Formulas tab. Choose More Functions >
Statistical from the ribbon to open the function drop
down list.
Click on AVERAGE in the list to bring up
the function's dialog box.
Drag select cells A1 to A6 in the spreadsheet to enter
the range into the dialog box.
Click OK.
The answer 13.5 should appear in cell A7.
The complete function = AVERAGE (A1 : A6) appears
in the formula bar above the worksheet.
AVERAGEIF
Enter the following data into cells A1 to A6:
114,165,178,143,130,165.
Enter the following data into cells B1 to B6: 10, 20,
30, 10, 20, 30.
Click on cell B7.
Click on the Formulas tab. Choose More
Functions > Statistical from the ribbon to open
the function drop down list.
Click on AVERAGEIF in the list to bring up the
function's dialog box.
In the dialog box, click on the Range line.
Drag select cells A1 to A6 on the spreadsheet.
On the Criteria line in the dialog box, type "165".
AVERAGEIF (cont.)
Click on the Average_range line.
Drag select cells B1 to B6 on the spreadsheet.
Click OK.
The answer 25 should appear in cell B7. Since
the criteria of equaling 165 is met by only two
cells - A2 and A6, only their corresponding cells
- B2 and B6 are averaged. The average of 20
and 30 is 25.
LARGE
Enter the following data into cells A1 to A6:
123,135,147,130,98,187.
Click on cell B1.
Click on the Formulas tab. Choose More
Functions > Statistical from the ribbon to open
the function drop down list.
Click on LARGE in the list to bring up the
function's dialog box.
Click on the Array line in the dialog box.
Drag select cells A1 to A6 in the spreadsheet to
enter the range into the dialog box.
LARGE (cont.)
Click on the K line in the dialog box.
Type a 3 ( three ) on this line to find the third
largest value in the range selected.
Click OK.
The number 135 should appear in cell B1 since
it is the third smallest number ( the numbers
187 and 147 in the list are both larger).
The complete function = LARGE ( A1 : A6 , 3 )
appears in the formula bar above the worksheet
when you click on cell B1.
SMALL
Enter the following data into cells A1 to A6:
123,135,147,130,98,187.
Click on cell B1
Click on the Formulas tab. Choose More
Functions > Statistical from the ribbon to open
the function drop down list.
Click on SMALL in the list to bring up the
function's dialog box.
Click on the Array line in the dialog box.
Drag select cells A1 to A6 in the spreadsheet to
enter the range into the dialog box.
SMALL (cont.)
Click on the K line in the dialog box.
Type a 3 ( three ) on this line to find the third
smallest value in the range selected.
Click OK.
The number 130 should appear in cell B1 since
it is the third smallest number ( the numbers
123 and 98 in the list are both smaller ).
The complete function = SMALL ( A1 : A6 ,
3 ) appears in the formula bar above the
worksheet when you click on cell B1.
MIN
Enter the following data into cells A1 to A6:
114,165,178,143,130,167.
Click on cell A7.
Click on the Formulas tab. Choose More
Functions > Statistical from the ribbon to
open the function drop down list.
Click on MIN in the list to bring up the function's
dialog box.
Select cells A1 to A6 in the Number field
Click OK.
The answer 114 appears in cell A7.
The complete function =MIN(A1:A6) appears in
the formula bar.
MAX
On the same spreadsheet as the MIN function.
Click on cell B7.
Click on the Formulas tab. Choose More
Functions > Statistical from the ribbon to open
the function drop down list and select MAX to bring
up the function's dialog box.
Drag select cells A1 to A6 on the spreadsheet to
enter the range into the dialog box.
Click OK.
The answer 178 should appear in cell B7 as it is
the largest number in the selected range.
The complete function = MAX (A1 : A6) appears
in the formula bar above the worksheet.
MODE
Enter the following data into cells D1 to D6:
98,135,147,135,98,135.
Click on cell E1.
Click on the Formulas tab. Choose More Functions >
Statistical from the ribbon to open the function drop down
list and select MODE to bring up the function's dialog box.
Drag select cells D1 to D6 in the spreadsheet to enter the
range into the dialog box.
Click OK.
The answer 135 should appear in cell E1 since this number
appears the most (three times) in the list of data.
The complete function = MODE (D1 : D6) appears in the
formula bar above the worksheet when you click on cell E1.
MEDIAN
Enter the following data into cells D1 to D5: 4,12,49,24,65.
Click on cell E1.
Click on the Formulas tab. Choose More Functions >
Statistical from the ribbon to open the function drop down
list and select MEDIAN to bring up the function's dialog box.
Drag select cells D1 to D5 in the spreadsheet to enter the
range into the dialog box.
Click OK.
The answer 24 should appear in cell E1 since there are two
numbers larger (49 and 65) and two numbers smaller (4 and
12) than it in the list.
The complete function = MEDIAN (D1 : D5) appears in the
formula bar above the worksheet when you click on cell F1.
RANK
Enter the following data into cells D1 to D6:
123,135,147,130,98,187.
Click on cell E1 - the location where the results will be
displayed.
Click on the Formulas tab.
Choose More Functions > Statistical from the ribbon
to open the function drop down list.
Click on RANK in the list to bring up the function's dialog
box.
Click on cell D2 to choose the number to be ranked
(135).
Click on the "Ref" line in the dialog box.
Drag select cells D1 to D6 in the spreadsheet to enter
the range into the dialog box.
RANK (cont.)
Click on the "Order" line in the dialog box.
Type a zero on this line to rank the number in
descending order.
Click OK.
The number 3 should appear in cell E1 since
the number 135 is the third largest number.
The complete function = RANK ( D2 , D1 :
D6 , 0 ) appears in the formula bar above the
worksheet when you click on cell E1.
Information Functions
CELL
TYPE
ISBLANK
ISERROR
ISNUMBER
ISTEXT
ERROR.TYPE
N
NA
CELL
Type a number, such as " 23 ", into cell D1.
Click on cell E1 - the location where the results will be
displayed.
Click on the Formulas tab.
Choose More Functions > Information from the
ribbon to open the function drop down list.
Click on CELL in the list to bring up that function's
dialog box.
Enter the word "type" on the Info_type line of the dialog
box
Click on the Reference line in the dialog box.
Click on cell D1 in the spreadsheet to enter the cell
reference into the dialog box.
Click OK.
CELL (cont.)
The letter " v " should appear in cell E1 to indicate
that the data in the cell is a value.
The complete function = CELL ( " type " , D1 )
appears in the formula bar above the worksheet
when you click on cell E1.
Note:
If cell D1 was empty, the letter " b " would appear in cell
E1 to indicate that the cell was blank.
If cell D1 contained a word of text, the letter " l " would
appear in cell E1 to indicate that the cell contained a label.
If cell D1 contained a date, the letter " v " would appear in
cell E1 to indicate that the cell contained a value - dates
are often considered to be values in Excel.
TYPE
Enter the following data to A1: Smith
Place cursor on B1.
Click on the Formulas tab. Choose More
Functions > Information from the ribbon to open
the function drop down list and select TYPE.
The table below shows the result in B1 if the data
in Formula is added to B1.
Formula
Description
Result
=TYPE(A1)
Checks the type of the value
in the cell reference
2
=TYPE("Mr.
"&A2)
Checks the type of "Mr.
Smith"
2
=TYPE(2+A2)
Checks the type of the
formula, which returns the
error #VALUE!
16
ISBLANK
Type a number or a word of text into cell C1.
Click on cell D1.
Click on the Formulas tab.
Choose More Functions > Information from
the ribbon to open the function drop down list.
Click on ISBLANK in the list to bring up that
function's dialog box.
Click on cell C1 in the spreadsheet to enter the
cell reference into the dialog box.
Click OK.
ISBLANK (cont.)
The value FALSE should appear in cell D1
because cell C1 is not empty.
The complete function = ISBLANK ( C1 )
appears in the formula bar above the worksheet
when you click on cell D1.
To return a value of TRUE in cell D1, delete the
data in cell C1 or change the cell reference in
the function to a cell that is empty.
ISERROR
Enter the following in A1 to A3: 12,3,53
Enter the following in B1 to B3: 25,0,1
Click C1. On the Formula bar, type: =A1/B1. Press
Enter.
Copy the contents of C1 to C2 and C3.
C1=0.48
C2=#DIV/0!
C3= 53
Click D1.
Click on the Formulas tab. Choose More Functions
> Information from the ribbon to open the function
drop down list and select ISERROR.
ISERROR (cont.)
On the Value field, select cell C1.
Copy the contents of D1 to D2 and D3.
D1 becomes FALSE
D2 becomes TRUE
D3 becomes FALSE
This is because only cell D2 is an error value.
The complete function = ISERROR ( C1 )
appears in the formula bar above the
worksheet when you click on cell D1.
ISNUMBER
Type a word, such as " January ", into cell D1.
Click on cell E1 - the location where the results
will be displayed.
Click on the Formulas tab.
Choose More Functions > Information from
the ribbon to open the function drop down list.
Click on ISNUMBER in the list to bring up that
function's dialog box.
Click on cell D1 in the spreadsheet to enter the
cell reference into the dialog box.
ISNUMBER (cont.)
Click OK.
The value FALSE should appear in cell E1
because the data in cell D1 is not a number.
The complete function = ISNUMBER ( D1 )
appears in the formula bar above the worksheet
when you click on cell E1.
To return a value of TRUE in cell E1, type a
number in cell D1 or change the cell reference
in the function to a cell that contains a number.
ISTEXT
Type a word, such as “ Books ”, into cell D1.
Click on cell E1 - the location where the results
will be displayed.
Click on the Formulas tab.
Choose More Functions > Information from
the ribbon to open the function drop down list.
Click on ISTEXT in the list to bring up that
function's dialog box.
Click on cell D1 in the spreadsheet to enter the
cell reference into the dialog box.
ISTEXT (cont.)
Click OK.
The value TRUE should appear in cell E1
because the data in cell D1 is text.
The complete function = ISTEXT ( D1 )
appears in the formula bar above the worksheet
when you click on cell E1.
To return a value of FALSE in cell E1, type a
number in cell D1 or change the cell reference
in the function to a cell that contains a number,
or an empty cell.
ERROR.TYPE
Enter the following to A1 and A2: #NULL and =1/0.
A2 = #DIV/0!
Click B1.
Click on the Formulas tab.
Choose More Functions > Information from the
ribbon to open the function drop down list.
Click on ERROR.TYPE in the list to bring up that
function's dialog box.
On the Error_val field, click A1.
Click OK.
ERROR.TYPE
Copy the contents of B1 to B2.
B1 = 1 and B2 = 2
The complete function = ERROR.TYPE( A1 )
appears in the formula bar above the worksheet
when you click on cell B1, and = ERROR.TYPE(
A2 ) appears in the formula bar above the
worksheet when you click on cell B2.
N
Enter the following values to A1 to A4: 7, Even,
TRUE, 4/17/2008
Click B1.
Click on the Formulas tab.
Choose More Functions > Information from
the ribbon to open the function drop down list.
Click on N in the list to bring up that function's
dialog box.
On the Value field, select cell A1.
Click OK.
N (cont.)
Copy the contents of B1 to B2, B3 and B4.
The table below shows the contents of the cell
(on the formula bar) and the result of the
formula.
Cel
l
Formula
Description
Result
B1
=N(A2)
Because A2 contains a number, it is
returned
7
B2
=N(A3)
Because A3 contains text, 0 is returned 0
B3
=N(A4)
Because A4 is the logical value TRUE, 1
is returned
1
B4
=N(A5)
Because A5 is a date, the serial
number is returned (varies with the
date system used)
B5
=N("7")
Because "7" is text, 0 is returned
0
Financial Functions
PMT
DB
PMT
Enter the following data into cells:
D2 - Rate:
D3 - # of payments:
D4 - Pv:
D5 - Future value:
D6 - Payment:
E2 - 7
E3 - 24
E4 - 0
E5 - $10,000.00
Click on cell E6
Click on the Formulas tab.
PMT (cont.)
Choose Financial functions > PMT from the ribbon
to bring up the function's dialog box.
Click on the Rate line in the dialog box.
Click on cell E2 in the spreadsheet.
After the E2, type a forward slash " / " followed by the
number 12 in the Rate line of the dialog box. This
gives you the interest rate per month.
Click on the Nper line ( # of payments ) in the dialog
box.
Click on cell E3 in the spreadsheet.
Click on the Pv line in the dialog box.
Click on cell E4 in the spreadsheet.
Click on the Fv line in the dialog box.
PMT (cont.)
Type a minus sign ( - ) and then click on cell E5
in the spreadsheet.
Click OK in the dialog box.
The payment amount - $389.39 appears in cell
E6.
When you click on cell E6 the complete function
= PMT ( E2/12 , E3 , E4 , -E5 ) appears in the
formula bar above the worksheet
Lookup Function
TRANSPOSE
TRANSPOSE
Enter the following data into cells:
B1 - cabbages , B2 - carrots, B3 - radishes
Drag select cells D1 to F1.
Click on the Formulas tab.
Choose Lookup & Reference functions >
TRANSPOSE from the ribbon to bring up the
function's dialog box.
Drag select cells B1 to B3 on the spreadsheet.
Press the CTRL , SHIFT , and ENTER keys on the
keyboard at the same time to enter the function
as an array function.
TRANSPOSE (cont.)
The data in cells B1 to B3 should appear in cells
D1, E1, and F1.
When you click on any of the cells D1 to F1 the
complete function
{ = TRANSPOSE ( B1 : B3 )} appears in the
formula bar above the worksheet.
Mathematical Functions
SUM, AUTOSUM
PRODUCT, QUOTIENT
SUMPRODUCT
ABS
ROUND, ROUNDUP, ROUNDDOWN
RAND
TRUNC
MOD
SUMIF
INT, ROMAN
RADIANS
COS, SIN, TAN, ACOS, ASIN, ATAN
SUM
Enter the following data into cells D1 to D6:
114,165,178,143,130,165.
Click on cell D7.
Click on the Formulas tab of the ribbon menu. Choose
Math & Trig from the ribbon to open the function
drop down list. Click on SUM in the list to bring up the
function's dialog box.
In the dialog box, click on the Number1 line.
Drag select cells D1 and D6 in the spreadsheet.
Click OK.
The answer 895 should appear in cell D7.
Click on cell D7 and the complete function = SUM
(D1 : D6) appears in the formula bar above the
worksheet.
AUTOSUM
Enter the following data into cells C1 to C6:
11,12,13,14,15,16
Click cell C7.
Drag select cells C1 to C6 in the spreadsheet.
Click on the AutoSum button on the ribbon of
the Home tab.
Click OK.
The answer 81 should appear in cell C7.
Click on cell C7 and the complete function =
SUM (C1 : C6) appears in the formula bar
above the worksheet.
PRODUCT
Enter the following data in cells indicated: C1: 5
C2: 2
Click on cell D1.
Click on the Formulas tab of the ribbon. Choose Math
& Trig from the ribbon to open the function drop down
list. Click on PRODUCT in the list to bring up the
function's dialog box.
On the Number1 field, click cell C1 on the spreadsheet.
On the Number2 field, click cell C2 on the spreadsheet.
Click OK in the dialog box.
The answer 10 should appear in cell D2.
The complete function =PRODUCT(C1,C2) appears in
the formula bar above the worksheet.
QUOTIENT
Enter the following data in cells indicated: C1: 5
C2: 2
Click on cell D1.
Click on the Formulas tab of the ribbon.
Choose Math & Trig from the ribbon to open the
function drop down list.
Click on QUOTIENT in the list to bring up the
function's dialog box.
In the dialog box, click on the Numerator line.
Click on cell C1 on the spreadsheet.
QUOTIENT (cont.)
In the dialog box, click on the Denominator line.
Click on cell C2 on the spreadsheet.
Click OK in the dialog box.
The answer 2 should appear in cell D2.
The complete function =QUOTIENT(C1,C2) appears
in the formula bar above the worksheet.
Since the QUOTIENT function only returns the
integer portion of the division operation, the
remainder(0.5 or 1/2) is not displayed. If you wish
the remainder to be displayed as part of the answer,
you can easily create your own division formula.
SUMPRODUCT
Enter the following in the spreadsheet:
A1 to A5: 2, 20, 14, 110, 25
B1 to B5: 17, 85, 23, 101, 5.
Colum A contains the available quantities of stock
and column B contains the prices for each product.
With SUMPRODUCT you can calculate total sales with
this formula: =SUMPRODUCT(A1:A5,B1:B5)
If the formula is on C6, then C6 becomes: 13291.
The complete formula on the formula bar when you
click on C6 is: =SUMPRODUCT(A1:A5,B1:B5)
ABS
Enter -4 and 2 in to A1 and A2 respectively.
Click B1.
Open the Math & Trig dropdown list on the
Formulas tab and click ABS.
On the Number field, select the cell A1.
Click OK.
Copy the contents of B1 to B2.
B1 = 4 and B2 = 2
The complete formula on the formula bar when
B1 is selected is : =ABS(A1)
ROUND
Enter the following data into cell D1: 34.567
Click on cell E1 in the spreadsheet - this is
where the function will be located.
Click on the Formulas tab of the ribbon menu.
Choose Math & Trig from the ribbon to open
the function drop down list.
Click on ROUND in the list to bring up the
function's dialog box.
In the dialog box, click on the Number line.
Click on cell D1 in the spreadsheet.
ROUND (cont.)
In the dialog box, click on the Num_digits line.
Type in a 1.
Click OK.
The answer 34.6 should appear in cell E1.
When you click on cell E1 the complete function
= ROUND ( D1 , 1 ) appears in the formula bar
above the worksheet.
To remove all decimal places from a value, set
the num_digits to 0
ROUNDUP
Enter the following data into cell D1: 34.567
Click on cell E1 in the spreadsheet - this is
where the function will be located.
Click on the Formulas tab of the ribbon menu.
Choose Math & Trig from the ribbon to open
the function drop down list.
Click on ROUNDUP in the list to bring up the
function's dialog box.
In the dialog box, click on the Number line.
ROUNDUP (cont.)
Click on cell D1 in the spreadsheet.
In the dialog box, click on the Num_digits line.
Type in a 1 to reduce the number in D1 to 1
decimal place.
Click OK.
The answer 34.6 should appear in cell E1.
When you click on cell E1 the complete function
= ROUNDUP ( D1 , 1 ) appears in the formula
bar above the worksheet.
ROUNDDOWN
Enter the following data into cell D1: 34.567
Click on cell E1 in the spreadsheet.
Click on the Formulas tab of the ribbon menu.
Choose Math & Trig from the ribbon to open
the function drop down list.
Click on ROUNDDOWN in the list to bring up the
function's dialog box.
In the dialog box, click on the Number line.
Click on cell D1 in the spreadsheet.
In the dialog box, click on the Num_digits line.
ROUNDDOWN (cont.)
Type in a 1 to reduce the number in D1 to 1
decimal place.
Click OK.
The answer 34.5 should appear in cell E1.
When you click on cell E1 the complete function
= ROUNDDOWN ( D1 , 1 ) appears in the
formula bar above the worksheet.
RAND
Click on cell E1 in the spreadsheet.
Click on the Formulas tab of the ribbon menu.
Choose Math & Trig from the ribbon to open the
function drop down list.
Click on RAND in the list to bring up the function's dialog
box.
Click OK.
A random number between 0 and 1 should appear in cell
E1.
To generate another random number, press the F9 key
on the keyboard.
When you click on cell E1 the complete function =
RAND( ) appears in the formula bar above the
worksheet.
TRUNC
Enter the following in A1 to A3: 2.2, 2.7 and 12345
Click on B1.
Click on the Formulas tab of the ribbon menu.
Choose Math & Trig from the ribbon to open the
function drop down list.
Click on TRUNC in the list to bring up the
function's dialog box.
On the Number field, select A1.
On the Num_digits field, type 0 – you do not want
the decimal places.
Click OK
TRUNC (cont.)
Copy the contents of B1 to B2.
Both B1 and B2 become 2. No rounding happened.
Click B3 and open the TRUNC function.
On the Number field, select A3 and type -3 on the
Num_digits field.
Click OK
B3 becomes 12000. Interesting!
The complete formula on the formula bar when
you click B3 is: =TRUNC(B1,-3).
Handy when you don't want to show the hundreds, the
tens and units in a report.
MOD
Enter 20 and 6 on A1 and B1 respectively.
Click C1.
Click on the Formulas tab of the ribbon menu.
Choose Math & Trig from the ribbon to open the
function drop down list.
Click on MOD in the list to bring up the function's
dialog box.
Select A1 on the Number and B1 on the Divisor field.
Click OK.
C1 is 2 becomes 2 is the remainder after dividing 20
by 6.
The complete formula on the formula bar if C1 is
clicked is: =MOD(A1,B1)
SUMIF
Enter the following data into cells E1 to E6:
114,165,178,143,130,165.
Enter the following data into cells F1 to F6: 10,
20, 30, 10, 20, 30.
Click on cell F7.
Click on the Formulas tab of the ribbon.
Choose Math & Trig from the ribbon to open
the function drop down list.
Click on SUMIF in the list to bring up the
function's dialog box
SUMIF (cont.)
In the dialog box, click on the Range line.
Drag select cells E1 to E6 on the spreadsheet.
On the Criteria line in the dialog box, type "165".
Click on the SUM Range line.
Drag select cells F1 to F6 on the spreadsheet.
Click OK.
The answer 50 should appear in cell F7. Since the
criteria of equaling 165 is met by only two cells -
E2 and E6, only their corresponding cells - F2 and
F6 are summed. The sum of 20 and 30 is 50
INT
Enter the following data into cell D1: 34.567
Click on cell E1 in the spreadsheet.
Click on the Formulas tab of the ribbon menu.
Choose Math & Trig from the ribbon to open the
function drop down list.
Click on INT in the list to bring up the function's dialog
box.
In the dialog box, click on the Number line.
Click on cell D1 in the spreadsheet to enter that cell
reference into the dialog box.
Click OK.
The answer 34 should appear in cell E1.
When you click on cell E1 the complete function = INT
( D1 ) appears in the formula bar above the worksheet.
ROMAN
Enter the following in A1 and A2: 5 and 23
Click B1.
Click on the Formulas tab of the ribbon.
Choose Math & Trig from the ribbon to open
the function drop down list.
Click on ROMAN in the list to bring up the
function's dialog box.
On the Number field, select A1.
On the form field, type 1.
Click OK..
ROMAN
Copy the contents of B1 to B2.
B1 = V
B2 = XXIII
The complete formula on the formula bar of the
worksheet when B1 is clicked is:
=ROMAN(A1,1)
RADIANS
Enter 30,60, 45 in A1,A2,A3.
Click B1.
Click on the Formulas tab of the ribbon menu.
Choose Math & Trig from the ribbon to open the
function drop down list.
Click on RADIANS in the list to bring up the
function's dialog box.
On the Angle field, select A1.
Click OK.
Copy the contents of B1 to B2 and B3
B1 becomes 0.523599, equivalent of 30 degrees.
SIN
On the same spreadsheet
Click C1.
Click on the Formulas tab of the ribbon menu.
Choose Math & Trig from the ribbon to open the
function drop down list.
Click on SIN function.
On the Number field, click B1.
Click OK.
C1 = 0.5, equivalent of Sine of 30 degrees.
The complete formula on the formula bar of the
worksheet when C1 is clicked is: =SIN(B1)
COS
On the same spreadsheet
Click C2.
Click on the Formulas tab of the ribbon menu.
Choose Math & Trig from the ribbon to open
the function drop down list.
Click on COS function.
On the Number field, click B2.
Click OK.
C2 = 0.5, equivalent of Cosine of 60 degrees.
The complete formula on the formula bar of the
worksheet when C2 is clicked is: =COS(B2)
TAN
On the same spreadsheet
Click C3.
Click on the Formulas tab of the ribbon menu.
Choose Math & Trig from the ribbon to open the
function drop down list.
Click on TAN function.
On the Number field, click B3.
Click OK.
C3 = 1, equivalent of the Tangent of 45 degrees.
The complete formula on the formula bar of the
worksheet when C3 is clicked is: =TAN(B3)
ASIN
On the same spreadsheet
Click D1.
Click on the Formulas tab of the ribbon menu.
Choose Math & Trig from the ribbon to open the
function drop down list.
Click on ASIN to open the function’s dialog box.
On the Number field, select C1.
Click OK.
D1 = 0.523598776, equal to B1 – radian
equivalent of 30 degrees
The complete formula on the formula bar of the
worksheet when D1 is clicked is: =ASIN(D1)
ACOS
On the same spreadsheet
Click D2.
Click on the Formulas tab of the ribbon menu.
Choose Math & Trig from the ribbon to open the
function drop down list.
Click on ACOS to open the function’s dialog box.
On the Number field, select C2.
Click OK.
D2 = 1.047197551 , equal to B2 – radian
equivalent of 60 degrees
The complete formula on the formula bar of the
worksheet when D2 is clicked is: =ACOS(D2)
ATAN
On the same spreadsheet
Click D3.
Click on the Formulas tab of the ribbon menu.
Choose Math & Trig from the ribbon to open
the function drop down list.
Click on ATAN to open the function’s dialog box.
On the Number field, select C3.
Click OK.
D3 = 1.047197551 , equal to B3 – radian
equivalent of 45 degrees
The complete formula on the formula bar of the
worksheet when D3 is clicked is: =ATAN(D3)
Date Functions
TODAY
NOW
NETWORKDAYS
TODAY
Click on cell A1
Click on the Formulas tab.
Choose Date & Time from the ribbon to open
the function drop down list.
Click on TODAY in the list to bring up the
function's dialog box.
Click OK.
The current date should appear in cell A1.
When you click on cell A1 the complete function
=TODAY( ) appears in the formula bar above
the worksheet.
NOW
Click on cell B1.
Click on the Formulas tab.
Choose Date & Time from the ribbon to open the
function drop down list.
Click on NOW in the list to bring up the function's
dialog box.
Click OK.
The current time and date should appear in cell B1.
When you click on cell B1 the complete function:
=NOW() appears in the formula bar above the
worksheet.
NETWORKDAYS
Enter the following data into the appropriate cell:
D1 - Start:
D2 - Finish:
D3 - Holiday 1:
D4 - Holiday 2:
E1 - 7/7/2008
E2 - 11/7/2008
E3 - 9/8/2008
E4 - 10/13/2008
Note: If the dates in cells E1 to E4 do not appear
correct, check to see that these cells are
formatted for dates.
NETWORKDAYS (cont.)
Click on cell F1.
Click on the Formulas tab.
Choose Date and Time functions >
NETWORKDAYS from the ribbon to bring up the
function's dialog box.
Click on the Start_date line in the dialog box.
Click on cell E1 in the spreadsheet.
Click on the End_date line in the dialog box.
Click on cell E2 in the spreadsheet.
Click on the Holidays line in the dialog box.
Drag select cells E3 and E4 in the spreadsheet.
NETWORKDAYS
Click OK in the dialog box.
The number of working days - 88 appears in cell
F1.
There are 90 weekdays between the start and
end dates. From this number the two holidays in
cells E3 and E4 are subtracted to leave 88
working days.
When you click on cell F1 the complete function
= NETWORKDAYS ( E1 , E2 , E3 : E4 )
appears in the formula bar above the
worksheet.
Nested IF Statements
An example of how a nested IF function looks:
=IF((AND(D2>5,C2>10000)),2,1)
=IF((OR(D2>5,C2>10000)),2,1)
=IF((AND(D2>5,C2>10000)),3,
(IF((OR(D2>5,C2>10000)),2,1)))
=IF((B2="East"),4,IF((B2="West"),3,IF((B2
="North"),2,IF((B2="South"),1,""))))
Other Nested Functions
Examples of how other nested functions may
look like:
=SUM(SUM(B1:B3),SUM(C1:C3))
=LEFT(C2,FIND(“”,C2),-1)
PivotTable
Use the data below to create a PivotTable report
and PivotChart
Order # Year
Month
Rep
Category Item
Sales
Custome
r
20040045
2004August
Hickman
Health
Brewer's
Yeast
$800Goods4U
20040044
2004August
Hickman
Feedstuffs
Protein
Mix
$400Winners
20040043
2004August
Velasquez Str Ale
Boneshake
r
$300
Countrywi
de
20040042
2004August
Hickman
Feedstuffs
Protein
Mix
$900Winners
20040041
2004
Septembe
r
Nilsson
Str Lager
Iron
Reserve
$2,384
Extra
Continent
al
20040040
2004July
Hickman
Std Ale
Mary Giant
$3,295
Extra
Continent
al
20040039
2004June
Velasquez Str Ale
Boneshake
r
$400
Countrywi
de
20040038
2004April
Hickman
Health
Brewer's
Yeast
$995Goods4U
20040037
2004March
Stewart
Std Ale
Corn Circle
$2,500
Moose
Pubs
PivotTable (cont.)
Drag the Year field to the page area. Excel adds the
field, displays a drop-down list next to it for selecting
the years, and selects the (All) entry
Drag the Rep field to the row area. Excel adds the
field with a drop-down list button for selecting the
rep name, enters the rep names in the cells (again,
displaying all items), and adds a Grand Total entry
under them
Drag the Category field to the column area. Excel
adds the field with a drop-down list button for
selecting the category, enters the categories in the
cells across the columns, and adds a Grand Total
entry immediately to their right
PivotTable (cont.)
Drag the Sales field to the data area. Excel
snaps the data into place and displays a Sum of
Sales button at the intersection of the rows and
columns. Now you can see which rep has sold
how much of each category of product
To see the reps’ results for a specific year (as
shown below) instead of for all years, choose
the year from the Year drop-down list
Changing a PivotTable
Drag the Item field to the column area. Excel breaks
down each category by its components.
Drag the Category field off the PivotTable area to
remove it. (Either drop the field in limbo anywhere
outside the PivotTable or drop it back in the
PivotTable Field List window.)
The PivotTable then shows how much of each item each
rep sold in the specified year, which shows very clearly
which rep is selling most of which item.
Drag the Customer field to the column area to
produce a PivotTable showing which rep sold how
much of which item to which customer. Drag the
Rep field off the PivotTable to display a breakdown
of which items each customer purchased.
PivotChart
Click on the data that is to be presented on the
PivotChart
Click on the Insert tab of the ribbon menu.
Click on the PivotTable Function and select
PivotChart on the dropdown menu to open the
PivotChart dialog box.
Select the range of cells for which to create the
PivotChart on the Table/Range field or select the
connection if the data is from another source.
Choose whether to display the PivotChart on a
new worksheet or the location if on an existing
one, then click OK.
PivotChart (cont.)
A PivotChart area appears.
On the PivotTable field list, select which fields to be
added to the PivotChart.
Note that a PivotTable gets created as you select the
fields.
Whatever changes you make on the PivotTable, the
PivotChart gets changed accordingly.
You can change the order of sorting the chart.
You may also change the type of PivotChart to use.
Right-click on the PivotChart and click “Change Chart
Type..” or while the PivotChart has been clicked, on the
Design tab on the toolbar, select “Change Chart Type”
and select the different type you would like to use.
What-If Analysis
What-If Utilities
Goal Seek
Scenario Manager
Solver
VLOOKUP and HLOOKUP
Goal Seek
You have two worksheet cells: Cell A1
contains a temperature value in degrees
Fahrenheit, and cell A2 contains the
Fahrenheit-to-Celsius temperature conversion
formula =CONVERT(A1, "F", "C").
Typing 100 in cell A1 returns the Celsius
temperature of approximately 37.8 degrees in
cell A2. But how many degrees Fahrenheit is a
Celsius temperature of 20 degrees?
Goal Seek (cont.)
Solution:
1. Click What-If Analysis ➤ Goal Seek from the
Data tab.
2. In the Set Value box, type or click cell A2.
3. In the To Value box, type 20.
4. In the By Changing Cell box, type or click
cell A1.
5. Click OK.
The Goal Seek Status dialog box displays the target
value, 20, and Excel inserts the answer, 68, into
cell A1.
Scenario Manager
Loan payment calculations.
Enter the following data in the spreadsheet:
cell B1 represents the loan’s interest rate, cell B2
represents the loan term, cell B3 represents the
loan amount, and cell B4 represents the loan
payment.
Let’s call the scenario in Table 1 the Three-
Bedroom House scenario, and let’s call the
scenario in Table 2 the Four-Bedroom House
scenario.
Tables follow.
Scenario Manager (cont.)
Table 1
Interest
6.90%
Months
360
Loan Amount
R 200,000
Loan Payment
Table 2
Interest
6.30%
Months
360
Loan Amount
R 250,000
Loan Payment
Solver
consider the problem of determining the best
theater ticket prices and number of tickets to
sell at those prices for a theater to achieve a
desired box office income amount.
The Theater Ticket Prices worksheet lists three
ticket price points for child, adult, and senior
tickets. The target box office income is simply
the sum of the child, adult, and senior ticket
prices multiplied by their respective number of
tickets to sell.
Solver (cont.)
Using Goal Seek, you can look for one value at a
time: the number of tickets sold for child, adult, or
senior, or the ticket prices for child, adult, or senior.
With Solver, you can still solve for only one value at
a time, but you gain the flexibility of solving for
specific values, minimum values, and maximum
values, and applying various other constraints.
You can use Solver to figure out how many tickets
to sell to achieve an income of exactly $1,000.00,
subject to the following constraints:
Child tickets sell only for $3.00 each.
Solver (cont.)
Adult tickets sell only for $5.00 each.
Senior tickets sell only for $4.00 each.
No more than 100 tickets can be sold to children, adults, or
seniors.
Only full tickets can be sold (no fractional ticket sales are
allowed).
You can run Solver to produce the following result:
If you sell 100 child tickets, 60 adult tickets, and 100 senior
tickets at their respective ticket prices, you will receive
$1,000.00.
In this case, Solver can suggest other possible
combinations of ticket sales to solve this problem.
For example, you could sell 96 child tickets, 92 adult tickets,
and 63 senior tickets; or you could sell 60 child tickets, 92
adult tickets, and 90 senior tickets;
VLOOKUP
Enter the following data into the cells indicated:
Cell Data
D3 - Part
D4 - Bearing
D5 - Bolt
D6 - Cog
D7 - Gear
D8 – Washer
E3 - Price
E4 - $17.34
E5 - $1.54
E6 - $20.21
E7 - $23.56
E8 - $1.43
VLOOKUP (cont.)
Click on cell E1
Click on the Formulas tab.
Choose Lookup & Reference from the
to
open the function drop down list.
Click on VLOOKUP in the list to bring up the
function's dialog box.
In the dialog box, click on the Lookup _value line.
Click on cell D1 in the spreadsheet. This is where
we will type the name of the part we wish to price.
In the dialog box, click on the Table_array line.
cells D4 to E8 in the spreadsheet to
enter the range into the dialog box. This is the
range of data we want VLOOKUP to search.
VLOOKUP (cont.)
In the dialog box, click on the Col_index_num line.
Type the number 2 to indicate that the data we want
returned is in column 2 of the table_array.
In the dialog box, click on the Range_lookup line.
Type the word False to indicate that we want an
exact match for our requested data.
Click OK.
In cell D1 of the spreadsheet, type the word bolt.
The value $1.54 should appear in cell E1 displaying
the price of a bolt as indicated in the table_array.
If you click on cell E1, the complete function =
VLOOKUP ( D1 , D4:E8 , 2 , FALSE ) appears in
the formula bar above the worksheet.
HLOOKUP
If you have an inventory list of parts or a large
membership contact list, HLOOKUP can help you find
data that matches specific criteria such as the price
of a specific item or a person's phone number.
Refer to the image above for more information on
this example.
On D1, enter =HLOOKUP("Widget",
$D$3:$G$4,2,False)
"Widget" - this HLOOKUP function is looking for the price
of Widgets.
Part
Bearing
Gear
Widget
Cog
Price
$17.34
$23.56
$14.76
$20.21
HLOOKUP (cont.)
$D$3:$G$4 - it is looking for this information in the
data table located in cells D3 to G4.
2- HLOOKUP is looking for the price in the second
row of the table.
False - indicates that only an exact match to the
lookup _value "Widget" will be accepted.
The HLOOKUP function returns the results of its
search - $14.76 - in cell D1.
Document Outline
- Microsoft Excel Functions
- Text Functions
- TEXT
- TEXT (cont.)
- TEXT (cont.)
- FIND
- FIND (cont.)
- LEFT
- RIGHT
- MID
- CONCATENATE
- REPLACE
- REPLACE (cont.)
- TRIM
- LEN
- UPPER & LOWER
- DOLLAR
- PROPER
- REPT
- SUBSTITUTE
- BAHTTEXT
- VALUE
- LOGICAL FUNCTIONS
- Comparison Operators
- Comparison Operators (cont.)
- AND
- AND (cont.)
- OR
- OR (cont.)
- IF
- IF (cont.)
- NOT
- Statistical Functions
- AVERAGE
- AVERAGEIF
- AVERAGEIF (cont.)
- LARGE
- LARGE (cont.)
- SMALL
- SMALL (cont.)
- MIN
- MAX
- MODE
- MEDIAN
- RANK
- RANK (cont.)
- Information Functions
- CELL
- CELL (cont.)
- TYPE
- ISBLANK
- ISBLANK (cont.)
- ISERROR
- ISERROR (cont.)
- ISNUMBER
- ISNUMBER (cont.)
- ISTEXT
- ISTEXT (cont.)
- ERROR.TYPE
- Slide 60
- N
- N (cont.)
- Financial Functions
- PMT
- PMT (cont.)
- Slide 66
- Lookup Function
- TRANSPOSE
- TRANSPOSE (cont.)
- Mathematical Functions
- SUM
- AUTOSUM
- PRODUCT
- QUOTIENT
- QUOTIENT (cont.)
- SUMPRODUCT
- ABS
- ROUND
- ROUND (cont.)
- ROUNDUP
- ROUNDUP (cont.)
- ROUNDDOWN
- ROUNDDOWN (cont.)
- RAND
- TRUNC
- TRUNC (cont.)
- MOD
- SUMIF
- SUMIF (cont.)
- INT
- ROMAN
- Slide 92
- RADIANS
- SIN
- COS
- TAN
- ASIN
- ACOS
- ATAN
- Date Functions
- TODAY
- NOW
- NETWORKDAYS
- NETWORKDAYS (cont.)
- Slide 105
- Nested IF Statements
- Other Nested Functions
- PivotTable
- PivotTable (cont.)
- Slide 110
- Changing a PivotTable
- PivotChart
- PivotChart (cont.)
- What-If Analysis
- Goal Seek
- Goal Seek (cont.)
- Scenario Manager
- Scenario Manager (cont.)
- Solver
- Solver (cont.)
- Slide 121
- VLOOKUP
- VLOOKUP (cont.)
- Slide 124
- HLOOKUP
- HLOOKUP (cont.)
Wyszukiwarka
Podobne podstrony:
Microsoft Excel Vba Examples
Microsoft Excel Functions
Microsoft Excel 2010 PL Jezyk VBA i makra Akademia Excela e21vba
Microsoft Excel dla Windows
Nowy Arkusz programu Microsoft Excel
Wykorzystanie arkusza kalkulacyjnego Microsoft Excel
Microsoft Excel 2007 PL Wykresy jako wizualna prezentacja informacji Rozwiazania w biznesie ex27wy
Skróty klawiszowe Microsoft Excel
Analiza i prezentacja danych w Microsoft Excel Vademecum Walkenbacha andaex
Microsoft Excel 2007 PL Analiza danych za pomoca tabel przestawnych Akademia Excela e27aae
Microsoft Excel 2010 PL Formuly i funkcje Akademia Excela e21fae
Analiza i prezentacja danych w Microsoft Excel Vademecum Walkenbacha andaex
Microsoft Excel 2010 PL Formuly i funkcje Akademia Excela e21fae
Microsoft Excel 2010 PL Formuly i funkcje Akademia Excela e21fae
Microsoft Excel SxView Record Parsing Memory Corruption
Analiza i prezentacja danych w Microsoft Excel Vademecum Walkenbacha
Microsoft Excel 2010 PL Formuly i funkcje Akademia Excela
Analiza i prezentacja danych w Microsoft Excel Vademecum Walkenbacha andaex
więcej podobnych podstron