Excel2010

There are three main parts in this window. If you remember the function name, write it in the ‘Search for a function’ box. If you know the function name and category for sure, you can first

select the category 1

Select a function list	2		usage
and explanation is shown. There is also a link for		4	the
current function at the bottom left corner of the dialog			.

5.4.1 Sum Function

1

2

3

4

Figure 5.8: Insert Function Dialog Box

This function calculates the sum of the numbers within the range. Any cell that is not a number format will be ignored. For the Sum function you can write a single cell addresses followed by commas or you can also use a colon(:) to define a range.

Example 5.5

If your Excel worksheet contains the data shown in Figure 5.9, the following formulas give the results described in the table.

	Formula	Description	Result
	=SUM(3, 12)	Adds 3 and 12 (Using direct numeric values)	15
	=SUM(A2:A4)	Adds the first three numbers in column A	40
	=SUM(A2:A4, 15)	Adds the first 3 numbers in column A, and 15	55
		Adds the values in the last two rows above,
	=SUM(A5,A6, 2)	and 2. Since text values in references are not	2
		translated, the values in them are ignored
		Adds 5, 15 and 1, because the text values in
	=SUM(“5”, 15,	parameters are translated into numbers, and	21
	TRUE)	the logical value TRUE is translated into the
		number
		Figure 5.9: Using Sum Function

	A	B
1	DATA
2	-5
3	15
4	30
5	'5
6	TRUE

Functions and Formulas

71

5.4.2 Average Function

Average function returns the average of the numbers in the selected cells. If your Excel worksheet contains the data shown in Figure 5.10, the following formulas give the results described in the table.

	A
1	DATA
2	7
3	7
4	10
5	27
6	4

Formula	Description	Result
=AVERAGE(A2:A4)	Average of the numbers	8
	7,7,10
=AVERAGE(A2:A6; 5)	Average of the numbers	10
	in A2:A6 and 5

Figure 5.10: Average formula usage

Example 5.6

You start working in the sales department of the SURAT Company which sells computer peripherals and devices. Your boss wants you to prepare reports for every quarter. In this report you will have a list of products. Your task is to prepare the sum and average of each quarter. You also need to prepare the sum and average of each product. The list will be similar to the following:

		A	B	C	D	E	F	G	H
	1							Reseller
	2	Id	Reseller	Quarter 1	Quarter 2	Quarter 3	Quarter 4	Annual	Company
								Total	Average
	3	1	Sony Computers	500	500	600	700	2300	575
	4	2	Compaq Computers	300	350	300	300	1250	312,5
	5	3	Dell Computers	600	600	500	620	2320	580
	6	4	Toshiba Computers	600	650	680	700	2630	657,5
	7	5	Acer Computers	1200	1100	1100	1150	4550	1137,5
	8		TOTAL	3200	3200	3180	3470	13050	652,5
	9		AVERAGE	640	640	639	694

Figure 5.11: SURAT Company Annual Report

72	Microsoft Excel

Analysis and Solution:

You have four formulas;

Total and average formulas for every product and

Total and Average formulas for every quarter.

1.Total formula for the first product will be in cell G3, and it will find the sum of C3…F3. You can use the Sum function here and your formula will be: =Sum (C3:F3).

2.The average formula for products is also similar and will be placed in cell H3: =Average (C3:F3). Having written these formulas, you may copy them to other resellers (G4:H7).

3.The Total for the first quarter will be in cell C8. You can use the Sum function here again: =Sum (C3:C7).

4.Average function for the first quarter will be in C9: =Average (C3:C7). Finally, your table is ready.

5.4.3 Max and Min Functions

The Max function returns the largest value within the given range. And the Min function returns the smallest value within the given range.

Example 5.7
According to the Figure 5.12, write the necessary formulas into
cells F11 and F12 to find the highest and the lowest averages.
Solution:
Write “=MAX(H3:H9)” into cell F11 and “=MIN(H3:H9)” into	Figure 5.12: Using Max and Min functions
cell F12 to find the maximum and minimum averages.

5.4.4 Count Function

Counts the number of cells that contain numeric values. You can use this function to avoid #DIV/0! (division by zero) errors.

Formula	Description	Result
	Counts the number of cells
=Count (A2:A8)	that contain numbers in the	3
	table next
	Counts the number of cells
=Count (A5:A8)	that contain numbers in the	2
	last 4 rows of the table
	Counts the number of cells
=Count (A5:A8,2)	that contain numbers in the	3
	list, and the number 2
	Figure 5.13: Count Function

	A
1	DATA
2	Sales
3	12.08.2008
4
5	19
6	22.24
7	TRUE
8	#DIV/O!

Functions and Formulas

73

Example 5.8

Your teacher wants to keep track of class attendance in an Excel workbook. The table will contain names and dates. If a student is absent from school for a day, he writes the number of lessons the student missed. He wants you to write a formula to count the number of days that the students have not attended. In another formula write the total of missed hours.

	A	B	C	D	E	F	G	...		Y	Z
