EXPERIMENT NO 5
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 upper-case letters to list functions, but you can use lower or upper-case 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|
|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 (case-sensitive)|
|SEARCH||Returns the position of a supplied character or text string from within a supplied text string (non-case-sensitive)|
Excel Logical Functions
|IF||Tests a user-defined 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 user-supplied time|
|MINUTE||Returns the minute part of a user-supplied time|
|SECOND||Returns the seconds part of a user-supplied 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|