Home To Learn Microsoft Excel,Free Tutorial

Formula Help "ERRORS"

2011-10-20T14:15:00.000-07:00

Following Errors will be prompted when user enters wrong value, undetermined by Microsoft Excel

#DIV/0!

The formula attempts to divide by zero (an operation net allowed on this planet).

This also occurs when the formula attempts to divide by an empty cell

#NAME?

The formula uses a name that Excel doesn't recognize. This can happen if you

delete a name used in the formula or if you misspell a function.

#N/A

The formula refers (directly or indirectly) tea cell that uses the NA function to

signal unavailable data. This error also occurs if a lookup function does not find a
match.

#NULL!

The formula uses an intersection of two ranges that don't intersect

#NUM!

A problem occurs with a value; for example, you specify a negative number where

a positive number is expected.

#REF!

The formula refers to an invalid cell. This happens if the cell has been deleted from

the worksheet.

#VALUE!

The formula includes an argument or operand of the wrong type. An operand

refers to a value or cell reference that a formula uses to calculate a result.

The WORKDAY ( ) Function

2011-10-02T21:58:00.000-07:00

Adding days to or subtracting days from a date is straightforward, but the basic calculation includes all days: workdays, weekends, and holidays, rn many cases, you might need to ignore weekends and holJdays and return a date that is specified number of workdays from some original date.

You can do this by using the Analysis ToolPak's WORKDAY ( ) function, which returns a date

that is a specified number of working days from some starting dates.

Syntax:
WORKDAY( startdate, days[, holidays])
Start Date:
The original date (or a string representation of the date)
Days:
The number of workdays before or after start date. Use a positive number to return a later date; use a negative number to return an earlier date. Non-integer values are truncated (that is, the decimal part is ignored)

Holidays:

A list of dates to exclude from the calculation. This can be range of dates or an array constant (that is, a series of date serial numbers or date strings, separated by commas and surrounded by braces{ })

Example 1:
The following expression returns a date is 30 workdays from today: =WORKDAY(TODAY(),30)

Example2:

Note
here's another expression that returns the date that is 30 workdays from December 1, 2011, excluding December 25, 2011, and January 1,2012: =WORKDAY("11/2/2011",30,{"25/12/2011","1/1/2012"})

If you don't find this function in the list, lead the Analysis ToolPak add-in.

The WEEKDAY ( ) Function

2011-09-19T21:38:00.000-07:00

The WEEKDAY ( ) function returns a number that corresponds to the day of the week upon which a specified date falls.

Syntax:

WEEKDAY(serial‑number [, return‑type] )

Serial‑Number:

The date (or a string representation of the date) you want to work with

Return Type:

An integer that determines how the value returned by WEEKDAY ( )

corresponds to the days of the week:

1. The return values are 1 (Sunday) through 7 (Saturday); this is the default.

2. The return values are 1 (Monday) through 7 (Sunday).

3.The return values are 0 (Monday) through 6 (Sunday).

Example:

If you want to see the day of Sep. 20, 2011, type this formula in any
cell =WEEKDAY("9/20/2011")

Returns:                            4

The DAY ( ) Function

2011-09-19T10:04:00.000-07:00

The DAY ( ) functiorl returns a number between 1 and 31 that corresponds to the day component of a specified date.

Syntax:

DAY(serial‑number)

Serial‑number:

The date (or a string representation of the date) you want to work with.

Example:

If today is Sep. 19,2011, type this formula Jn any cell
=DAY("9/19/2011")

Returns: 19

The MONTH ( ) Function

2011-09-17T21:32:00.000-07:00

The MONTH ( ) function returns a number between I and 12 that corresponds to the month component of a specified date.

Syntax: MONTH(serial_number)

Serial‑number: The date (or a string representation of the date) you want to
work with.

Example: if today is Sep.18,2011,type this formula in any cell

=MONTH("SEPTEMBER 18,2011")

Returns: 9

The YEAR ( ) Function

2011-09-17T09:59:00.000-07:00

The YEAR ( ) function returns a four-digit number that corresponds to the year component of a specified date.

Syntax:

