Microsoft Excel Functions Examples
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Microsoft Excel Functions Examples as PDF for free.
More details
- Words: 8,977
- Pages: 126
Examples
FIND
Text Functions
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
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
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
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
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
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
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
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
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
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.
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
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
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
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.
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”.
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)
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)
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
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.
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
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 2 in the cell reference
=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
C ell B1
(on the formula bar) and the result of the formula. Formula
Description
Result
=N(A2)
Because A2 contains a number, it is 7 returned Because A3 contains text, 0 is returned 0
B2
=N(A3)
B3
=N(A4)
Because A4 is the logical value TRUE, 1 1 is returned
B4
=N(A5)
B5
=N("7")
Because A5 is a date, the serial number is returned (varies with the date system Because "7" used) 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
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.
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
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.
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
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
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 =
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
20040041
Brewer's 2004 August Hickman Health Yeast Protein 2004 August Hickman Feedstuffs Mix Boneshak 2004 August Velasquez Str Ale er Protein 2004 August Hickman Feedstuffs Mix Septembe Iron 2004 r Nilsson Str Lager Reserve
20040040
2004 July
Hickman
20040039
2004 June
Velasquez Str Ale
20040038
2004 April
Hickman
Health
20040037
2004 March
Stewart
Std Ale
20040045 20040044 20040043 20040042
Std Ale
Mary Giant Boneshak er Brewer's Yeast Corn Circle
Sales
Custome r
$800Goods4U $400Winners Countrywi $300 de $900Winners Extra Continent $2,384 Extra al Continent $3,295 al Countrywi $400 de $995Goods4U Moose $2,500 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
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 ThreeBedroom 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 Loan Payment
R 250,000
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
VLOOKUP (cont.)
Click on cell E1
Click on the Formulas tab. Choose Lookup & Reference from the ribbon 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. Drag select 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. Part
Bearing
Gear
Widget
Cog
Price
$17.34
$23.56
$14.76
$20.21
Refer to the image above for more information
on this example. On D1, enter =HLOOKUP("Widget",$D$3:$G$4,2,False)
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.
FIND
Text Functions
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
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
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
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
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
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
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
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
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
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.
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
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
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
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.
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”.
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)
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)
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
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.
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
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 2 in the cell reference
=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
C ell B1
(on the formula bar) and the result of the formula. Formula
Description
Result
=N(A2)
Because A2 contains a number, it is 7 returned Because A3 contains text, 0 is returned 0
B2
=N(A3)
B3
=N(A4)
Because A4 is the logical value TRUE, 1 1 is returned
B4
=N(A5)
B5
=N("7")
Because A5 is a date, the serial number is returned (varies with the date system Because "7" used) 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
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.
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
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.
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
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
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 =
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
20040041
Brewer's 2004 August Hickman Health Yeast Protein 2004 August Hickman Feedstuffs Mix Boneshak 2004 August Velasquez Str Ale er Protein 2004 August Hickman Feedstuffs Mix Septembe Iron 2004 r Nilsson Str Lager Reserve
20040040
2004 July
Hickman
20040039
2004 June
Velasquez Str Ale
20040038
2004 April
Hickman
Health
20040037
2004 March
Stewart
Std Ale
20040045 20040044 20040043 20040042
Std Ale
Mary Giant Boneshak er Brewer's Yeast Corn Circle
Sales
Custome r
$800Goods4U $400Winners Countrywi $300 de $900Winners Extra Continent $2,384 Extra al Continent $3,295 al Countrywi $400 de $995Goods4U Moose $2,500 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
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 ThreeBedroom 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 Loan Payment
R 250,000
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
VLOOKUP (cont.)
Click on cell E1
Click on the Formulas tab. Choose Lookup & Reference from the ribbon 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. Drag select 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. Part
Bearing
Gear
Widget
Cog
Price
$17.34
$23.56
$14.76
$20.21
Refer to the image above for more information
on this example. On D1, enter =HLOOKUP("Widget",$D$3:$G$4,2,False)
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.
Related Documents
Microsoft Excel Functions Examples
May 2020 14
Microsoft Excel Functions
November 2019 14
Microsoft Excel Functions
May 2020 9
Microsoft Excel Functions For Purchasers
June 2020 5
Microsoft Excel Functions And Formulae.
April 2020 10
Excel Functions
December 2019 18More Documents from "iPakistan"
Microsoft Excel Functions Examples
May 2020 14
Repository Wiki New
May 2020 9
Repository Wiki
May 2020 15