1		1st Semester Attendance Form
2	Id	Name and surname	15.09.2010	16.09.2010	17.09.2010	18.09.2010	19.09.2010		...	Total	Total
										Days	Hours
3	1	Rob Brooks		2	7				...	2	9
4	2	Raymond Camden							...	0	0
5	3	Michael Dinowitz	1				1	...		2	2
6	4	Adam Churvis	7	7	7				...	3	21
7	5	Shlomy Gantz							...	0	0
8	6	Paul Hastings				5			...	1	5

Figure 5.14: Count Function example

Analysis and Solution:

Your first formula will count the days that the student missed at least one or more hours. So, You can use the Count function to count the number of days he missed lessons. In the other column, you can calculate the sum of the lessons he missed.

For the first student, in cell Y3, write the formula =Count(C3:X3) and for the total hours skipped, in cell Z3, write =Sum(C3:X3). And copy these formulas to the other students.

5.4.5 All in One: Subtotal

Subtotal is a very flexible formula and can be used to summarize the tables or lists. It is generally easier to create a list with subtotals by using this command in the Outline group on the Data tab. You have 11 common functions included in this aspirin function. Once the subtotal list is created, you can modify the function number anytime to change to the desired SUBTOTAL function.

74	Microsoft Excel

	Function Number	Function Number	Function
	(Includes hidden values)	(Ignores hidden values)
	1	101	AVERAGE
	2	102	COUNT
	3	103	COUNTA
	4	104	MAX
	5	105	MIN
	6	106	PRODUCT
	7	107	STDEV
	8	108	STDEVP
	9	109	SUM
	10	110	VAR
	11	111	VARP

Figure 5.15: SUBTOTAL Functions

Function numbers have two variants: the first type includes the hidden rows/columns where the other ignores them.

=SUBTOTAL(function_num, ref1, ref2, ...)

Example 5.9

Here is how you could solve the previous example with this function.

Solution:

For the first student, in cell Y3, write the formula =SUBTOTAL(102, C3:X3) and for the total hours skipped, in cell Z3, write = SUBTOTAL(109,C3:X3).

Here, we use function numbers 102 and 109. You could also use 2 and 9, it’s up to your project design. The difference is that 109 excludes the hidden rows (if any), 9 doesn’t pay any attention to hidden rows. And now, copy these formulas for the other students.

Functions and Formulas

75

5.5 Using Functions and Formulas

In the previous section you studied how to define your own formulas and use some common functions. In this section you will study the usage of other predefined formulas and to get help with them.

1

Figure 5.16: Formulas Tab

2

3

4

5

6

Figure 5.17: Insert Function dialog box

Parameters

(Function Arguments)

Current Function	Function	Cell name button
	description

You already know how to open the Insert Function Window (from the button 1 in the Formulas Tab). When you select a function

using 3
brief information for the selected item. There	6

at the bottom of the window to get more help on the selected function.

After you click OK, it will show another window that will guide you with the function arguments. In the Function Arguments window, you are asked to fill in all the required

separate boxes. If you can’t remember a cell name, click the Cell Name button; it will minimize the function arguments window, letting you select a cell or a range. After you click the same button or press ENTER, it’ll return the address to the Function arguments window. When you finish entering arguments, Excel will show the result in the Formula result section in the Function Arguments window.

So far you have studied some of the common predefined functions. But, we have many others. There are 12 main function groups with around 350 predefined functions.

These groups are
Financial	Text
Date & Time	Logical
Math & Trig	Information
Statistical	Engineering
Lookup & Reference	Cube
Database,	Compatibility

	The result according to		Current Parameter
	current input		description

Figure 5.18: Function arguments window

To explain all these functions we need several books. Nevertheless, we’ll try to explain some useful functions that you might need.

76	Microsoft Excel

5.5.1 Date and Time Functions

5.5.1.1 Date()

You can easily enter a date into a cell by simply typing it while using any of the date formats that Excel recognizes. On the other hand, we also have a Date function to convert numbers into date format. The DATE function takes three arguments: the year, the month, and the day. The following formula, for example, returns a date comprised of the year in cell A1, the month in cell B1, and the day in cell C1:

=DATE(A1,B1,C1)

Date function is useful when you want to create and manipulate a date yourself using numbers. See the section “5.5.1.8 Counting by Months or Years” for an example.

5.5.1.2 Now()

Shows current date and time. If the cell format was General before the function was entered, the result is formatted as a date.

Example 5.10

=Now ()

07/15/2009 15:00

5.5.1.3 Today()

Shows current date. If the cell format was General before the function was entered, the result is formatted as a date.

Example 5.11

=Today () 07/15/2009

5.5.1.4 More about Dates (Optional)

