Have you ever wondered why VLOOKUP works only in one direction! What would you do if you are in a situation where you need to apply VLOOKUP in the opposite direction? Would you give a negative sign to the range_lookup to identify the column from where you need to pick the data? Well, not quite literally.
Excel gives us two amazing function (INDEX and MATCH) to take care of such a situation. You can combine these two functions to get the desired result for which you'd have wanted a reverse VLOOKUP function.
Friday, January 29, 2010
Thursday, January 28, 2010
Important Excel Functions
Well well well... we have had enough of Introduction and History I guess. Now it's time we're into the business. Let's start with some Excel Functions. Functions that does make our lives less miserable [:)] when it comes to calculation. So, here we go...
String Functions
CHAR [Number] Returns the character specified by a number.
CLEAN [Text] Removes all nonprintable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operating system. For example, you can use CLEAN to remove some low-level computer code that is frequently at the beginning and end of data files and cannot be printed.
CODE [Text] Returns a numeric code for the first character in a text string.
CONCATENATE [Text1, Text2,…] Joins several text strings into one text string
EXACT [Text1, Text2] Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive but ignores formatting differences. Use EXACT to test text being entered into a document.
FIND [find_text,within_text,start_num] FIND finds one text string (find_text) within another text string (within_text), and returns the number of 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.
LEFT [text,num_chars] LEFT returns the first character or characters in a text string, based on the number of characters you specify.
LEN [text] LEN returns the number of characters in a text string.
LOWER [text] Converts all uppercase letters in a text string to lowercase.
MID [text,start_num,num_chars] MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.
PROPER [text] Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.
REPLACE [old_text,start_num,num_chars,new_text] REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string.
RIGHT [text,num_chars] RIGHT returns the last character or characters in a text string, based on the number of characters you specify.
SEARCH [find_text,within_text,start_num] SEARCH returns the number of the character at which a specific character or text string is first found, beginning with start_num. Use SEARCH to determine the location of a character or text string within another text string so that you can use the MID or REPLACE functions to change the text.
SUBSTITUTE [text,old_text,new_text,instance_num] Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string.
T [value] Returns the text referred to by value.
TEXT [value, format_text] Converts a value to text in a specific number format.
TRIM [text] Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.
UPPER [text] Converts text to uppercase.
Statistical, Mathematical/Numeric Functions
RANK [number,ref,order] Returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. (If you were to sort the list, the rank of the number would be its position.)
ABS [Number] Returns the absolute value of a number. The absolute value of a number is the number without its sign (+ or -)
AVERAGE [Number1, Number2,…] Returns the average (arithmetic mean) of the arguments.
CEILING [Number, Significance] Returns number rounded up, away from zero, to the nearest multiple of significance. For example, if you want to avoid using pennies in your prices and your product is priced at $4.42, use the formula =CEILING(4.42,0.05) to round prices up to the nearest nickel.
COUNT [Val1, Val2, Val3, …] Counts the number of cells that contain numbers and also numbers within the list of arguments. Use COUNT to get the number of entries in a number field that's in a range or array of numbers.
COUNTA [Val1, Val2, Val3, …] Counts the number of cells that are not empty and the values within the list of arguments. Use COUNTA to count the number of cells that contain data in a range or array.
COUNTBLANK [Range] Counts empty cells in a specified range of cells.
COUNTIF [Range, Criteria] Counts the number of cells within a range that meet the given criteria.
FLOOR [number, significance] Rounds number down, toward zero, to the nearest multiple of significance.
INT [number] Rounds a number down to the nearest integer.
LARGE [array, k] Returns the k-th largest value in a data set. You can use this function to select a value based on its relative standing. For example, you can use LARGE to return the highest, runner-up, or third-place score.
MAX [number1,number2,…] Returns the largest value in a set of values.
MIN [number1,number2,…] Returns the smallest number in a set of values.
MOD [number, divisor] Returns the remainder after number is divided by divisor. The result has the same sign as divisor.
POWER [number, power] Returns the result of a number raised to a power.
PRODUCT [number1,number2,…] Multiplies all the numbers given as arguments and returns the product.
QUOTIENT [numerator, denominator] Returns the integer portion of a division. Use this function when you want to discard the remainder of a division.
If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.
RAND [RAND( )] Returns an evenly distributed random number greater than or equal to 0 and less than 1. A new random number is returned every time the worksheet is calculated
ROUND [number, num_digits] Rounds a number to a specified number of digits.
ROUNDDOWN [number, num_digits] Rounds a number down, toward zero.
ROUNDUP [number, num_digits] Rounds a number up, away from 0 (zero).
SUBTOTAL [function_num, ref1, ref2, …] Returns a subtotal in a list or database. It is generally easier to create a list with subtotals using the Subtotals command (Data menu). Once the subtotal list is created, you can modify it by editing the SUBTOTAL function.
Function_Num Functions
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP
SUMIF [range, criteria, sum_range] Adds the cells specified by a given criteria.
SUMPRODUCT [array1, array2, array3, …] Multiplies corresponding components in the given arrays, and returns the sum of those products.
TRUNC [number, num_digits] Truncates a number to an integer by removing the fractional part of the number.
Lookup/Reference Functions
ADDRESS [Row_num, Col_num, Abs_num] Creates a cell address as text, given specified row and column numbers.
COLUMN [Reference] Returns the column number of the given reference.
COLUMNS [Array (Reference)] Returns the number of columns in an array or reference
HLOOKUP [lookup_value, table_array, row_index_num, range_lookup] 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. Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find.
The H in HLOOKUP stands for "Horizontal."
INDEX [array, row_num, column_num] Returns the reference of the cell at the intersection of a particular row and column. If the reference is made up of nonadjacent selections, you can pick the selection to look in.
The INDEX function has two syntax forms: array and reference. The array form always returns a value or an array of values; the reference form always returns a reference.
INDEX [reference, row_num, column_num, area_num] Returns the reference of the cell at the intersection of a particular row and column. If the reference is made up of nonadjacent selections, you can pick the selection to look in.
INDIRECT [ref_text, a1] Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.
LOOKUP [lookup_value, lookup_vector, result_vector] Returns a value either from a one-row or one-column range or from an array. The LOOKUP function has two syntax forms: vector and array. The vector form of LOOKUP looks in a one-row or one-column range (known as a vector) for a value and returns a value from the same position in a second one-row or one-column range. The array form of LOOKUP looks in the first row or column of an array for the specified value and returns a value from the same position in the last row or column of the array.
LOOKUP [lookup_value, array] Returns a value either from a one-row or one-column range or from an array. The LOOKUP function has two syntax forms: vector and array. The vector form of LOOKUP looks in a one-row or one-column range (known as a vector) for a value and returns a value from the same position in a second one-row or one-column range. The array form of LOOKUP looks in the first row or column of an array for the specified value and returns a value from the same position in the last row or column of the array.
MATCH [lookup_value, lookup_array, match_type] Returns the relative position of an item in an array that matches a specified value in a specified order. Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself.
OFFSET [reference, rows, cols, height, width] Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.
ROW [reference] Returns the row number of a reference.
ROWS [array] Returns the number of rows in a reference or array.
TRANSPOSE [array] Returns a vertical range of cells as a horizontal range, or vice versa. TRANSPOSE must be entered as an array formula in a range that has the same number of rows and columns, respectively, as an array has columns and rows. Use TRANSPOSE to shift the vertical and horizontal orientation of an array on a worksheet.
VLOOKUP [lookup_value, table_array, col_index_num, range_lookup] 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. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data you want to find.
The V in VLOOKUP stands for "Vertical."
Logical Functions
AND [Logical1, Logical2,….] Returns TRUE if all its arguments are TRUE; returns FALSE if one or more argument is FALSE.
IF [logical_test, value_if_true, value_if_false] Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.
Use IF to conduct conditional tests on values and formulas.
OR [logical1, logical2, …] Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.
Information Functions
CELL [Information Type; eg, "Address", "Filename", "Row", etc] Returns information about the formatting, location, or contents of the upper-left cell in a reference.
ISERROR [value] Checks if the value is any error value
Date and Time Functions
DATE [Year, Month, Day] Returns the sequential serial number that represents a particular date. If the cell format was General before the function was entered, the result is formatted as a date.
DATEDIF [Start Date, End Date, "Y"/"YM"/"MD"] You can calculate a persons age based on their birthday and todays date.
DATEVALUE [Date_Text] Returns the serial number of the date represented by date_text. Use DATEVALUE to convert a date represented by text to a serial number.
DAY [Serial_Number] Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31.
DAYS360 [Start Date, End Date] Returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations. Use this function to help compute payments if your accounting system is based on twelve 30-day months.
EDATE [start_date, months] Returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue.
EOMONTH [start_date, months] Returns the serial number for the last day of the month that is the indicated number of months before or after start_date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.
HOUR [serial_number] Returns the hour of a time value. The hour is given as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.).
MINUTE [Serial_Number] Returns the minutes of a time value. The minute is given as an integer, ranging from 0 to 59.
MONTH [Serial_Number] Returns the month of a date represented by a serial number. The month is given as an integer, ranging from 1 (January) to 12 (December).
NETWORKDAYS [start_date, end_date, holidays] Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.
If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.
NOW [Now()] Returns the serial number of the current date and time. If the cell format was General before the function was entered, the result is formatted as a date.
TIME [hour, minute, second] Returns the decimal number for a particular time. If the cell format was General before the function was entered, the result is formatted as a date.
The decimal number returned by TIME is a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).
TIMEVALUE [time_text] Returns the decimal number of the time represented by a text string. The decimal number is a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).
TODAY [TODAY( )] Returns the serial number of the current date. The serial number is the date-time code used by Microsoft Excel for date and time calculations. If the cell format was General before the function was entered, the result is formatted as a date.
WEEKDAY [serial_number, return_type] Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.
WEEKNUM [serial_num, return_type] Returns a number that indicates where the week falls numerically within a year.
If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.
WORKDAY [start_date, days, holidays] Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed.
If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.
YEAR [serial_number] Returns the year corresponding to a date. The year is returned as an integer in the range 1900-9999.
Database Functions
DAVERAGE [Datebase, Field, Criteria] Averages the values in a column of a list or database that match conditions you specify.
DCOUNT [Datebase, Field, Criteria] Counts the cells that contain numbers in a column of a list or database that match conditions you specify. The field argument is optional. If field is omitted, DCOUNT counts all records in the database that match the criteria.
DCOUNTA [Datebase, Field, Criteria] Counts the nonblank cells in a column of a list or database that match conditions you specify. The field argument is optional. If field is omitted, DCOUNTA counts all records in the database that match the criteria.
DGET [Datebase, Field, Criteria] Extracts a single value from a column of a list or database that matches conditions you specify.
DMAX [Datebase, Field, Criteria] Returns the largest number in a column of a list or database that matches conditions you specify.
DMIN [Datebase, Field, Criteria] Returns the smallest number in a column of a list or database that matches conditions you specify.
DSUM [Datebase, Field, Criteria] Adds the numbers in a column of a list or database that match conditions you specify.
String Functions
CHAR [Number] Returns the character specified by a number.
CLEAN [Text] Removes all nonprintable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operating system. For example, you can use CLEAN to remove some low-level computer code that is frequently at the beginning and end of data files and cannot be printed.
CODE [Text] Returns a numeric code for the first character in a text string.
CONCATENATE [Text1, Text2,…] Joins several text strings into one text string
EXACT [Text1, Text2] Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive but ignores formatting differences. Use EXACT to test text being entered into a document.
FIND [find_text,within_text,start_num] FIND finds one text string (find_text) within another text string (within_text), and returns the number of 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.
LEFT [text,num_chars] LEFT returns the first character or characters in a text string, based on the number of characters you specify.
LEN [text] LEN returns the number of characters in a text string.
LOWER [text] Converts all uppercase letters in a text string to lowercase.
MID [text,start_num,num_chars] MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.
PROPER [text] Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.
REPLACE [old_text,start_num,num_chars,new_text] REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string.
RIGHT [text,num_chars] RIGHT returns the last character or characters in a text string, based on the number of characters you specify.
SEARCH [find_text,within_text,start_num] SEARCH returns the number of the character at which a specific character or text string is first found, beginning with start_num. Use SEARCH to determine the location of a character or text string within another text string so that you can use the MID or REPLACE functions to change the text.
SUBSTITUTE [text,old_text,new_text,instance_num] Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string.
T [value] Returns the text referred to by value.
TEXT [value, format_text] Converts a value to text in a specific number format.
TRIM [text] Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.
UPPER [text] Converts text to uppercase.
Statistical, Mathematical/Numeric Functions
RANK [number,ref,order] Returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. (If you were to sort the list, the rank of the number would be its position.)
ABS [Number] Returns the absolute value of a number. The absolute value of a number is the number without its sign (+ or -)
AVERAGE [Number1, Number2,…] Returns the average (arithmetic mean) of the arguments.
CEILING [Number, Significance] Returns number rounded up, away from zero, to the nearest multiple of significance. For example, if you want to avoid using pennies in your prices and your product is priced at $4.42, use the formula =CEILING(4.42,0.05) to round prices up to the nearest nickel.
COUNT [Val1, Val2, Val3, …] Counts the number of cells that contain numbers and also numbers within the list of arguments. Use COUNT to get the number of entries in a number field that's in a range or array of numbers.
COUNTA [Val1, Val2, Val3, …] Counts the number of cells that are not empty and the values within the list of arguments. Use COUNTA to count the number of cells that contain data in a range or array.
COUNTBLANK [Range] Counts empty cells in a specified range of cells.
COUNTIF [Range, Criteria] Counts the number of cells within a range that meet the given criteria.
FLOOR [number, significance] Rounds number down, toward zero, to the nearest multiple of significance.
INT [number] Rounds a number down to the nearest integer.
LARGE [array, k] Returns the k-th largest value in a data set. You can use this function to select a value based on its relative standing. For example, you can use LARGE to return the highest, runner-up, or third-place score.
MAX [number1,number2,…] Returns the largest value in a set of values.
MIN [number1,number2,…] Returns the smallest number in a set of values.
MOD [number, divisor] Returns the remainder after number is divided by divisor. The result has the same sign as divisor.
POWER [number, power] Returns the result of a number raised to a power.
PRODUCT [number1,number2,…] Multiplies all the numbers given as arguments and returns the product.
QUOTIENT [numerator, denominator] Returns the integer portion of a division. Use this function when you want to discard the remainder of a division.
If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.
RAND [RAND( )] Returns an evenly distributed random number greater than or equal to 0 and less than 1. A new random number is returned every time the worksheet is calculated
ROUND [number, num_digits] Rounds a number to a specified number of digits.
ROUNDDOWN [number, num_digits] Rounds a number down, toward zero.
ROUNDUP [number, num_digits] Rounds a number up, away from 0 (zero).
SUBTOTAL [function_num, ref1, ref2, …] Returns a subtotal in a list or database. It is generally easier to create a list with subtotals using the Subtotals command (Data menu). Once the subtotal list is created, you can modify it by editing the SUBTOTAL function.
Function_Num Functions
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP
SUMIF [range, criteria, sum_range] Adds the cells specified by a given criteria.
SUMPRODUCT [array1, array2, array3, …] Multiplies corresponding components in the given arrays, and returns the sum of those products.
TRUNC [number, num_digits] Truncates a number to an integer by removing the fractional part of the number.
Lookup/Reference Functions
ADDRESS [Row_num, Col_num, Abs_num] Creates a cell address as text, given specified row and column numbers.
COLUMN [Reference] Returns the column number of the given reference.
COLUMNS [Array (Reference)] Returns the number of columns in an array or reference
HLOOKUP [lookup_value, table_array, row_index_num, range_lookup] 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. Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find.
The H in HLOOKUP stands for "Horizontal."
INDEX [array, row_num, column_num] Returns the reference of the cell at the intersection of a particular row and column. If the reference is made up of nonadjacent selections, you can pick the selection to look in.
The INDEX function has two syntax forms: array and reference. The array form always returns a value or an array of values; the reference form always returns a reference.
INDEX [reference, row_num, column_num, area_num] Returns the reference of the cell at the intersection of a particular row and column. If the reference is made up of nonadjacent selections, you can pick the selection to look in.
INDIRECT [ref_text, a1] Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.
LOOKUP [lookup_value, lookup_vector, result_vector] Returns a value either from a one-row or one-column range or from an array. The LOOKUP function has two syntax forms: vector and array. The vector form of LOOKUP looks in a one-row or one-column range (known as a vector) for a value and returns a value from the same position in a second one-row or one-column range. The array form of LOOKUP looks in the first row or column of an array for the specified value and returns a value from the same position in the last row or column of the array.
LOOKUP [lookup_value, array] Returns a value either from a one-row or one-column range or from an array. The LOOKUP function has two syntax forms: vector and array. The vector form of LOOKUP looks in a one-row or one-column range (known as a vector) for a value and returns a value from the same position in a second one-row or one-column range. The array form of LOOKUP looks in the first row or column of an array for the specified value and returns a value from the same position in the last row or column of the array.
MATCH [lookup_value, lookup_array, match_type] Returns the relative position of an item in an array that matches a specified value in a specified order. Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself.
OFFSET [reference, rows, cols, height, width] Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.
ROW [reference] Returns the row number of a reference.
ROWS [array] Returns the number of rows in a reference or array.
TRANSPOSE [array] Returns a vertical range of cells as a horizontal range, or vice versa. TRANSPOSE must be entered as an array formula in a range that has the same number of rows and columns, respectively, as an array has columns and rows. Use TRANSPOSE to shift the vertical and horizontal orientation of an array on a worksheet.
VLOOKUP [lookup_value, table_array, col_index_num, range_lookup] 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. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data you want to find.
The V in VLOOKUP stands for "Vertical."
Logical Functions
AND [Logical1, Logical2,….] Returns TRUE if all its arguments are TRUE; returns FALSE if one or more argument is FALSE.
IF [logical_test, value_if_true, value_if_false] Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.
Use IF to conduct conditional tests on values and formulas.
OR [logical1, logical2, …] Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.
Information Functions
CELL [Information Type; eg, "Address", "Filename", "Row", etc] Returns information about the formatting, location, or contents of the upper-left cell in a reference.
ISERROR [value] Checks if the value is any error value
Date and Time Functions
DATE [Year, Month, Day] Returns the sequential serial number that represents a particular date. If the cell format was General before the function was entered, the result is formatted as a date.
DATEDIF [Start Date, End Date, "Y"/"YM"/"MD"] You can calculate a persons age based on their birthday and todays date.
DATEVALUE [Date_Text] Returns the serial number of the date represented by date_text. Use DATEVALUE to convert a date represented by text to a serial number.
DAY [Serial_Number] Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31.
DAYS360 [Start Date, End Date] Returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations. Use this function to help compute payments if your accounting system is based on twelve 30-day months.
EDATE [start_date, months] Returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue.
EOMONTH [start_date, months] Returns the serial number for the last day of the month that is the indicated number of months before or after start_date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month.
HOUR [serial_number] Returns the hour of a time value. The hour is given as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.).
MINUTE [Serial_Number] Returns the minutes of a time value. The minute is given as an integer, ranging from 0 to 59.
MONTH [Serial_Number] Returns the month of a date represented by a serial number. The month is given as an integer, ranging from 1 (January) to 12 (December).
NETWORKDAYS [start_date, end_date, holidays] Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.
If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.
NOW [Now()] Returns the serial number of the current date and time. If the cell format was General before the function was entered, the result is formatted as a date.
TIME [hour, minute, second] Returns the decimal number for a particular time. If the cell format was General before the function was entered, the result is formatted as a date.
The decimal number returned by TIME is a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).
TIMEVALUE [time_text] Returns the decimal number of the time represented by a text string. The decimal number is a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).
TODAY [TODAY( )] Returns the serial number of the current date. The serial number is the date-time code used by Microsoft Excel for date and time calculations. If the cell format was General before the function was entered, the result is formatted as a date.
WEEKDAY [serial_number, return_type] Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.
WEEKNUM [serial_num, return_type] Returns a number that indicates where the week falls numerically within a year.
If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.
WORKDAY [start_date, days, holidays] Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed.
If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.
YEAR [serial_number] Returns the year corresponding to a date. The year is returned as an integer in the range 1900-9999.
Database Functions
DAVERAGE [Datebase, Field, Criteria] Averages the values in a column of a list or database that match conditions you specify.
DCOUNT [Datebase, Field, Criteria] Counts the cells that contain numbers in a column of a list or database that match conditions you specify. The field argument is optional. If field is omitted, DCOUNT counts all records in the database that match the criteria.
DCOUNTA [Datebase, Field, Criteria] Counts the nonblank cells in a column of a list or database that match conditions you specify. The field argument is optional. If field is omitted, DCOUNTA counts all records in the database that match the criteria.
DGET [Datebase, Field, Criteria] Extracts a single value from a column of a list or database that matches conditions you specify.
DMAX [Datebase, Field, Criteria] Returns the largest number in a column of a list or database that matches conditions you specify.
DMIN [Datebase, Field, Criteria] Returns the smallest number in a column of a list or database that matches conditions you specify.
DSUM [Datebase, Field, Criteria] Adds the numbers in a column of a list or database that match conditions you specify.
Friday, January 22, 2010
Excel - An Introduction to History
Introduction:
You will probably wonder what this section is doing in an Excel Tutorial. Normally people don’t bother about the History and Geography of most of the things. But wouldn’t it be nice to know a little about the definition of Excel - one of the most popular applications in Computer history, and the people who burnt lights at night to gift it to us? So here I go, very short indeed.
Excel (Microsoft Office Excel) is a spreadsheet-application that allows easy analysis and manipulation of data using tables and formulas. Period.
Advantages:
Like all other applications, Excel does have its share of advantages and disadvantages. However, if I start jotting them down, I am afraid the whole book needs to be dedicated to this section. However, just to name a few - it’s easy to use, portable (you can create something in excel and send it in a mail), affordable, and one of the best parts is it has amazing capabilities in terms of presentation of your data. If you are as much (or even more) in love with Excel as I am, and if you are reading this right now (which means you have a PC at your disposal), the chances are fairly high that you have access to internet. Browse through the cyberworld and you will find hundreds of advantages and disadvantages of this amazing application. Period.
History:
Up till 1978, a spreadsheet comprised of a ledger paper, pencil (pen in some cases, or both) and eraser. Now, imagine you realised at the end of the year that there’s an error in one of the entries in the starting of the year. You will probably end up an entire day erasing and recalculating to get to the correct figures.
However, in 1979, two guys (Bob Frankston and Dan Bricklin) came to the rescue and invented VisiCalc (Visual Calculator) for Apple II computers. This was the first Spreadsheet program. Then came Lotus 1-2-3 for DOS. Some of the other spreadsheets who claimed their existence during those times were Quattro, Multiplan, and others; but Lotus 1-2-3 was clearly the leader in the world of Electronic Spreadsheets.
Come early 90s and Microsoft was ready with their improved Spreadsheet program named Excel. The initial Excel wasn’t as good as Lotus 1-2-3, but they showed rapid improvement each year. From mid 90 onwards Microsoft Excel started leading the Spreadsheet world and today it’s the undisputed leader. Period.
Like all other applications, Excel does have its share of advantages and disadvantages. However, if I start jotting them down, I am afraid the whole book needs to be dedicated to this section. However, just to name a few - it’s easy to use, portable (you can create something in excel and send it in a mail), affordable, and one of the best parts is it has amazing capabilities in terms of presentation of your data. If you are as much (or even more) in love with Excel as I am, and if you are reading this right now (which means you have a PC at your disposal), the chances are fairly high that you have access to internet. Browse through the cyberworld and you will find hundreds of advantages and disadvantages of this amazing application. Period.
History:
Up till 1978, a spreadsheet comprised of a ledger paper, pencil (pen in some cases, or both) and eraser. Now, imagine you realised at the end of the year that there’s an error in one of the entries in the starting of the year. You will probably end up an entire day erasing and recalculating to get to the correct figures.
However, in 1979, two guys (Bob Frankston and Dan Bricklin) came to the rescue and invented VisiCalc (Visual Calculator) for Apple II computers. This was the first Spreadsheet program. Then came Lotus 1-2-3 for DOS. Some of the other spreadsheets who claimed their existence during those times were Quattro, Multiplan, and others; but Lotus 1-2-3 was clearly the leader in the world of Electronic Spreadsheets.
Come early 90s and Microsoft was ready with their improved Spreadsheet program named Excel. The initial Excel wasn’t as good as Lotus 1-2-3, but they showed rapid improvement each year. From mid 90 onwards Microsoft Excel started leading the Spreadsheet world and today it’s the undisputed leader. Period.
Subscribe to:
Posts (Atom)
