MICROSOFT EXCEL

 

Microsoft Office Excel 2007

Definition:-

Excel 2007 is an application in the Microsoft office suit that can be used to create, revise and save data in a spreadsheet format. It is used to perform calculations using Formulas & functions. The excel application can also be used to analyze, share, and manage information using charts and tables.                                     

Spreadsheet
A spreadsheet is a paper or an electronic document that stores various types of data, such as numbers, text, and non-alphanumeric symbols, in a tabular format. A spreadsheet consists of vertical columns and horizontal rows that intersect to form cells.

Worksheets

A worksheet is a spreadsheet used to store data in the Excel application. An Excel worksheet contains 16,384 Columns (Columns have alphabetical heading A- XFD) and 1,048,576 Rows that intersect to form grids.   

 

 

Excel Formulas & Functions

TIME()

The Excel TIME function is a built-in function that allows you to create a time with individual hour, minute, and second components. The TIME function is useful when you want to assemble a proper time inside another formula.

 

Syntax 

=TIME (hour, minute, second)

Arguments 

hour - The hour for the time you wish to create.

minute - The minute for the time you wish to create.

second - The second for the time you wish to create.

 

Today()

The Excel TODAY function returns the current date, updated continuously when a worksheet is changed or opened. The TODAY function takes no arguments. You can format the value returned by TODAY using any standard date format

Syntax 

=TODAY ()

 

Now()

The Excel NOW function returns the current date and time, updated continuously when a worksheet is changed or opened. The NOW function takes no arguments. 

Syntax 

=NOW ()

SUM()

The Excel SUM function returns the sum of values supplied as multiple arguments. SUM can handle up to 255 individual arguments, which can include numbers, cell references, ranges, arrays, and constants.

Syntax 

=SUM (number1, [number2], [number3], ...)

 

Product ()

The Excel PRODUCT function returns the product of numbers provided as arguments. The PRODUCT function is helpful when when multiplying many cells together. The formula =PRODUCT(A1:A3) is the same as =A1*A2*A3.

Syntax 

=PRODUCT (number1, [number2], ...)

 

MAX()

The Excel MAX function returns the largest numeric value in a range of values. The MAX function ignores empty cells, the logical values TRUE and FALSE, and text values.

Return value 

The largest value in the array.

Syntax 

=MAX (number1, [number2], ...)

Arguments 

number1 - Number, reference to numeric value, or range that contains numeric values.

number2 - [optional] Number, reference to numeric value, or range that contains numeric values.

 

MIN()

The Excel MIN function returns the smallest numeric value in a range of values. The MIN function ignores empty cells, the logical values TRUE and FALSE, and text values.

Return value 

The smallest value in the array.

Syntax 

=MIN (number1, [number2], ...)

 

COUNT()

The Excel COUNT function returns the count of values that are numbers, generally cells that contain numbers. Values can be supplied as constants, cell references, or ranges.

Return value 

A number representing a count of numbers.

Syntax 

=COUNT (value1, [value2], ...)

 

COUNTA()

The Excel COUNTA function returns the count of cells that contain numbers, text, logical values, error values, and empty text (""). COUNTA does not count empty cells.

Return value 

A number representing non-blank cells.

Syntax 

=COUNTA (value1, [value2], ...)

AVERAGE()

The Excel AVERAGE function returns the average of values supplied as multiple arguments. AVERAGE can handle up to 255 individual arguments, which can include numbers, cell references, ranges, arrays, and constants.

Purpose 

Get the average of a group of numbers

Return value 

A number representing the average.

Syntax 

=AVERAGE (number1, [number2], ...)

Arguments 

number1 - A number or cell reference that refers to numeric values.

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

 

SUMIF()

SUMIF is a function to sum cells that meet a single criteria. SUMIF can be used to sum cells based on dates, numbers, and text that match specific criteria. SUMIF supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

Purpose 

Sum numbers in a range that meet supplied criteria

Return value 

The sum of values supplied.

Syntax 

=SUMIF (range, criteria, [sum_range])

Arguments 

range - The range of cells that you want to apply the criteria against.

criteria - The criteria used to determine which cells to add.

sum_range - [optional] The cells to add together. If sum_range is omitted, the cells in range are added together instead.

 

IF()

The IF function can perform a logical test and return one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF function can be combined with logical functions like AND and OR.

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])

Arguments 

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.

VLOOKUP()

VLOOKUP is an Excel function to lookup and retrieve data from a specific column in table. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. The "V" stands for "vertical". Lookup values must appear in the first column of the table, with lookup columns to the right.

Purpose 

Lookup a value in a table by matching on the first column

Return value 

The matched value from a table.

Syntax 

=VLOOKUP (value, tablearray, col_index, [range_lookup])

Arguments 

Lookup value - The value to look for in the first column of a table.

tablearray - The table from which to retrieve a value.

col_index - The column in the table from which to retrieve a value.

range_lookup - [optional] TRUE = approximate match (default). FALSE = exact match.

COUNTIF()