YEAR(serial_number)

Serialnumber:

The date (or a string representation of the date) you want to work with.

Example:

If today is sep.17, 2011, type this formula in any cell =YEAR(TODAY ( ) )

Returns: 2011

Date Functions

2011-09-14T22:05:00.000-07:00

The date and time functions enable you to convert dates and times to serial numbers and perform operations on those numbers. This capability is useful for such things as accounts receivable aging, project scheduling, time-management applications, and much more. This chapter introduces you to Excel's date and time functions and puts them through their paces with many practical examples.

Returning a Date

If you need a date for expression operand for a function argument, you can always enter it by hand if you have a specific date in mind. Much of the time, however, you need more flexibility, such as always entering the current date or building a date from day, month, and year components. Excel offers three functions that can help: TODAY( ), DATE( ), and DATE VALUE( ).

TODAY ( ): Returning the Current Date

When you need to use the current date in a formula, function, or expression, use the TODAY ( ) function, which doesn't take any arguments:

Syntax:                                TODAY ( )

This function returns the serial number (or current date) of the current date. For example, if today date is December 31, 2007, the TODAY ( ) function returns the following serial number:

39447.0

Note that TODAY ( ) is a dynamic function that doesn't always return the same value. Each time you edit the formula, enter another formula, recalculate the worksheet, or reopen the workbook, TODAY ( ) updates its value to return the current system date.

DATE ( ): Returning Any Date

A date consists of three components: the year, month, and day. It often happens that a worksheet generates one or more of these components, and you need some way of building a proper date out of them. You can do that by using Excel's DATE ( ) function:

Syntax:    DATE (year, month, day)

Year:    The year component of the date (a number between 1900 and 9999)

Month:    month component of the date

Day: The day component of the date

Type: =DATE(2005, 8, 14) in any cell

Returns: 14-8-2005

DATEVALUE ( ) Converting a String to a Date

If you have a date value in string form, you can convert it to a date serial number by using the DATE VALUE ( ) function:

Syntax: DATEVALUE("date_text")

Date‑Text: The string containing the date

Example:   The following expression returns the date serial number for the string August 23, 2007, Type
this formula =DATEVALUE("august 23, 2007")

Returns:                             39317

Returning Parts of a Date
The three components of a date - year, month, and day - can also be extracted individually from a given date. This might not seem all that interested at first, but actually many useful techniques arise out of working with a date's component pads. A date's components are extracted using Excel's YEAR (), MONTH (), and DAY
( ) functions.

Advanced Lookup Operations

2011-09-13T22:38:00.000-07:00

The basic lookup procedure - looking up a value in a column or row and then returning an offset value - will satisfy most of your needs. However, a few operations require a more sophisticated approach. The rest of this chapter examines these more advanced lookups, most of which make use of two more lookup functions:

I.    MATCH

2.   INDEX

The MA TCH ( ) and INDEX ( ) Functions

The MATCH ( ) function looks through a row or column of cells for a value. If MATCH ( ) finds a match, it returns the relative pesitien of the match in the row or coIumn.

Syntax:

MATCH ( lookup_value, lookup_array [, match_type] )

Lookup‑value:

The value you want to find. You can use a number, string, reference, or logical value

Lookup‑Array:

The row or column of cells you want to use for the lookup

Match‑Type:

How you want Excel to match the lookup‑value with the entries in the lookup‑array. You have three choices:

0   Finds the first value that exactly matches lookup value. The lookup‑array can be in any order

1     Finds the largest value that's less than or equal to lookup‑value (this is the default value). The lookup‑array must be in ascending order

-1   Finds the smallest value that is greater than or equal to lookup‑value. Thc lookup‑array must be in descending order.

Tips->

You can use the usual wildcard characters within the lookupvalue argument (provided that matchtype is 0 and lookup‑value is text). You can use the question mark (?) for single characters and the asterisk (*) for multiple characters.

Normally, you don't use the MATCH ( ) function by itself; you combine it with the INDEX ( ) function.

INDEX ( ) returns the value of a cell at the intersection of a row and column inside a reference.

Syntax:

