Microsoft Excel 2007 tutorial: function


Function

I. What is function?

Function is a named sequence of statements that executes as a unit, performs a specific task within a program, and returns a value. In this tutorial, we didn't show you how to create a function. We only introduce you how to use functions. If you want to learn how to create a function, visit VBA for excel 2007.

II. Using functions

The following functions you may need to use in your daily work.

1. SUM: Adds all the numbers in a range of cells . It has the following syntax:

Sum(number1, number2,...)

Number1, number2... are 1 to 255 arguments.

For example, calculate total score of each student in the list below:

Excel 2007 example data

To do this task, do the following:

1. Create a column total.

2. Click the cell under Total column, and then type =sum(

3. Select the range of cells you want to sum.

4. Close the bracket, and then press enter.

Excel 2007 sum function

To calculate the below range of cells, you just drag the fill handle of the sum cell to the cell that you want to display the sum score.

2. SUMIF: Adds the cells specify by a given condition or criteria. It has the following syntax:

Sumif(range, criteria, sum_range,...)

Range is the range of cells that you want to evaluate by the criteria.

Criteria is the condition of the cell range in the form of a numbers, expression, or text that shows which cells will be added. For instance, condition can be expressed as 23,"23",">=23", or "Orange".

Sum_range are the actual cells to add if their corresponding cells in range match condition. If you miss out the sum_range, the cells in range are both evaluated by criteria and added if they match criteria.

For example, calculate quantity of Fanta that has been sold today, according to the following data.

Excel 2007 invoice list

To do this task, do the following:

1. Click the cell that you want to display the total amount of Fanta.

2. Type the following formula:

=SUMIF(B2:B6,"Fanta",C2:C6)

3. Press Enter.

The the sum quantities of Fanta: 198

3. COUNT: Counts the number of cells in a range that contain numbers and counts numbers that you type directly into the list of argument. It has the following syntax:

COUNT(value1,value2,...)

Value1,value2,....are 1 to 255 arguments that can contain or refer to different types of data, but only numbers are counted.

The following examples show how to use COUNT function. They can help you to understand the function clearly.

Excel 2007  a list of data

1. Count the number of cells that contain numbers in the list above.

=COUNT(A17:A25)

The result is 5.

2. Count the number of cells that contain numbers in the last 5 rows of the list.

=COUNT(A21:A25)

The result is 3.

3. Count the number of cells that contain numbers in the list, and the value 5.

=COUNT(A17:A25,5)

The result is 6.

4. COUNTA: Counts the number of cells in a range or array that are not empty and the values that you type directly in the list of argument. It has the following syntax:

Counta(Value1, Value2,...)

Value1,Value2,... are 1 to 255 arguments that represent the data you want to count.

The following examples show how to use COUNTA function. They can help you to understand the function clearly. Use data in the list that you use in COUNT function.

1. Count the number of cells in the list.

=COUNTA(A17:A25)

The result is 8.

2. Count the number of cells in the last 5 rows and the value 3.

=COUNTA(A21:A25,3)

The result is 6.

3. Count the number of cells in the list and the value "Two".

=COUNTA(A17:A25,"Two")

The result is 9.

5. COUNTIF: Counts the number of cells in a range that match the given condition. It has the following syntax:

COUNTIF(range, criteria)

Range is a range of cells that you want to count, including numbers or names, arrays, or references containing numbers. Blank and text values are not counted.

Criteria is the criteria of the range in the form of a numbers, expression, cell reference, or text that shows which cells will be counted. For instance, criteria can be expressed as 23,"23",">=23", "Orange", or B2.

The following examples show how to use COUNTIF function. They can help you to understand the function clearly.

Excel 2007 data

1. Count the number of cells with Fanta in the Product Name column of the list above.

=COUNTIF(A2:A6,"Fanta")

The result is 2.

2. Count the number of cells with Freshy in the Product Name column of the list above.

=COUNTIF(A2:A6,A4)

The result is 1.

3. Count the number of cells with Freshy and Black Milk Soy in the Product Name column of the list above.

=COUNTIF(A2:A6,A4)+COUNTIF(A2:A6,A6)

The result is 2.

4. Count the number of cells with quantities of product greater than 70 in the Quantity column of list above.

=COUNTIF(B2:B6,">70")

The result is 4.

5. Count the number of cells with quantities of product not equal to 100 in the Quantity column of list above.

=COUNTIF(B2:B6,"<>100") or =COUNTIF(B2:B6,"<>" &B4)

The result is 4.

6. Count the number of cells with quantities of product greater than or equal to 50 and less than or equal to 150 in the Quantity column of list above.

=COUNTIF(B2:B6">=50") -COUNTIF(B2:B6">150")

The result is 3.

You can use wildcard characters such as question mark(?) and asterisk(*) in the criteria of COUNTIF function. A question mark matches any single character and an asterisk matches any sequence of characters. The following examples explain how to use wildcard characters.

1. Count the number of cells starting with the letter "F" in the Product Name column above.

=COUNTIF(B2:B6,"f*")

The result is 3.

2. Count the number of cells ending with the letter "y" in the Product Name column above.

=COUNTIF(B2:B6,"*y")

The result is 2.

3. Count the number of cells ending with the letter "a" and having exactly 5 letters in the Product Name column above.

=COUNTIF(B2:B6,"????a*")

The result is 2.

4. Calculate the average of Fanta in the Product Name column above formatted as a percentage with no decimal places.

=COUNTIF(A2:A6,"Fata")/Rows(A2:A6)

The result is 40%.

5. Calculate the average of Coca cola excluding Fanta in the Product Name column above formatted as a percentage with no decimal places.

=COUNTIF(A2:A6,"Coca cola")/(Rows(A2:A6)-COUNTIF(A2:A6,"Fanta"))

The result is 33%.

Note: To format the number as a percentage, select the cell that you want its values formatted as a percentage, and then on the Home tab, in the Number group, click Percent Style Excel 2007 percent style. Alternatively, you can use shortcut key by pressing Ctrl+ Shift+ %.

6. AVERAGE: Returns the average (arithmetic mean) of the arguments. It has the following syntax:

AVERAGE(number1, number2,...)

Number1, number2 are 1 to 255 arguments which you want the average. The arguments can be numbers or names, arrays, or references that contain numbers.

The following examples show how to use AVERAGE function. They can help you to understand the function clearly.

1. Calculate the average of product quantities in the Quantity column above.

=AVERAGE(B2:B6)

The result is 114.

2. Calculate the average of product quantities in the Quantity column above and 12.

=AVERAGE(B2:B6,12)

The result is 97.

7. MAX: Returns the highest in a set of values. It has the following syntax:

MAX(number1, number2,...)

Number1, number2 are 1 to 255 numeric arguments for which you want to calculate the maximum value. The arguments can be numbers or names, arrays, or references that contain numbers.

The following examples show how to use MAX function. They can help you to understand the function clearly.

1. Calculate the largest number of product quantities in the Quantity column above.

=MAX(B2:B6)

The result is 200.

2. Calculate the maximum of product quantities in the Quantity column above and 260.

=MAX(B2:B6,260)

The result is 260.

8. MIN: Returns the lowest value in a set of values. It has the following syntax:

MIN(number1, number2,...)

Number1, number2 are 1 to 255 arguments for which you want to find the lowest value. The arguments can be numbers or names, arrays, or references that contain numbers.

The following examples show how to use MIN function. They can help you to understand the function clearly.

1. Calculate the lowest number of product quantities in the Quantity column above.

=MIN(B2:B6)

The result is 48.

2. Calculate the minimum of product quantities in the Quantity column above and 26.

=MIN(B2:B6,26)

The result is 26.

9. PRODUCT: Multiplies all the numbers given as arguments. It has the following syntax:

PRODUCT(number1, number2,...)

Number1, number2 are 1 to 255 numeric arguments that you want to multiply.

The following examples show how to use PRODUCT function. They can help you to understand the function clearly.

1. Multiply the first three rows of product quantities in the Quantity column above.

=PRODUCT(B2:B4)

The result is 345600.

2. Multiply the the last two rows of product quantities in the Quantity column above and 15.

=PRODUCT(B5:B6,15)

The result is 450000.

 


Comments

DEEPAK  comment

 DEEPAK

nice


2017-08-14




This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.