INTRODUCTION TO INFORMATION TECHNOLOGY

TEXT FUNCTIONS
Excel EXACT Function:
Purpose:
Compare two text strings

Return value:
A boolean value (TRUE or FALSE)

Syntax:
=EXACT (text1, text2)

Parameter list:
text1 - The first text string to compare.
text2 - The second text string to compare.

Usage notes:

1. EXACT will compare two text strings and return TRUE if they are the same, and FALSE if not.
EXACT is case-sensitive.
2. You can also use the equal sign in a formula (text1=text2) to compare text strings. However, the
equal sign is not case sensitive.

Excel LEFT Function

Purpose:
Extract text from the left of a string

Return value:
One or more characters.

Syntax:
=LEFT (text, [num_chars])
Parameter list:

text - The text from which to extract characters.

num_chars - [optional] The number of characters to extract, starting on the left side of text. Default = 1.

Rahim Zulfiqar Ali

 INTRODUCTION TO INFORMATION TECHNOLOGY
Usage notes:
Use the LEFT function when you want to extract characters starting at the left side of text.

num_chars is optional and defaults to 1.

LEFT will extract digits from numbers as well. Keep in mind that number formatting (i.e. the currency
symbol $) is not part of a number so is not counted or extracted.

Excel RIGHT Function:

Purpose:
Extract text from the right of a string

Return value:
One or more characters.

Syntax:
=RIGHT (text, [num_chars])

Parameter list:
text - The text from which to extract characters on the right.
num_chars - [optional] The number of characters to extract, starting on the right. Optional, default = 1.

Usage notes:
Use the RIGHT function when you want to extract characters starting at the right side of text.
num_chars is optional and defaults to 1.

RIGHT will extract digits from numbers as well. Keep in mind that number formatting is not part of a and
will not be extracted or counted.

Excel MID Function:

Purpose:
Extract text from inside a string

Return value:
The characters extracted.

Syntax:
=MID (text, start_num , num_chars )

Rahim Zulfiqar Ali

 INTRODUCTION TO INFORMATION TECHNOLOGY
Parameter list:
text - The text to extract from.
start_num - The location of the first character to extract.
num_chars - The number of characters to extract.

Usage notes:
MID returns a specific number of characters from a text string, starting at start_num and continuing
through start_num + num_chars.

Use the MID function when you want to extract text from inside a text string, based on location and
length.

Excel LOWER Function:

Purpose:
Convert text to lower case

Return value:
Text in lower case.

Syntax:
=LOWER (text)

Parameter list:
text - The text that should be converted to lower case.

Usage notes:
All letters in text are converted to lower case.

Numbers and punctuation characters are not affected.

Excel UPPER Function:

Purpose:
Convert text to upper case

Return value:
Uppercase text.

Syntax:
=UPPER (text)

Rahim Zulfiqar Ali

 INTRODUCTION TO INFORMATION TECHNOLOGY
Parameter list:
text - The text thatto convert to upper case.

Usage notes:
All letters in text are converted to upper case. Numbers and punctuation characters are not affected.

Excel PROPER Function:

Purpose:
Capitalize the first letter in each word

Return value:
Text in proper case.

Syntax:
=PROPER (text)

Parameter list:
text - The text that should be converted to proper case.

Usage notes:
Use PROPER to capitalize each word in a given string.

All letters in text will be converted to lower case before the first letter in each word is capitalized.

Numbers and punctuation characters are not affected.

Excel TRIM Function:

Purpose:
Remove extra spaces from text

Return value:
Text with extra spaces removed.

Syntax:
=TRIM (text)

Parameter list:
text - The text from which to remove extra space.

Rahim Zulfiqar Ali

 INTRODUCTION TO INFORMATION TECHNOLOGY
Usage notes:
TRIM strips extra spaces from text, leaving only single spaces between words and no space characters at
the start or end of the text.

Excel TEXT Function:

Purpose:
Convert a number to text in a number format

Return value:
A number as text in the given format.

Syntax:
=TEXT (value, format_text)

Parameter list:
value - The number to convert.
format_text - The number format to use.

Usage notes:
Use the TEXT function to convert a number to text in a specific number format.

TEXT is especially useful when you want to embed the numeric output of a formula or function and
present it in a particular format inside other text. For example, "Sales last year increased by over
$43,500", where the number 43500 has been formatted with a currency symbol and thousands
separator.

format_text must appear in double quotation marks.

Excel LEN Function:

Purpose:
Get the length of text.

Return value:
A number representing the lengh of the text.

Syntax:
=LEN (text)

Parameter list:
text - The text for which to calculate length.

Rahim Zulfiqar Ali

 INTRODUCTION TO INFORMATION TECHNOLOGY

Usage notes:
LEN is a useful when you want to count how many characters there are in some text.

Numbers and dates will also return a length keep in mind that number formatting is not included. (i.e.
the length of "100" formatted as "$100.00" is still 3).

