Excel help for the rest of us
To rename the active worksheet use one of these two methods:
Method 1
1. On the format menu point to Sheet then click Rename. The sheet tab name will be highlighted (edit mode).
2. Type the new name, then click anywhere outside the tab name.
Method 2
Simply double-click the sheet tab name, to get into edit mode, and then type the new name.
The INDIRECT function returns a reference specified by a text string, evaluates that reference and displays its contents. The function is useful when you want a cell reference in a formula to be variable, without the need to change the formula every time.
Syntax
INDIRECT(ref_text,a1)
ref_text: a text that contains a cell reference.
a1: a logical value that specifies what type of reference is contained in the cell ref_text.
• If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference. E.g. C12
• If a1 is FALSE, ref_text is interpreted as an R1C1-style reference. E.g R12C3
Example 1:
Cell B1 contains the formula: =INDIRECT(A1)
Cell A1 contains the text: “C6″
Cell C6 contains the value: 135
The INDIRECT function will evaluate the reference as: = C6, which is equal to 135.
If you change the text in cell A1 to “C3″ then you will get 198 in B1.
Example 2:
The data shown above is a snapshot from a sheet named “Sheet2″ in a workbook.
Cell B1 contains the formula: =INDIRECT(A1&”!”&”C8″)
Cell A1 contains the text: “Sheet2″
Cell C8 contains the value: 1922
The INDIRECT function will evaluate the reference as: = Sheet2!C8, which is equal to 1922.
If you change the text in cell A1 to “Sheet1″ then the reference will be evaluated as: = Sheet1!C8 and you will get whatever value in Cell C8 of sheet1.
The Conditional sum wizard helps you create complex formulas to sum values in a column based on certain criteria. The wizard is part of Excel Add-ins. You must load it first before it can show up in the Tools menu. See then bottom of this post if you want to know how to load the wizard.
The following example will demonstrate how to use the conditional sum wizard:
Suppose we have data of total sales by month and product as shown below, and we want to sum the total sales for Product3 in the first quarter (months 1 to 3).
1. On the Tools menu click Conditional Sum. The dialogue for step1 will open.
2. Click on the text box then select your data range, including column headings.
3. Click next. The dialogue for step2 will open.
4. Specify your conditions by manipulating the three combo boxes for Column, Is and This value. Click Add condition every time.
5. When finished with your conditions click next. The dialogue for step 3 will open.
6. Specify whether you want to copy your generated formula only, or both formula and the values of your conditions.
7. Click next. The dialogue for step 4 will open.
8. Specify the cell(s) where you want your generated formula (and values of conditions if applicable) to be copied.
9. Click finish. The formula will be copied to the specified cell, and the result of the formula will be shown. Here is the formula in our example:
{=SUM(IF($A$2:$A$19<4,IF($B$2:$B$19=”Product3″,$C$2:$C$19,0),0))}
How to Load the Conditional Sum Wizard
The Conditional Sum Wizard is not loaded by default. To determine whether it has been loaded, on the Tools menu, look for the Wizard menu item. If it is not present, or if when you point to it, the item Conditional Sum is not present, you need to load the add-in.
To load the Conditional Sum Wizard, follow these steps:
1. On the Tools menu, click Add-ins.
2. In the Add-Ins available list, select Conditional Sum Wizard, and then click OK.
Applies to Excel 2003
The RANK function returns the rank of a number, i.e. its position within a list of numbers. It is based on the value of a number, relative to the other numbers in the list.
The list evaluated by the rank doesn’t have to be sorted, but to get the idea of how items are ranked imagine that the list is sorted, and then each item is given a rank depending on its position in the list.
Syntax
RANK(number,ref,order)
Number: is the number whose rank you want to find.
Ref: is an array of or a reference to a list of numbers. Non-numeric values in the array or the reference are ignored.
Order: is a number specifying how to rank number.
• If order is 0 (zero) or omitted, Excel ranks number as if the list is sorted in descending order.
• If order is any nonzero value, Excel ranks number as if the list is sorted in ascending order.
Note:
The RANK function gives duplicate numbers the same rank. This however will affect the ranks of subsequent numbers. For example, if we have two numbers both have the rank of 5, then no number will have a rank of 6. The next number in the list will have a rank of 7.
Examples:
1. Rank with order 0 (descending).
This example shows ranks in descending order (Order = zero). Here are the applicable formulas in each cell:
C2: =RANK(B2,$B$2:$B$6,0)
C3: =RANK(B3,$B$2:$B$6,0)
C4: =RANK(B4,$B$2:$B$6,0)
C5: =RANK(B5,$B$2:$B$6,0)
C6: =RANK(B6,$B$2:$B$6,0)
2. Duplicate ranks.
The second example shows duplicate ranks, also in descending order (Order = zero). Notice that students 2 and 3 both have a rank of 2, and therefore rank 3 is skipped. Here are the applicable formulas in each cell:
#NAME? error occurs when Excel doesn’t recognize text in a formula.
Possible causes and solutions
1. Referring to a defined name that does not exist. On the Insert menu, point to Name, and then click Define.
2. Using label in a formula but labels are not allowed in your settings. On the Tools menu, click Options, and then click the Calculation tab. Under Workbook options, select the Accept labels in formulas check box.
3. Misspelling a name of a function in a cell. To avoid misspelling use the function option in the Insert menu. Or the function icon in the toolbar.
4. Entering text in a formula without enclosing the text in double quotation marks. Make sure quotation marks are added and they are balanced from left and right.
5. Omitting a colon (:) in a range reference. E.g. COUNT(A1A10) should be COUNT(A1:A10)
6. Referencing another worksheet, but the worksheet name is not enclosed in single quotation marks. If you have a formula referring to another worksheet and the name of that worksheet has a space, then the name should be enclosed within quotation marks. E.g. this formula is correct: =’Monthly budget’!$A$1, but this one will give a “#NAME” error: =Monthly budget!$A$1
7. Using a function that is part of the Analysis Toolpak add-in, without the add-in being loaded. Load the Analysis Toolpak. How to Load the Analysis Toolpak?