INDEX (reference, row_num [, column_num] [, area_num])

Reference:                           A reference to one or more cell ranges

Row_Num:

The number of the row in reference from which to return a value. You
can omit row‑num if reference is a single row.

Column_Num:

The number of the column in reference from which to return a value. You
can omit column_num if reference is a single column

Area_Num:

If you entered more than one range for reference, area_num is the range, you want to use. The first range you entered is 1 (this is the default), the second is 2, and so on.

The idea is that you use MATCH ( ) to get row‑hum or column_num (depending on how your table is laid out), and then use INDEX ( ) to return the value you need.

Looking Up a Customer Name (Using INDEX and MATCH)

Example:

1 Create the Sheet given below.

2. Type this formula in cell B4.

=INDEX(D3:E15,MATCH(B2,D3:D15,0),2)

The MATCH ( ) function looks up the value in cell B2 in the range D3:D15. That value is then used as the row_num argument for the INDEX ( ) function. That value is 1 in the example, so the INDEX ( ) function reduces to this: =INDEX (D3:E15,1, 2).This returns the value in the first row and the second column of the range D3:E15.

HLOOKUP ( ) Function

2011-09-13T21:54:00.000-07:00

The HLOOKUP ( ) function is similar to VLOOKUP (), except that it searches for the lookup value in the first row of a table. (The "H' in HLOOKUP ( ) stands for HORIZONTAL). If successful, this function then looks down the specified number of rows and returns the value it finds there.

Syntax:

HLOOKUP(Iookup_value,table_array,row_index_num[,range‑lookup])

Lookup_Value:

This is the value you want to find in the first row of table_array. You can enter a number, string, or reference.

Table_Array:

This is the table to use for the lookup. You can use a range reference or a name.

Row_Index_Num:

If HLOOKUP( ) finds a match, row_index_num is the row number in thetable that contains the data you want returned (the first row - that is, the lookup row - is 1, the second row is 2, and so on)

Range‑Lookup:

This is a Boolean value that determines how Excel searches for
lookup_value in the first row:

TRUE_VLOOKUP ( ) searches for the first exact match for lookup value, if no exact match is found, the function looks for the largest value that is less than lookup_value (this is the default)

FALSE_VLOOKUP ( ).searches only for the first exact match for   lookup_value.

Looking Up Total Expenses

Example:

1. Create the sheet given below.

Figure shows an example. Cell D11 contains Month's name.

Figure shows an example. Cell D11 contains Month's name.

2. Type the formula given below in cell D12. =HLOOKUP(D11,C1:H9,FALSE)

This formula gives you the exact match lookup.

Finding Exact Match

2011-09-12T21:49:00.000-07:00

Finding Exact Matches

If you need to look up a customer account number, a part code, or an employee ID, you want to be sure that your formula matches the value exactly. You can perform exact-match lookups with VLOOKUP ( ) and HLOOKUP( ) by including the range_lookup argument with the value FALSE. The next couple of sections demonstrate this technique. A table of customer account numbers and names is a good example of a lookup table that contains discrete lookup values. In such a case, you want to use VLOOKUP ( ) or HLOOKUP ( ) to find an exact match for an account number you specify, and then return the corresponding account name. example below shows a simple date-entry screen that automatically adds a customer name after the user enters the account number in cell B2.

Example:

1. Create the sheet given below and use VLOOKUP ( ) function to find out Account Name.

2. Type the given formula in cell B4.

=VLOOKUP(B2,D3:E9,2,FALSE)

The value in B2 is looked up in column D, and because the rangelookup argument is set to FALSE, VLOOKUP ( ) searches for an exact match. If it finds one, it returns the text from column E.

Multiple VLOOKUP Tables

2011-09-12T21:39:00.000-07:00

You might find that you have multiple lookup tables in your model, for example, you might have multiple tax rate tables that apply to different types of taxpayers (single versus married, for example). You can use the IF ( ) function to choose which lookup table is used in a lookup forhlula. Here is the general formula:

=VLOOKUP(Iookup_value, IF(Condition, Table1, Table2),Col_lndex Num)

IF condition returns TRUE, a reference to table1 is returned, and that table is used as the lookup table; otherwise, table2 is used.

Tax Rate Schedule

2011-09-12T21:35:00.000-07:00

Returning a Tax Rate with a Range Lookup

Tax rates are perfect candidates for a range lookup because a given rate always applies to any income that is greater than some minimum amount and less than or equal to some maximum amount. For example, a rate of 25% might be applied to annual incomes over $28,400 and less than or equal to $68,800.

Example:

1. Create the sheet given below and use the VLOOKUP ( ) function to calculate tax rate.

2. Type the given formula in cell 817 and see the result.

=VLOOKUP(BI6,C9:F14,4)

The lookup table is C9:F14, and the lookup value is cell B16, which contains the annual income. VLOOKUP ( ) finds in column C the largest income that is less than or equal to the value in B16, which is 30,000. In this case, the matching value is 28,400 in cell 11. VLOOKUP ( ) then looks in column 4 to get the marginal rate in row F, which, in this case, is 25%.

The VLOOKUP ( ) Function

2011-09-12T09:35:00.000-07:00

The VLOOKUP () Function:

The VLOOKUP ( ) function works by looking in the first column of a table for the value you specify. (The "V" in VLOOKUP ( ) stands for VERTICAL.) It then looks across the appropriate number of columns (which you specify) and returns whatever value it funds there.

SYNTAX: VLOOKUP(lookup_value,table_array,col_index_num[,range_lookup])

Lookup‑Value: This is the value you want to find in the first column of table‑array. You can enter a number, string, or reference.

Table‑Array: This is the table to use for the Lookup. You can use a range reference or a name.

Col Index‑Num: If VLOOKUP( ) finds a match, col‑index num is the column number in the table that contains the data you war)t returned (the first column -that is, the lookup column - is 1, the second column is 2, and so on)