EXCEL STATISTICAL FUNCTIONS

Excel MEDIAN Function:
Purpose:
Get the median of a group of numbers

Return value:
A number representing the median.

Syntax:
=MEDIAN (number1, [number2], ...)

Parameter list:
number1 - A number or cell reference that refers to numeric values.
number2 - [optional] A number or cell reference that refers to numeric values.

Usage notes:
The MEDIAN function returns the median (middle number) in a group of supplied arguments. For
example, =MEDIAN(1,2,3,4,5) returns 3.

When the total number of supplied numbers is odd, the median is calculated as the middle number in
the group. When the total number of supplied numbers is even, the median is calculated as the average
of the two numbers in the middle.

Numbers can be supplied as numbers, ranges, named ranges, or cell references that contain numeric
values. Up to 255 numbers can be supplied.

Excel AVERAGE Functions:

Purpose:
Get the average of a group of numbers

Return value:
A number representing the average.

Rahim Zulfiqar Ali

 INTRODUCTION TO INFORMATION TECHNOLOGY
Syntax:
=AVERAGE (number1, [number2], ...)

Parameter list:
number1 - A number or cell reference that refers to numeric values.
number2 - [optional] A number or cell reference that refers to numeric values.

Usage notes:
The AVERAGE function returns the average (arithmetic mean) of a group of supplied numbers. To
calculate the average, Excel adds the numbers together and divides by the total number of numbers. For
example, AVERAGE (2,4,6) returns 4.

Numbers can be supplied as numbers, ranges, named ranges, or cell references that contain numeric
values. Up to 255 numbers can be supplied.

EXCEL LOGICAL FUNCTIONS:

EXCEL IF FUCNTION:
Purpose:
Test for a specific condition

Return value:
The values you supply for TRUE or FALSE

Syntax:
=IF (logical_test, [value_if_true], [value_if_false])

Parameter list:
logical_test - A value or logical expression that can be evaluated as TRUE or FALSE.
value_if_true - [optional] The value to return when logical_test evaluates to TRUE.
value_if_false - [optional] The value to return when logical_test evaluates to FALSE.

Usage notes:
Use the IF function to test for or evaluate certain conditions. For example, to use the
IF function to assign a pass or fail to a a test score in A1, where 70 is the minimum
score to pass, you could use the this formula to display either "Pass" or "Fail":

Rahim Zulfiqar Ali

 INTRODUCTION TO INFORMATION TECHNOLOGY
=IF(A1>=70, "Pass", "Fail").

IF functions can be nested. For example, if G5 contains a test score, the following
nested IF function can be used to assign a grade:

=IF(G5<64,"F",IF(G5<73,"D",IF(G5<85,"C",IF(G5<95,"B","A"))))

logical_test can use any Comparison Operator listed in the table below:
Comparison operator Meaning Example

= equal to A1=D1

> greater than A1>D1

>= greater than or equal to A1>=D1

< less than A1<D1

<= less than or equal to A1<=D1

<> not equal to A1<>D1

Up to 64 IF functions can be nested. However, in general, you should consider other

functions, like VLOOKUP or HLOOKUP for more complex scenarios, because they can
handle more conditions in more straightforward fashion.

value_if_true can be any value or expression. To return text, enclose the text in
double quotes. If value_if_true is not supplied, and logical_test evaluates to TRUE,
the IF function will return 0. To return TRUE, use the logical value TRUE
forvalue_if_true.
value_if_false can be any value or expression. If logical_test evaluates to FALSE
and value_if_false is not supplied IF will return FALSE.
If any of the arguments to IF are supplied as arrays, the IF function will evaluate
every element of the array.

Rahim Zulfiqar Ali

 INTRODUCTION TO INFORMATION TECHNOLOGY
Excel AND Function:
Purpose:
Test multiple conditions with AND

Return value:
TRUE if all arguments evaluate TRUE; FALSE if not

Syntax:
=AND (logical1, [logical2], ...)

Parameter list:
logical1 - The first condition or logical value to evaluate.
logical2 - [optional] The second condition or logical value to evaluate.

Usage notes:
Use the AND function to test multiple conditions at the same time, up to 255 conditions. Each
logical condition (logical1, logical2, etc.) must evaluate to TRUE or FALSE, or be arrays or
references that contain logical values.

For example, to test if the value in A1 is greater than 0 and less than 5, use the following
formula:

=AND(A1>0,A1<5)
It can be useful to extend the functionality of functions like IF with AND. Using the above
example, you can supply AND as the logical_test for the IF function like so:

=IF(AND(A1>0,A1<5), "Approved", "Denied")

This formula will return "Approved" only if the value in A1 is greater than 0 and less than 5.
Notes:
Text values or empty cells supplied as arguments are ignored.
The AND function will return #VALUE if no logical values are found or created during evaluation.

Rahim Zulfiqar Ali