COUNTIF is a function to count cells that meet a single criteria. COUNTIF can be used to count cells with dates, numbers, and text that match specific criteria. The COUNTIF function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

Purpose 

Count cells that match criteria

Return value 

A number representing cells counted.

Syntax 

=COUNTIF (range, criteria)

Arguments 

range - The range of cells to count.

criteria - The criteria that controls which cells should be counted

SUMIFS

SUMIFS is a function to sum cells that meet multiple criteria. SUMIFS can be used to sum values when adjacent cells meet criteria based on dates, numbers, and text. SUMIFS supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

Purpose 

Sum cells that match multiple criteria

Return value 

The sum of the cells that meet all criteria

Syntax 

=SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], ...)

Arguments 

·         sum_range - The range to be summed.

·         range1 - The first range to evaulate.

·         criteria1 - The criteria to use on range1.

·         range2 - [optional] The second range to evaluate.

criteria2 - [optional] The criteria to use on range2.

HLOOKUP

HLOOKUP is an Excel function to lookup and retrieve data from a specific row in table. The "H" in HLOOKUP stands for "horizontal", where lookup values appear in the first row of the table, moving horizontally to the right. HLOOKUP supports approximate and exact matching, and wildcards (* ?) for finding partial matches.

Purpose 

Look up a value in a table by matching on the first row

Return value 

The matched value from a table.

Syntax 

=HLOOKUP (value, table, row_index, [range_lookup])

Arguments 

value - The value to look up.

table - The table from which to retrieve data.

row_index - The row number from which to retrieve data.

range_lookup - [optional] A boolean to indicate exact match or approximate match. Default = TRUE = approximate match.

PMT()

The Excel PMT function is a financial function that returns the periodic payment for a loan. You can use the NPER function to figure out payments for a loan, given the loan amount, number of periods, and interest rate.

 

Purpose 

Get the periodic payment for a loan

Return value 

loan payment as a number

Syntax 

=PMT (rate, nper, pv, [fv], [type])

Arguments 

rate - The interest rate for the loan.

nper - The total number of payments for the loan.

pv - The present value, or total value of all loan payments now.

fv - [optional] The future value, or a cash balance you want after the last payment is made. Defaults to 0 (zero).

type - [optional] When payments are due. 0 = end of period. 1 = beginning of period. Default is 0.

INDEX()

The Excel INDEX function returns the value at a given position in a range or array. You can use index to retrieve individual values or entire rows and columns. INDEX is often used with the MATCH function, where MATCH locates and feeds a position to INDEX.

Purpose 

Get a value in a list or table based on location

Return value 

The value at a given location.

Syntax 

=INDEX (array, row_num, [col_num], [area_num])

Arguments 

array - A range of cells, or an array constant.

row_num - The row position in the reference or array.

col_num - [optional] The column position in the reference or array.

area_num - [optional] The range in reference that should be used.

 

 

Dated if()

 

The Excel DATEDIF function returns the difference between two date values in years, months, or days. The DATEDIF (Date + Dif) function is a "compatibility" function that comes from Lotus 1-2-3. For reasons unknown, it is only documented in Excel 2000, but you can use it in your formulas in all Excel versions since that time.

 

Note: Excel won't help you fill out the arguments for DATEDIF like other functions, but it will work when configured correctly.

 

Purpose 

Get days, months, or years between two dates

Return value 

A number representing time between two dates

Syntax 

=DATEDIF (start_date, end_date, unit)

Arguments 

start_date - Start date in Excel date serial number format.

end_date - End date in Excel date serial number format.

unit - The time unit to use (years, months, or days).

 

Unit	Result
"Y"	Difference in complete years
"M"	Difference in complete months
"D"	Difference in days
"MD"	Difference in days, ignoring months and years
"YM"	Difference in months, ignoring days and years
"YD"	Difference in days, ignoring years

SEARCH()

The Excel SEARCH function returns the location of one text string inside another. SEARCH returns the position of the first character of find_text inside within_text. Unlike FIND, SEARCH allows wildcards, and is not case-sensitive.

Purpose 

Get the location of text in a string

Return value 

A number representing the location of find_text.

Syntax 

=SEARCH (find_text, within_text, [start_num])

Arguments 

find_text - The text to find.

within_text - The text to search within.

start_num - [optional] Starting position in the text to search. Optional, defaults to 1.

 

ISTEXT

The Excel ISTEXT function returns TRUE when a cell contains a text, and FALSE if not. You can use the ISTEXT function to check if a cell contains a text value, or a numeric value entered as text.

Purpose 

Test for a text value

Return value 

A logical value (TRUE or FALSE)

Syntax 

=ISTEXT (value)

Arguments 

value - The value to check.

 

 

ISNUMBER()

The Excel ISNUMBER function returns TRUE when a cell contains a number, and FALSE if not. You can use ISNUMBER to check that a cell contains a numeric value, or that the result of another function is a number.

Purpose 

Test for numeric value

Return value 

A logical value (TRUE or FALSE)

Syntax 

=ISNUMBER (value)

Arguments 

value - The value to check.