Excel2010

5.5.2.2 Pi()

Gives the value of Pi with an accuracy of 15 digits

Example 5.16

=Pi()

returns 3.14159265358979

5.5.2.3 Radians()

It converts degrees into radians. Trigonometric functions use radians as arguments. E.g. Because, following the Sin function, it waits for the argument in radians, when you write =Sin(30), it will not produce your desired result. You should convert 30 into radians first.

Example 5.17

=Sin(Radians(30))

returns 0.5

5.5.2.4 Degrees()

Converts radians into degrees

Example 5.18
=Degrees(Pi())	returns 180
=Degrees(Pi()/3)	returns 60

5.5.2.5 Sin()

Returns the sine of a given angle. If your argument is in degrees, multiply it by PI()/180 or use the radians function to convert it into radians. You can also use the cosine function in the same way.

Example 5.19
=Sin(Radians(30))	returns 0.5
=Sin(30*Pi()/180)	returns 0.5
=Cos(Radians(60))	returns 0.5
=Cos(60*Pi()/180)	returns 0.5

Functions and Formulas

81

	A	B
1	2	2
2	1	3
3	4	1
4	2	2

5.5.2.6 Fact()

Returns the factorial of a number. n factorial is the product of the numbers 1 through n. n! = 1*2*3*…*n.

Example 5.20
=Fact(5)	returns 120
=1*2*3*4*5	returns 120

5.5.2.7 Int()

Rounds the given real number down to the nearest integer

Example 5.21

=Int(2.768)

returns 2

=Int(-2.768) returns -3

5.5.2.8 Mod()

Returns the remainder after a number is divided by a divisor. The result has the same sign as the divisor.

Example 5.22

=Mod(23,5)

returns 3

5.5.2.9 Power()

Returns the result of a number raised to a power

Example 5.23

=Power(2,4)

returns the fourth power of two 16

5.5.2.10 Product()

Multiplies the numbers within the given range

Example 5.24

According to next Figure, the following formulas give the results shown below

=Product(A1:A4) returns 16

=Product(B1:B4) returns 12

=Product(A1:B4) returns 192

82	Microsoft Excel

5.5.2.11 Round()

Rounds a number to the specified number of digits

=Round (number, num_digits)

Number: is the number you want to round.

Num_digits: specifies the number of digits to which you want to round. If Num_digits is greater than 0 (zero), then Number is rounded to the specified number of decimal places. If Num_digits is 0, then Number is rounded to the nearest integer. If Num_digits is less than 0, then Number is rounded to the left of the decimal point.

Example 5.25
=Round(56.538,-3)		0	=Round(56.538,0)		57
=Round(56.538,-2)		100	=Round(56.538,1)		56.5
=Round(56.538,-1)		60	=Round(56.538,2)		56.54

5.5.2.12 Trunc()

It’s similar to Round function. But, Trunc removes the digits after the specified number of digits from the fractional part

=Trunc (number, num_digits)

Number: is the number you want to truncate.

Num_digits: is a number specifying the precision of the truncation. The default value for Num_digits is 0 (zero).

Example 5.26
=Trunc(253.268569,3)	returns 253.268
=Trunc(253.268569,2)	returns 253.26

5.5.2.13 SumIf()

Calculates the sum of the numbers within the range according to the given criteria

=SUMIF (range, criteria, sum_range)

Range: Is the range of cells you want to be manipulated.

Criteria: Is the criteria in the form of a number, expression, or text that defines which cells are to be added.

Sum_Range: are the actual cells to sum.

Example 5.27

Summing negative numbers in a range.

=SumIf(Range, "<0")

Criterion can be

a direct number	7
a text	“Book”
an expression	“>=5”

Functions and Formulas

83

	A	B
1	Ages	Cost
2	2	23
3	4	54
4	1	76
5	8	45
6	4	98
7	5	34
8	3	27
9	RESULT	126

Example 5.28

Write a formula to calculate the sum of the numbers in the range B2:B8 where the ages in A2:A8 is less than 4.

Solution:

Write this formula in cell B9 =SumIf (A2:A8, "<4", B2:B8)

Let Excel help you write SumIf functions:

1.Choose Office Excel Options to display the Excel Options dialog box.

2.Click the Add-ins tab on the left.

3.Select Excel Add-Ins from the drop-down list labeled Manage.

4.Click Go to display the Add-Ins dialog box.

5.Place a check mark next to Conditional Sum Wizard.

6.Click OK.

5.5.2.14 Sqrt()

Returns the square root of a number

Example 5.29
= Sqrt(4)	returns 2
= Sqrt(25634)	returns 160.1062147

In Logical tests, logical operators are used:

<, <=, >, >=, =, <>.

They produce True or False results.

5.5.2.15 RandBetween(bottom, top)

