When you're working in Manual Calculation mode(Tools->Option->Calculation), Excel displays Calculate in the status bar when you have any uncalculated formulas. You can press the following shortcut keys to recalculate the formulas:
§ F9 calculates the formulas in all open workbooks.
§ Shift+F9 calculates only the formulas in the active worksheet. Other worksheets in the same workbook won't be calculated.
§ Ctrl+Alt+F9 forces a recalculation of everything. Use it if Excel (for some reason) doesn't seem to be calculating correctly, or if you want to force a recalculation of formulas that use custom functions created with Visual Basic for Applications (VBA).
********* SS2 Cells and Range References *********
Most formulas reference one or more cells. This reference can be made by using the cell's or range's address or name (if it has one). Cell references come in four styles:
§ Relative: The reference is fully relative. When the formula is copied, the cell reference adjusts to its new location. Example: A1.
§ Absolute: The reference is fully absolute. When the formula is copied, the cell reference does not change. Example: $A$1.
§ Row Absolute: The reference is partially absolute. When the formula is copied, the column part adjusts, but the row part does not change. Example: A$1.
§ Column Absolute: The reference is partially absolute. When the formula is copied, the row part adjusts, but the column part does not change. Example: $A1.
By default, all cell and range references are relative. To change a reference, you must manually add the dollar signs. Or, when editing a cell in the formula bar, move the cursor to a cell address and press F4 repeatedly to cycle through all four types of cell referencing.
********* SS3 A1 / R1C1 notation *********
On default A1 notation is used. You can change by: Tool->Option->General->...
********* SS4 Referencing Other Sheets or Workbooks *********
References to cells and ranges need not be in the same sheet as the formula. To refer to a cell in a different worksheet, precede the cell reference with the sheet name followed by an exclamation point. Here's an example of a formula that uses a cell reference in a different worksheet:
=Sheet2!A1+1
You can also create link formulas that refer to a cell in a different workbook. To do so, precede the cell reference with the workbook name (in square brackets), the worksheet name, and an exclamation point. Here's an example:
=[Budget.xls]Sheet1!A1+1
If the workbook name in the reference includes one or more spaces, you must enclose it (and the sheet name) in single quotation marks. For example:
='[Budget For 2003.xls]Sheet1'!A1+A1
If the linked workbook is closed, you must add the complete path to the workbook reference. Here's an example:
='C:\MSOffice\Excel\[Budget For 2003.xls]Sheet1'!A1+A1
Note: You can also using Using Links to Recover Data in a Corrupt File.
4. Using Names
One of the most useful features in Excel is its ability to provide meaningful names for various items.
For example, you can name cells, ranges, rows, columns, charts, and other objects. An advantage
unique to Excel is that you can name values or formulas that don't even appear in cells in your worksheet .
Array Formulas
An array is simply a collection of cells or values that is operated on as a group. An array formula is a special type of formula that works with arrays. An array formula can produce a single result, or it can produce multiple results — with each result displayed in a separate cell (because Excel can fit only one value in a cell).
For example, when you multiply a 1 x 5 array by another 1 x 5 array, the result is a third 1 x 5 array. In other words, the result of this kind of operation occupies five cells; each element in the first array is multiplied by each corresponding element in the second array to create five new values, each getting its own cell. The array formula that follows multiplies the values in A1:A5 by the corresponding values in B1:B5. This array formula is entered into five cells simultaneously:
=A1:A5*B1:B5
|
|
Note
|
You enter an array formula by pressing Ctrl+Shift+Enter.To remind you that a formula is an array formula, Excel surrounds it with curly brackets ({ }) in the formula bar. Don't enter the brackets yourself.
|
Using the COUNTIF or SUMIF Function
§ COUNTIF takes two arguments:
o The range that holds the data to be counted
o The criteria used to determine whether the cell is included in the count
§ SUMIF takes three arguments:
o The range to be evaluated
o The criteria used to determine whether the cell is included in the count
o The range that holds the data to be summed
Table 3-3 demonstrates a variety of uses for the COUNTIF function. The formulas assume that you have a
range named data. (You'll need to substitute the actual range name or address in these formulas.) Also,
be aware that the second argument for the COUNTIF function can be a reference to a cell that contains
the search criteria.
|
Table 3-3: Examples of Common Uses for the Countif Function
Open table as spreadsheet
|
|
Formula
|
Return Value
|
|
=COUNTIF(data,12)
|
The number of cells that contain the value 12
|
|
=COUNTIF(data,1)+COUNTIF(data,12)
|
The number of cells that contain 1 or 12
|
|
=COUNTIF(data,"<0")
|
The number of cells that contain a negative number
|
|
=COUNTIF(data,"<>0")
|
The number of nonzero values
|
|
=COUNTIF(data,">=1")-COUNTIF(data,">10")
|
The number of cells that contain a value between 1 and 10
|
|
=COUNTIF(data," yes")
|
The number of cells that contain the word yes (not case-sensitive)
|
|
=COUNTIF(data,"*")
|
The number of cells that contain any text
|
|
=COUNTIF(data,"*s*")
|
The number of cells that contain the letter s (not case-sensitive)
|
|
=COUNTIF(data,"???")
|
The number of three-letter words
|
Using Array Formulas to Count and Sum
Don't forget: When you enter an array formula, press
Ctrl+Shift+Enter.
To count the number of numerical values (skipping text and blanks), use this array formula:
=SUM(IF(ISNUMBER(data),1,0))
To count the number of cells that contain an error value, use this array formula:
=SUM(IF(ISERR(data),1,0))
To count the number of unique numeric values (skipping text, blanks not allowed), use this array formula:
=SUM(IF(FREQUENCY(data,data)>0,1,0))
Table 3-4 shows a number of array formula examples based on the worksheet shown in Figure 3-7.
|
Table 3-4: Complex Array Formulas with the Sum Function
Open table as spreadsheet
|
|
Array Formula
|
Returns
|
|
=SUM((A2:A10=" Jan")*(B2:B10=" North") *C2:C10)
|
Sum of Sales where Month=" Jan" AND Region=" North"
|
|
=SUM((A2:A10=" Jan")*(B2:B10<>" North") *C2:C10)
|
Sum of Sales where Month=" Jan" AND Region<>" North"
|
|
=SUM((A2:A10=" Jan")*(B2:B10=" North"))
|
Count of Sales where Month=" Jan" AND Region=" North"
|
|
=SUM((A2:A10=" Jan")*((B2:B10=" North")+ (B2:B10=" South")))
|
Count of Sales where Region=" North" or "South" and Month=" Jan"
|
|
=SUM((A2:A10=" Jan")*(C2:C10>=200) *(C2:C10))
|
Sum of Sales where Month=" Jan" and Sales>=200
|
|
=SUM((C2:C10>=300)*(C2:C10<=400) *(C2:C10))
|
Sum of Sales between 300 and 400
|
|
=SUM((C2:C10>=300)*(C2:C10<=400))
|
Count of Sales between 300 and 400
|