Range‑Lookup:This is a Boolean value that determines how Excel searches for lookup‑value in the first column:

TRUE - VLOOKUP ( ) searches for the first exact match for lookup‑value. If no exact match is found, the function looks for the largest value that is less than lookup‑value (this is the default)

FALSE - VLOOKUP ( ) searches only for the first exact match for lookup‑value

Returning a Customer Discount Rate with a Range Lookup

The most common use for VLOOKUP ( ) is to look for a match that falls within a range of values. This section will guide you through a few of examples of this range-lookup technique.

In business-to-business transactions, the cost of an item is often calculated as a percentage of the retail price. For example, a publisher might sell books to a bookstore at half the suggested list price. The percentage that the seller takes off the list price for the buyer is called the discount. Often, the size of the discount is a function of the number of units ordered. For example, ordering 1 - 3 items might result in a 20% discount, orderi0g 4 - 24 items might result in a 40% discount, and so on.

Lookup Function

2011-09-12T09:18:00.000-07:00

Determining the Month of the Fiscal Year

For many businesses, the fiscal year does not coincide with the calendar year. For example, the fiscal year might run from April 1 to March 31. In this case, month 1 of the fiscal year is April, month 2 is May, and so on. It's often handy to be able to determine the fiscal month given the calendar month.

To see how you'd set this up, first consider the following table, which compares the calendar month and the fiscal month for a fiscal year beginning April 1:

               Month                       Calendar Month                                    Fiscal Month
January                                                1                                                        10
February                                              2                                                        11
March 3                                                        12
April 4                                                          1
May 5                                                          2
June 6                                                          3
July 7                                                          4
August 8                                                          5
September                                           9                                                          6
October 10 7
November 11 8
December 12 9

You need to use the Calendar month as the lookup value, and the fiscal month as the data values. Here is the result:

=CHOOSE(Calendar Month,10,11,12,f,2,3,4,5,6,7,8,9)

Determining day

2011-09-12T09:15:00.000-07:00

What if you want to know the actual day (not the number) of the week? If you need only to display the day of the week, you can format the cell as dddd. If you need to use the day of the week as a string value in a formula, you need a way to convert the WEEKDAY( ) result into the appropriate string. Fortunately, the CHOOSE ( ) fdnction makes this process easy. For example, suppose that cell B5 contains a date 28/10/08. You can find the day of the week it represents with the following formLda (Type this formula in any cell):