To Excel, a date is simply a number. More precisely, a date is a serial number that represents the number of days since the fictitious date of January 0, 1900.

A serial number of 1 corresponds to January 1, 1900;

a serial number of 2 corresponds to January 2, 1900, and so on.

This system makes it possible to easily deal with dates in formulas. For example, you can create a formula to calculate the number of days between any two dates (just subtract one from the other). Excel supports dates from January 1, 1900, through December 31, 9999 (serial number = 2,958,465).

You can also insert the current date or time (statically) with a shortcut.

Current date: Ctrl+;

Current time: Ctrl+Shift+;

The difference is that when you insert with NOW() or TODAY() the values are renewed every time the worksheet is recalculated.

Functions and Formulas

77

Example 5.12

Your cousin was born on March 17, 2009 and you wonder, how old is she,

now?

Figure 5.19: Arithmetic operations with dates

Solution:

Figure 5.20: Arithmetic operations with times

Just write her birthday in a cell (A1) and write the current day in another cell (B1). Then, in a third cell (C1) write =B1-A1

This will subtract the first day from the second one (today). But, because both inputs are date formatted, the result might be automatically formatted as date. Just change the cell format to Number Format.

Calculating Time difference is the same. If two cells contain time information and you subtract one from another the result will give the difference between them.

This can be helpful for you to calculate extra work time in a week for staff in a company.

5.5.1.5 Summing the Times that exceed 24 Hours (Optional)

When calculating the sum operations, if you don’t pay attention you might have wrong answers.

The problem is because of the number format which is set to General Format and it automatically takes the date format.

Figure 5.21.b: Number Format

Figure 5.21.c: Number Format

Figure 5.21.a: Summing times

Excel uses the 1900 date and time system in which

January 1, 1900 is represented by numeric value 1.

2 represents January 2, 1900. And so on,

July 17, 2009 is represented by 40011.

Here is the question, what is 0 (zero) date for? The zero date is used to represent times without date.

Because, in normal date/time format, you cannot have 25th hour, it increments the day and says that 11:55 minutes more than it. If you change the format, you will see that. We have the solution for this in Format cells Custom formats. [h] directive says to Excel not to use 24 hour system.

78	Microsoft Excel

5.5.1.6 DateDif()

You may notice that this function does not appear in the drop-down function list for the Date & Time category, nor does it appear in the Insert Function dialog box. Therefore, when you use this function, you must always enter it manually.

DATEDIF is a handy function that calculates the number of days, months, or years between two dates. The function takes three arguments: start date, end date, and a code that represents the interested time unit. The following table displays some valid codes for the third argument. (You must enclose the codes in double quotation marks.)

Unit Code	Returns
“y”	The number of complete years in the period.
“m”	The number of complete months in the period.
“d”	The number of days in the period.

Example 5.13

Your elder cousin was born on June 17, 1993 and you wonder how old is he?

Solution:

When we want to calculate the months, years or days difference between any two dates, DateDif is the best. Here, we just provide the start date, end date and the code.

=DateDif(A3, B3, "y")

Then, it returns the result.

5.5.1.7 Counting by Weeks

How can we count by weeks? Because, date is also a number format, you can use arithmetic addition or subtraction to calculate new date values. So, if you insert into A1 the initial date. In B1, you can say

=A1+7

After you copy this formula to the following cells, this will create a series of dates with seven days difference between them.

Figure 5.22: Using the DateDif function

Figure 5.23: Counting by weeks

Functions and Formulas

79

Figure 5.25: Using ABS when subtracting times

5.5.1.8 Counting by Months or Years

Because each month has a different number of days, in a formula, you cannot say =A4+30. But you can use DATE function to produce the desired result.

Figure 5.24: Counting by months

The Year function returns the year of a date. Similarly, the Month function returns the month of a date. So, we can divide the date value in A5 into Year, Month and Day information separately. Then, you add 1 to the Month. Finally, you form the new Date information.

=Date(Year(A5), Month(A5)+1, Day(A5))

You can use the same logic when counting by years.

5.5.1.9 NetWorkDays Function

Here is another very useful function for accountants. The NetWorkDays function calculates the difference between two dates, excluding weekend days (Saturdays and Sundays). As an option, you can specify a range of cells that contain the dates of holidays, which are also excluded.

=NETWORKDAYS(start_date, end_date, holidays)

the holidays option is an optional range that contains the holidays.

5.5.2 Math and Trigonometric Functions

5.5.2.1 Abs()

Calculates the absolute value of a number.

Example 5.14
=ABS(2)	returns 2
=ABS(-2)	returns 2
=ABS(-2.345)	returns 2.345

Example 5.15

When you subtract times, if somehow the date or time gives negative value, Excel cannot show the result. It shows full of hashes in the cell.

If you really intend to have such a result, no problem, go on. But if you want to see simply the time difference between any two times, you can use the ABS function to avoid such problems.

80	Microsoft Excel