Returns an evenly distributed random number greater than or equal to the bottom and less than or equal to the top value. A new random number is returned every time the worksheet is calculated.

5.5.3 Logical Functions

5.5.3.1 And(), Or(), Not() functions

And, Or, Not are used to process and combine logical results.

And(LT1, LT2,…) checks if all the arguments (Logical Tests) are true. It returns TRUE if all are true.

Or(LT1, LT2,…)) checks if any of the arguments are true. It returns TRUE if any argument is true, and returns FALSE if all are false.

Not() changes False to True or True to False.

84	Microsoft Excel

5.5.3.2 If()

If-statement is one of the most commonly used logical functions. It checks a boolean-expression to decide a result and returns one of the values according to the result.

Usage : If(LT, Value_if_true, Value_if_false)

Example 5.30

Write a formula that decides if a student has passed or failed. The results that are less than or equal to 4 means that he/she failed, and any (integer) result which is greater than 4 means that he passed.

Solution:

Figure 5.26: Using the IF function

The formula will be written in E1 and it’ll test if the value in D1 is less than or equal to 4. (D1<=4)

If that logical test is true it’ll return the first value (“Failed”), otherwise it’ll return the second value (“Passed”). So the final formula is:

=IF(D1<=4,"Failed","Passed")

5.5.4 Statistical Functions

Statistics searches the occurrences, distributions, frequencies, relations and possibilities of events. It is one of the developing sciences of the future. If there is the best and the worst, then there is statistics. So, it’s everywhere.

And as you know, when functions are an essential part of something, Excel is good for that. Here are some basic and essential functions for statistics.

5.5.4.1 Count()

Counts the number of cells that contain a number in the given range

=Count(value1, value2, value3,….)

Example 5.31

According to Figure 5.27, find the number of students who take Phy.Edu lesson.

Solution:

Write this formula in cell E11 =Count(E2:E10)

Figure 5.27: Using the IF function

Functions and Formulas

85

5.5.4.2 CountBlank()

It’s similar to the Count function. But, it counts the number of empty cells in the selected range.

CountBlank(range)

5.5.4.3 CountA()

Counts the number of cells that are not empty within the selected range. =CountA(value1,value2,...)

5.5.4.4 CountIf()

Counts the number of cells within the selected range that comply with the given criteria.

=CountIf(range, criteria)

Here are some examples of CountIf functions:

=COUNTIF(Data,12)	Returns the number of cells containing the value 12
=COUNTIF(Data,”<0”)	Returns the number of cells containing a negative value
=COUNTIF(Data,”<>0”)	Returns the number of cells not equal to 0
=COUNTIF(Data,A1)	Returns the number of cells equal to the contents of cell A1
=COUNTIF(Data,”>”&A1)	Returns the number of cells greater than the value in cell A1
=COUNTIF(Data,”*”)	Returns the number of cells containing text
=COUNTIF(Data,”budget”)	Returns the number of cells containing the single word budget (not case sensitive)
=COUNTIF(Data,”*budget*”)	Returns the number of cells containing the text budget anywhere within the text
=COUNTIF(Data,”A*”)	Returns the number of cells containing text that begins with the letter A
=COUNTIF(Data,TODAY())	Returns the number of cells containing the current date
=COUNTIF(Data,”>”&AVERAGE(Data))	Returns the number of cells with a value greater than the average
=COUNTIF(Data,TRUE)	Returns the number of cells containing logical TRUE
=COUNTIF(Data,”#N/A”)	Returns the number of cells containing the #N/A error value

For using multiple criteria Excel provided a new function series: AverageIfs, SumIfs, CountIfs are some of them.

=COUNTIFS(Data, ">10", Data, "<20")

Returns the number of cells whose value is between 10 and 20

Example 5.32

According to Figure 5.27, write the necessary formula in cell F11 to find out the number of students whose Phy.Edu. average is greater than or equal to 9.

Solution:

Write the formula in cell F11 = CountIf (E2:E10; ">= 9")

86	Microsoft Excel

5.5.4.5 Median()

Median is the middlemost number in a list. So to get it, first, we need to put items in order.

Example 5.33

The Simpson family drove through 7 states on their summer vacation. The price for gasoline varied from state to state. They stored their prices in a workbook. What was the median gasoline price paid?

Solution:

If you put the items in order, you get:

$1.79, $1.84, $1.84, $1.84, $1.96, $1.96, $2.11. The result is 1.84, because it’s the 4th (middle) element.

5.5.4.6 Mode()

The Mode of a set of data is the value in the set that occurs most often.

Example 5.34

On a cold winter day in January, the temperature for 9 cities is recorded. What is the mode of these temperatures?

Solution:

If you put the items in order, you get:

-8, -3, -1, 0, 0, 0, 4, 5, 12. As you see 0 repeats 3 times which’s the mode of these series.

5.5.4.7 Standard deviation

In a set of data, standard deviation is a statistic that tells us how tightly all the various examples can be clustered around the mean. This can be useful when comparing results from different sources.

Example 5.35

Let’s say that, North Elementary school (NES) has a higher average than South Elementary school (SES). Your first reaction might be to say that the kids at NES are smarter.

Figure 5.28: Median of a list

Figure 5.29: Mode of a list

Functions and Formulas

87

But a bigger standard deviation for one school tells you that there are relatively more kids at that school towards one extreme or the other. By asking a few more questions, you might find that mean was skewed up because the school district sends all of the gifted kids to NES. Or, that SES scores were dragged down because of the students who recently have been “mainstreamed” from special education classes and have all been sent to SES.

To make the question clear, I made the first 4 marks for both schools the same. Only the last two marks change. As you see from the example, the last 2 students from class NES have closer marks to their friends. On the other hand, the last 2 students from SES are very low. If you check only the general average, you will think that the kids at NES are smarter. But actually, Standard deviation 7 shows that students in NES have all closer results. But, 26 tells that there is big gap with some students in SES where you can understand the reason when you have further analysis.

Figure 5.30: STDEV function

5.5.4.8 Correlation

Correlation is a statistical technique that can show whether and how strongly pairs of variables are related.

CORREL(array1,array2)

Closer to zero means that there is a weak or no relation.

Closer to 1 means that there is a strong relation.

Closer to minus one means strong negative relation between variables.

Example 5.36

In a biology study, you have searched the Age and blood pressure relation among people. You recorded your data in a worksheet and you want to search whether there is a relation between age and blood pressure.

On Figure 5.31, the result 0.89 says that there is a strong positive relation between blood pressure and age. (When age increases mostly the blood pressure increases too.)

In another research, you want to determine if there is a relation between the number of absences and the final grade. The result is -0.945, a strong negative relation, which tells that when absence increases final grade mostly decreases.

Figure 5.31: Using the CORREL Function

88	Microsoft Excel

5.5.5 Text Functions

5.5.5.1 Mid()

Returns Num_Chars characters from the text starting from the start_num character

=Mid(text, start_num, num_chars)

Text: is the text string containing the characters you want to extract.

Start_num: is the position of the first character you want to extract from the text.

Num_chars: specifies the number of characters you want Mid to return from the text.

5.5.5.2 Find()

Finds one text string (find_text) within another text string (within_text), and returns the starting position of find_text, from the first character of within_text. You can also use Search to find one text string within another, but unlike Search, Find is case sensitive and doesn’t allow wildcard characters.

=Find(find_text, within_text, start_num)

Find_text: is the text you want to find.

Within_text: is the text containing the text you want to find.

Start_num: specifies the character at which to start the search.

5.5.5.3 Len()

Returns the number of characters in a text string

=Len(text)

5.5.5.4 Left()

Returns the first character or characters in a text string, based on the number of characters you specify from the left.

=Left(text, num_chars)

5.5.5.5 Right()

Returns the last character or characters in a text string, based on the number of characters you specify from the right.

=Right(text, num_chars)

Text: is the text string containing the characters you want to extract.

Num_chars: specifies the number of characters you want to extract.

Functions and Formulas

89

Example 5.37

According to the Figure 5.32, write these formulas into suitable cells, and examine the results.

=Find( " ",C3)	Finds the first position of space char in C3
=Len(C3)	Return the length of C3
=LEFT(C3,C7)	Copy C7 (6) chars from C3
=RIGHT(C3,C8-C7)	Get C8-C7 (13-6=7) chars from the right of C3
=MID(C3,C7+1,15)	Get 15 chars from C3 starting from the
Figure 5.32: Using Text functions	(C7+1)th (7th) char

5.5.6 Lookup & Reference Functions

5.5.6.1 VLookUp()

Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table.

=VLookUp (lookup_value, table_array, col_index_num, range_lookup)

Lookup_value: is the value to be found in the first column of the array. Lookup_value can be a value, a reference, or a text string.

Table_array: is the table of information in which data is searched. Use a reference to a range or a range name, such as Database or List.

Col_index_num: is the column number in the table_array from which the matching value must be returned.

Range_lookup: is a logical value that specifies whether you want VLookUp to find an exact match or an approximate match.

5.5.6.2 HLookUp()

Searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array.

=HLookUp(lookup_value,table_array,row_index_num,range_lookup)

Lookup_value: is the value to be matched in the first row of the table. Lookup_value can be a value, a reference, or a text string.

Table_array: is a table of information in which data is looked up. Use a reference to a range or a range name. The values in the first row of table_array can be text, numbers, or logical values.

Row_index_num: is the row number in the table array from which the matching value will be returned.

Range_lookup: is a logical value that specifies whether you want HLookUp to find an exact match or an approximate match.

90	Microsoft Excel