=CHOOSE(WEEKDAY(B5),"SUN ","MON","TU E","WED","TH U","FRI","SAT")

I've used abbreviated day names to save space, but you're free to use any form of the daynames that suits your purposes.

The CHOOSE ( ) Function

2011-09-12T09:06:00.000-07:00

Lookup functions enable you to "look up" values from worksheet ranges. Excel allows you to perform both vertical and horizontal lookups. This chapter introduces you to number of functions that enable you to perform lookup operations in your worksheet models.

The CHOOSE ( ) Function

The simplest of the lookup functions is CHOOSE (), which enables you to select a value from a list. Specially, given an inter n, CHOOSE ( ) returns the nth item from the list.

Syntax : CHOOSE(hum, value1[, value2,...])

Num: Determines which of the values in the list is returned, if hum is 1, valuef is returned; if num is 2, value2 is returned (and so on). num must be an integer (or a formula or function that returns an integer) between I and 29.

Value1 Value2...: The list of up to 29 values from which CHOOSE (selects the return value. The values can be numbers, strings, references, names, formula, or functions.

Example: Type SURFACE MAIL in cell A I, AIR MAIL in cell B1, COURIER in cell C1, then type this formula --=CHOOSE(2,AI,B1,CI)

Returns:

The num argument is 2, so CHOOSE ( ) returns the second value in the list, which is the string value AIR MAIL

The INFO ( ) Function

2011-09-12T09:00:00.000-07:00

It gives you information about the current operating environment.

Syntax:                               INFO(type text)

Type‑Text:                         A string that specified the type of information you want.

Table below shows the possible values for the info‑type argument.

"directory"    Path of the current directory or folder

"memavail" Amount of memory available, in bytes.

"memused"    Amount of memory being used for data.

"numfile"   Number of active worksheets in the open workbooks.

"osversion"    Current operating system version, as text.

"recalc" Currant recalculation mode; returns "Automatic" or "Manual"

"totmem" Total memory available, including memory already in. use, in bytes.

The ERROR TYPE ( ) Function

2011-09-12T08:53:00.000-07:00

The ERROR.TYPE ( ) function returns a value that corresponds to a specific Excel error value:

Syntax: ERROR.TYPE(error_val)
Error_Val:                             A reference to a cell containing a formula that you want to check for the error value. Here are the possible return values:

error_value Returned

#NULL! 1

#DIV/0! 2

#VALUE!    3

#REF! 4

#NAME?    5

#NUM! 6

All other values #   N/A

Example:                             Type the wrong SUM formula in cell Al,
=SM(A1), it returns #NAME?
Now, type =ERROR. TYPE(A 1) in cell A2

Returns: 5 (5 = #NAME? as you can see in the above table.)

The Cell Function

2011-09-12T08:39:00.000-07:00

In Excel, the Infofunction returns information about the operating environment.Excel's information functions return data concerning cells, worksheets, and formula results.

The CELL ( ) Function

Cell ( ) function returns information about a particular cell.

Syntax: CELL (infotype, [reference])

Info‑Type:                            A string that specifies the type of information you want

Reference:                          The cell you want to use (the default is the cell that contains the CELL ( )function). If reference is a range, CELL ( ) applies to the cell in the upper-
left corner of the range.

The Table lists the various possibilities for the InfoType argument.

Value Explanation

"directory" Path of the current directory.

"memavail" Amount of memory available (bytes).

"memused" Amount of memory being used (bytes).

"numfile" Number of active worksheets.

"origin" The cell that is in the top, left-most cell visible in the current Excel spreadsheet

"osversion" Operating system version.

"recalc"    Returns the recalculation mode - either Automatic or Manual.

"release" Version of Excel that you are running.

"system" Name of the operating environment

"totmem"    Total memory.

there is also some more list of various possibilities for the info_type arguments

INFO TYPE VALUE	WHAT CELL ( ) RETURNS
Address	The absolute address,as text, of the reference cell
Col	The column number of reference.
Color	1 if reference has a custom cell format that displays negative values in a color; returns 0 ether wise.
Contents	The contents of reference.
Filertame	The full path and filename of the file that contains reference, as text. Returns the null string (" ") if the workbook that contains reference hasn't been saved for the first time.
Format	A string that corresponds to the built-in Excel numeric format applied to reference.
Parentheses	1 if reference has a custom cell format that uses parentheses for positive or all values; returns O otherwise.
Prefix	A character that represents the text alignment used by reference.
Protect	O if reference isn't locket; 1 otherwise.
Row	The row number of reference.
Type	A letter that represents the type of data in the reference.
Width	The column width of reference.

The TEXT ( ) Function

2011-09-08T11:40:00.000-07:00

DOLLAR ( ) and FIXED ( ) are useful functions in specific circumstances. However, if you want
total control over the way a number is formatted within a string, or if you want to include dates
and times within strings, the powerful TEXT ( ) function is what you need.

Syntax: TEXT(number, format)
Number: The number, date, or time you want to convert
Format: The numeric or date/time format you want to apply to number

Example-l: For example, type 45, 67, 88, 55, 88, 55, 99, 44, 33, 66 through
A1:10,

calculate AVERAGE in cell A11, by using
=AVERAGE(A1:1Result=64.
Now, type the TEXT function =TEXT(AVERAGE(AI:AIO),"#,0.O0O0")
in ceil A12 and see the result

     Returns:                            64.0000
Example-2:                       Type this function in any cell. =TEXT(Now(),"mm/dd/yy hh:mm")
Returns:                            Current Date and Time with the format you specify

The DOLLAR ( ) Function

2011-09-08T11:37:00.000-07:00

The DOLLAR ( ) function converts a numeric value into a string that uses the Currency format.

Syntax: DOLLAR (number [ decimals])

Number: The number you want to convert
Decimals: The number of decimals to display (the default is 2)

Example: Type 50 in cell A1 and use =DOLLAR(A1,4)in cellA2

Returns: $5,000.0000

The PROPER ( ) Function

2011-09-08T11:36:00.000-07:00

The PROPER ( )function converts a specified string to proper case, which means the first letter
of each word appears in uppercase and the rest of the letters appear in lowercase.

Syntax: PROPER (Text)

Text: The string you want to convert to proper case
Example: Type GOOGLE or nadeem in ceil A1 and use =PROPER(A1) in cell A2
Returns: Google

The CHAR ( ) Function

2011-09-08T11:33:00.001-07:00

The CHAR ( ) function returns the character that corresponds to the ANSI code given by a number.

Syntax: CHAR (number)

Number: Must be a number between 1 and 255

Example: Type in any cell: =CHAR(169)

Converting Text

2011-09-08T11:33:00.000-07:00

Excel's specialty is number crunching, so it often seems to give short shrift to strings, particularly when it comes to displaying strings in the worksheet. For example, concatenating a numeric value into a string results in the number being displayed without any formatting, even if the original cell, had a numeric format applied to it.

Similarly, strings imported from a database or text file can have the wrong case or ne formatting. However, as you'll see over the next few sections, Excel offers a number of worksheet functions that enable you to convert strings to a more suitable text format, or to convert between text and numeric values.

A1 versus RtC1 Notation

2011-09-08T11:31:00.000-07:00

Normally, Excel uses A1 notation. Each cell address consists of a column letter and a row number. However, Excel also supports R1C1 notation. In this system, cell Al is referred to as cell R1C1, cell A2 as R2C1, and so on.

To change to R1C1 notation, choose File -~ Excel Options to open the Excel Options dialog box, click the Formulas tab, and place a check mark next to the R1Cl Reference Style option. Now, notice that the column letters all change to numbers. And all the cell and range references in your formulas also adjust.

Look at the following examples of formulas using standard notation and R1C1 notation. The formula is assumed to be in cell B1 (also known as R1C2).

Standard RlCl

=A1+1 =RC[-1]+I

=$A$1+1                     =RLC1+1
=$A1+1                       =RCI+I
=A$1+1                       =[R1C[-1]+1