EXPERIMENT NO 5
Aim: Working with various excel formulas and functions
Objectives: Understand and demonstrate various formulas and functions of MS Excel
Hardware requirements: Any CPU with Pentium Processor or similar, 256 MB RAM or more, 1 GB Hard Disk or more.
Software requirements: Windows 64 bit / Ubuntu 14 Operating System, MS office 2010 or later, Open Office for Ubuntu
Theory: the worksheet is arranged in “columns” (denoted by letters) and “rows” (indicated by numbers). Each location is a “cell” and a group of cells is known as a “range.”
Function: A function is a calculation or operation that returns a result. The inputs in a function are called “arguments.” All functions begin with an equals sign [=]. That way Excel knows not to treat the arguments as text. For example, = AVERAGE(2,4) is a function but AVERAGE(2,4) is just a string of text. Without an equals sign, Excel will not calculate a result. The arguments in this function are 2 and 4.
Note, Excel uses uppercase letters to list functions, but you can use lower or uppercase letters when you write them.
In Excel, the “Function Library” can be found on the “Formulas” tab.
There are 13 categories of functions, some of which are:
 Mathematical: AVERAGE() – calculates the average of a series of numbers.
 Date and time: DATEVALUE() – converts a string of text like “30 November 2013” to a number so that you can use this number in other date and time functions. You cannot do math with dates unless you convert them to numbers first.
 Text: LEN() – returns the length of a string. For example =LEN(“Excel”) is 5.
 Logical: IF() – the IF() function is written like =IF(<test>, then A, else B). So, if “test” is true, then the result is A; if “test” is not true, then B.
 Lookup and Reference: These are needed to lookup values elsewhere in the spreadsheet. For example, VLOOKUP looks in a table of values to find one cell.
There are also special functions for financial, engineering, and statistics which are listed separately on the “More Functions” menu.
Formula: A formula is combination of “operators”, “operands”, and “functions.” For example, the function =SUM adds a list of numbers. You use a formula like doing a calculation by hand. For example, you could put your family budget into a formula like this:
Remaining cash = (4 * weekly salary) – mortgage – food – utilities
The operators are multiply [*] and subtract []. The operands are the values “weekly salary”, “mortgage”, “food”, and “utilities.” The result is “remaining cash.”
Following are some common and important excel functions.
Excel Text Functions
Functions to Convert Between Upper & Lower Case  
LOWER  Converts all characters in a supplied text string to lower case 
PROPER  Converts all characters in a supplied text string to proper case (i.e. letters that do not follow another letter are upper case and all other characters are lower case) 
UPPER  Converts all characters in a supplied text string to upper case 
UNICHAR  Returns the Unicode character that is referenced by the given numeric value 
UNICODE  Returns the number (code point) corresponding to the first character of a supplied text string 
Information Functions  
LEN  Returns the length of a supplied text string 
FIND  Returns the position of a supplied character or text string from within a supplied text string (casesensitive) 
SEARCH  Returns the position of a supplied character or text string from within a supplied text string (noncasesensitive) 
Excel Logical Functions


Conditional Functions  
IF  Tests a userdefined condition and returns one result if the condition is TRUE, and another result if the condition is FALSE  
SWITCH  Compares a number of supplied values to a supplied test expression and returns a result corresponding to the first value that matches the test expression. 
Excel Date and Time Functions
Current Date & Time  
NOW  Returns the current date & time 
TODAY  Returns today’s date 
Extracting The Components of a Time  
HOUR  Returns the hour part of a usersupplied time 
MINUTE  Returns the minute part of a usersupplied time 
SECOND  Returns the seconds part of a usersupplied time 
Excel Engineering Functions
Converting Between Bases  
BIN2DEC  Converts a binary number to a decimal 
BIN2HEX  Converts a binary number to hexadecimal 
BIN2OCT  Converts a binary number to octal 
DEC2BIN  Converts a decimal number to binary 
Excel Statistical Functions
Count & Frequency  
COUNT  Returns the number of numerical values in a supplied set of cells or values 
Finding the Largest & Smallest Values  
MAX  Returns the largest value from a list of supplied numbers 
MIN  Returns the smallest value from a list of supplied numbers 
LARGE  Returns the Kth LARGEST value from a list of supplied numbers, for a given value K 
SMALL  Returns the Kth SMALLEST value from a list of supplied numbers, for a given value K 
Conclusion: In this experiment, we have studied use of different functions and formulas.
R.No  Name of Student  Date of Performance  Date of Evaluation  Grade  Sign of student  Sign of Faculty 