Welcome Guys

review http://excel-mania99.blogspot.com on alexa.com

Range Name Syntax

Name syntax rules:

The Name string must begin with a text character, not a number, and consists of adjacent characters.

Two words can be joined with an underscore (_). For example, to enter the Name "Excel Book", you should type Excel_Book.

You cannot use a Name that could otherwise be used as a cell reference. For example, A1 or IS2002.

Notes:

There is no limit on the number of Names you can define.

Be sure to define unique Names for a specific workbook. Defining Names that resemble Names in other sheets will only complicate your work.

F1 - Get The Most Out Of Excel Formulas and Functions0033

5:48 AM | 0 comments | Read More

Deleting a Range Name

Why it is highly recommended to delete unnecessary range Names:

Large numbers of range Names makes it more difficult to locate a specific Name.

Range Names create references and unwanted links.

To find unnecessary/unwanted range Names:

1. Select a cell in a new sheet.

2. Press <F3> and click Paste List. A full list of range names and their

references is pasted into the new sheet; delete each unwanted Name.

To delete a range Name:

Press <Ctrl+F3>, select the Name, and then click Delete.

5:47 AM | 0 comments | Read More

Defining a Range Name

To define a range Name, use one of the following two techniques:

Type the text directly into the Name box

  1. Select cell A1.
  2. In the Name box, type the text, and then press <Enter>.

Define a Name using the Define Name dialog box

1. Select cell B1.

2. Press <Ctrl+F3>.

OR

From the Insert menu, select Name and then Define.

3. Type the text in the Names in workbook box, and then click OK.

5:47 AM | 0 comments | Read More

Array Formulas

Understanding Arrays

For those who do not have a background in programming or mathematics, the expression Array may not be familiar.

So what exactly is an Array?

For our purposes, an Array is simply a set of values which can be stored in a formula, a range of cells, or the computer’s memory.

The size of an Array can range from two to thousands of values.

Using Arrays in Formulas

There are several different types of Arrays used by Excel when working with formulas:

An Array stored in a Worksheet in a range of cells: For example, when the SUM function sums the values stored in range of cells, it is treating those values as an Array.

An Array stored in a formula:

Instead of entering cell addresses to enable a formula to operate on the values stored in those locations, you may enter an Array of values into the SUM function arguments: =SUM(1,2,3,4,5).

OR

Enter an Array enclosed in brackets into the formula argument. For example, use the MATCH function to return the position of the number 10 in an Array of values: =MATCH(10,{3,7,10,15,20}). The result = 3

Excel formulas create Arrays to store values:

Formulas such as SUMPRODUCT utilize computer memory to store values temporarily while calculating complicated math problems.

These values are stored in an Array.

Example:

To add the total sales amount of 3 items when the quantities sold are 10, 20, and 30 and the sale prices are $3, $4, and $5 respectively, the

SUMPRODUCT formula stores each multiplication product in an Array (Array size is 3) and then adds the three values from the Array.

The SUMPRODUCT formula: =SUMPRODUCT(A1:A3,B1:B3),

Result - total sales=$260.

Let Excel create an Array formula:

As explained in the previous section, many formulas create Arrays when they need to store values during calculations. However, an Excel user may create a formula that deliberately enforces the program to open an Array/Arrays to store values.

Example:

Use the SUM function to return total sales (see previous example).

The formula will now look like this: {=SUM(A1:A3*B1:B3)}, Result total sales=$260.

To apply an Array formula:

Enter the formula, select the cell, press <F2>, and then simultaneously press <Ctrl+Shift+Enter>.

5:42 AM | 2 comments | Read More

Printing Formula Syntax

To print the formula syntax for a range of cells:

  1. Display the formula syntax by pressing <Ctrl+‘> (the key to the left of  the number 1).
  2. Print the desired area.
5:38 AM | 0 comments | Read More

Adding a Comment to a Formula

To add a Comment to a formula:

  1. At the end of the formula, add a + (plus) sign.
  2. Type the letter N, and, in parentheses, type your Comment in quotation marks.

Example:

=CurrentAssets / CurrentLiabilities+ N("The formula returns Current Ratio")

F1 - Get The Most Out Of Excel Formulas and Functions0032

5:37 AM | 0 comments | Read More

Pasting Values

To paste the calculated value of a single formula into a cell (thus overwriting the formula):

Press <F2> to edit and then <F9> to calculate.

To paste the calculated value of a single formula into the cell below the cell containing the formula:

Press <Ctrl+Shift+">.

To paste values in a range of cells, use the Paste Special dialog box:

  1. Copy a range of cells containing formulas, press <Shift+F10> or right-click, and then select Paste Special from the shortcut menu.
  2. Select Values and click OK.

Use the Paste Values Icon:

Add the Paste Values icon from the Edit category in

the Customize dialog box.

F1 - Get The Most Out Of Excel Formulas and Functions0031

New in Excel 2002 and Excel 2003

The Paste icon has been expanded, enabling some options from the Paste Special dialog box to be quickly accessed.

F1 - Get The Most Out Of Excel Formulas and Functions0030

5:35 AM | 0 comments | Read More