Microsoft Excel-functions.pptx

Empowerment Technology

MICROSOFT EXCEL

Understanding Functions

EMB

FUNCTIONS are prewritten formulas. Functions differ from regular formulas in that you supply the value but not the operators, such as +, -, *, or /. For example, you can use the SUM function to add. EMB

When using a function, remember the following:  Use

an equal sign (=) to begin a formula.

 Specify

the function name.

 Enclose

arguments within parentheses. Arguments are values on which you want to perform the calculation. For example, arguments specify the numbers or cells you want to add.

 Use EMB

a comma to separate arguments.

Common Sample Functions: FUNCTIONS SUM AVERAGE

COUNT MIN MAX EMB

DESCRIPTION

EXAMPLE

add values of =SUM(argument) ranges of cells display the =AVERAGE(argument) average value calculate the =COUNT(argument) number of values determine the =MIN(argument lowest value determine the =MAX(argument) maximum value

Here is an example of function: =SUM(2,13,A1,B2:C7) In this function:  The

equal sign begins the function.  SUM is the name of the function.  2, 31, A1, and B2:C7 are the arguments.  Parentheses enclose the arguments.  Commas separate the arguments. EMB

IF FUNCTION  It

is use to conduct conditional tests on values and formulas.

Syntax: =IF(condition,value_if_true,value_if_false)

Example: =IF(A2<=100,”Within the budget”,”Over budget”) EMB

ACTIVITY: Create 10 entries of numbers between 60 to 100 and determine the following: SUM  AVERAGE  COUNT  MIN  MAX 

 If

the AVERAGE is greater than or equal to 75, display the message “You passed the subject”; otherwise, display the message “Study hard!”.

EMB

VLOOKUP FUNCTION  Excel's

VLOOKUP function, which stands for vertical lookup, is used to find specific information that has been stored in a spreadsheet table. This topic includes step by step instructions of how to use Excel's VLOOKUP function.

EMB

The syntax for the VLOOKUP function is: = VLOOKUP ( lookup_value , table_array , col_index_num , range_lookup )

EMB



lookup _value: The value you want to find in the first column of the table_array.



table_array: This is the table of data that VLOOKUP searches to find the information you are after.

* The table_array must contain at least two columns of data. The first column contains the lookup_values. 

col_index_num: The number of the column in the table_array that contains the data you want returned.



range_lookup: A logical value (TRUE or FALSE only) that indicates whether you want VLOOKUP to find an exact or an approximate match to the lookup_value. Typing False will return exact matches only.

EMB

Example: How to Use Excel's VLOOKUP Function 1. Enter the data into cells D1 to E8 as seen in the image above. Remember to leave cell D1 and E1 blank. This is where the result of the VLOOKUP function will be displayed.

2. Click on cell E1 --- the location where the results will be displayed. EMB

3. Click on the Formulas tab. 4. Choose Lookup & Reference from the ribbon to open the function drop down list. 5. Click on VLOOKUP in the list to bring up the function's dialog box. 6. In the dialog box, click on the Lookup _value line. 7. Click on cell D1 in the spreadsheet to enter that cell reference into the dialog box. This is the cell where we will type the part name about which we are seeking information. EMB

8. In the dialog box, click on the Table_array line. 9. Drag select cells D4 to E8 in the spreadsheet to enter that range into the dialog box. The table_array is the table of data that VLOOKUP searches for the lookup_value specified in cell D1.

10. In the dialog Col_index_num line. EMB

box,

click

on

the

11. Type the number 2 to indicate that the data we want returned is in column 2 of the table_array. 12. In the dialog Range_lookup line.

box,

click

on

the

13. Type the word False to indicate that we want an exact match for our requested data. 14. Click OK. 15. In cell D1 of the spreadsheet, type the word bolt. EMB

16. The value $1.54 should appear in cell E1 displaying the price of a bolt as indicated in the table_array. 17. If you click on cell E1, the complete function = VLOOKUP ( D1 , D4:E8 , 2 , FALSE ) appears in the formula bar above the worksheet.

EMB

ACTIVITY:  Create

10 entries for the following labels: ITEM_CODE

 After

ITEM_NAME

typing the item_code, the corresponding item_name should appear in the selected or preferred location of cell.

 Design EMB

your spreadsheet.

Thank you for listening!

EMB