About Formulas
Formulas, of course, are what make a spreadsheet a spreadsheet. If no formulas in spreadsheet, it means it just a static document — something like a word processor that has great support for tables could produce.
A formula entered into a cell can consist of any of the following elements:
· Operators such as + (for addition), – (for subtraction), / (for division) and * (for multiplication)
· Cell references (including named cells and ranges)
· Numbers or text strings
· Worksheet functions (such as SUM or AVERAGE)
After you enter a formula into a cell, the cell displays the result of the formula. The formula itself appears in the formula bar when the cell is activated.
Have you noticed that the formulas in your worksheet get calculated immediately? If you change a cell that a formula uses, the formula displays a new result. This is happens when the Excel Calculation mode is set to Automatic. This mode is the default mode; Excel uses the following rules when calculating your worksheet:
· When you make a change, enter or edit data or formulas. Excel immediately calculates those formulas that depend on the new or edited data.
· If it’s in the middle of a lengthy calculation, Excel temporarily suspends calculation when you need to perform other worksheet tasks; it resumes when you’re finished.
· Formulas are evaluated in a natural sequence. For Example, if a formula in cell D12 depends on the result of a formula in cell D11, cell D11 is calculated before D12.
Sometimes, you might want to control when Excel calculates formulas. Suppose, if you create a worksheet with thousands of complex formulas, calculation might slow things down. In such a case, you should set Excel’s calculation mode to Manual. When you’re working in Manual Calculation mode, Excel displays Calculate in the status bar when you have any uncalculated formulas. You can press the following shortcut keys to recalculate the formulas:
A. F9 calculates the formulas in all open workbooks.
B. Shift+F9 calculate the formulas in the active worksheet only. Other worksheets in the same workbook won’t be calculated.
C. Ctrl+Alt+F9 forces a recalculation of everything in all workbooks. 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).
D. Ctrl+Alt+Shift+F9 rechecks all dependent formulas and calculates all cells in all workbooks (including cells not marked as needing to be calculated).
Next we will talk about Cell and Range References
Cell and Range References
Most formulas refer to one or more cells. You can make cell references by using the cell’s or range’s address or name (if it has one). Cell references come in four styles:
A. Relative: The reference is fully relative. When the formula is copied, the cell reference adjusts to its new location. Example: A1.
B. Absolute: The reference is fully absolute. When the formula is copied, the cell reference doesn’t change. Example: $A$1.
C. Row Absolute: The reference is partially absolute. When the formula is copied, the column part adjusts, but the row part doesn’t change. Example: A$1.
D. Column Absolute: The reference is partially absolute. When the formula is copied, the row part adjusts, but the column part doesn’t change. Example: $A1.
By Default, all cell and range references are relative. To change a reference, you must manually add the dollar signs.
R1C1 notation
Each cell address consists of a column letter and a row number. However, Excel also supports R1C1 notation. In this system, cell A1 is referred to as cell R1C1, cell A2 as R2C1, and so on.
Below is the Comparison Table for Slandered and R1C1 notation
Standard = A1+1
R1C1 = RC[–1]+1
Standard = $A$1+1
R1C1 = R1C1+1
Standard = $A1+1
R1C1 = RC1+1
Standard = A$1+1
R1C1 = R1C[–1]+1
Standard = SUM(A1:A10)
R1C1 = SUM(RC[–1]:R[9]C[–1])
Standard = SUM($A$1:$A$10)
R1C1 = SUM(R1C1:R10C1)
Referencing other sheets or workbooks
We can also refer other sheet by a formula given below
=Sheet2! A1+1
To refer to a cell in a different worksheet, precede the cell reference with the sheet name followed by an exclamation point.
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. Below is an example for referring different workbook:
= [Data.xlsx] Sheet1! A1
If the linked workbook is closed, you must add the complete path to the workbook reference. Below is an example:
=’C:\My Document\Excel Files\[Data.xlsx]Sheet1’!A1
Referencing Data in a Table
You can create formulas that refer to cells within the table by using the column headers. In some cases, using column headers may make your formulas easier to understand. But the real advantage is that your formulas will continue to be valid if rows are added or removed from the table. For example, these are all valid formulas that use table references:
=Table1[[#Totals],[Income]]
=SUM(Table1[Income])
=Table1[[#Totals],[Income]]-Table1[[#Totals],[Expenses]]
=SUM(Table1[Income])-SUM(Table1[Expenses])
=SUMIF(Table1[State],”MP”,Table1[Income])
Formulas, of course, are what make a spreadsheet a spreadsheet. If no formulas in spreadsheet, it means it just a static document — something like a word processor that has great support for tables could produce.
A formula entered into a cell can consist of any of the following elements:
· Operators such as + (for addition), – (for subtraction), / (for division) and * (for multiplication)
· Cell references (including named cells and ranges)
· Numbers or text strings
· Worksheet functions (such as SUM or AVERAGE)
After you enter a formula into a cell, the cell displays the result of the formula. The formula itself appears in the formula bar when the cell is activated.
Have you noticed that the formulas in your worksheet get calculated immediately? If you change a cell that a formula uses, the formula displays a new result. This is happens when the Excel Calculation mode is set to Automatic. This mode is the default mode; Excel uses the following rules when calculating your worksheet:
· When you make a change, enter or edit data or formulas. Excel immediately calculates those formulas that depend on the new or edited data.
· If it’s in the middle of a lengthy calculation, Excel temporarily suspends calculation when you need to perform other worksheet tasks; it resumes when you’re finished.
· Formulas are evaluated in a natural sequence. For Example, if a formula in cell D12 depends on the result of a formula in cell D11, cell D11 is calculated before D12.
Sometimes, you might want to control when Excel calculates formulas. Suppose, if you create a worksheet with thousands of complex formulas, calculation might slow things down. In such a case, you should set Excel’s calculation mode to Manual. When you’re working in Manual Calculation mode, Excel displays Calculate in the status bar when you have any uncalculated formulas. You can press the following shortcut keys to recalculate the formulas:
A. F9 calculates the formulas in all open workbooks.
B. Shift+F9 calculate the formulas in the active worksheet only. Other worksheets in the same workbook won’t be calculated.
C. Ctrl+Alt+F9 forces a recalculation of everything in all workbooks. 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).
D. Ctrl+Alt+Shift+F9 rechecks all dependent formulas and calculates all cells in all workbooks (including cells not marked as needing to be calculated).
Next we will talk about Cell and Range References
Cell and Range References
Most formulas refer to one or more cells. You can make cell references by using the cell’s or range’s address or name (if it has one). Cell references come in four styles:
A. Relative: The reference is fully relative. When the formula is copied, the cell reference adjusts to its new location. Example: A1.
B. Absolute: The reference is fully absolute. When the formula is copied, the cell reference doesn’t change. Example: $A$1.
C. Row Absolute: The reference is partially absolute. When the formula is copied, the column part adjusts, but the row part doesn’t change. Example: A$1.
D. Column Absolute: The reference is partially absolute. When the formula is copied, the row part adjusts, but the column part doesn’t change. Example: $A1.
By Default, all cell and range references are relative. To change a reference, you must manually add the dollar signs.
R1C1 notation
Each cell address consists of a column letter and a row number. However, Excel also supports R1C1 notation. In this system, cell A1 is referred to as cell R1C1, cell A2 as R2C1, and so on.
Below is the Comparison Table for Slandered and R1C1 notation
Standard = A1+1
R1C1 = RC[–1]+1
Standard = $A$1+1
R1C1 = R1C1+1
Standard = $A1+1
R1C1 = RC1+1
Standard = A$1+1
R1C1 = R1C[–1]+1
Standard = SUM(A1:A10)
R1C1 = SUM(RC[–1]:R[9]C[–1])
Standard = SUM($A$1:$A$10)
R1C1 = SUM(R1C1:R10C1)
Referencing other sheets or workbooks
We can also refer other sheet by a formula given below
=Sheet2! A1+1
To refer to a cell in a different worksheet, precede the cell reference with the sheet name followed by an exclamation point.
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. Below is an example for referring different workbook:
= [Data.xlsx] Sheet1! A1
If the linked workbook is closed, you must add the complete path to the workbook reference. Below is an example:
=’C:\My Document\Excel Files\[Data.xlsx]Sheet1’!A1
Referencing Data in a Table
You can create formulas that refer to cells within the table by using the column headers. In some cases, using column headers may make your formulas easier to understand. But the real advantage is that your formulas will continue to be valid if rows are added or removed from the table. For example, these are all valid formulas that use table references:
=Table1[[#Totals],[Income]]
=SUM(Table1[Income])
=Table1[[#Totals],[Income]]-Table1[[#Totals],[Expenses]]
=SUM(Table1[Income])-SUM(Table1[Expenses])
=SUMIF(Table1[State],”MP”,Table1[Income])
Using
Names
One of the most useful features
in Excel is its ability to provide meaningful names for various items. You can
name cells, ranges, rows, columns, charts, and other objects. You can even name
values or formulas that don’t appear in cells in your worksheet.
a.
Choose
Formulas->Defined Names->Define Name to display the New Name dialog box.
b.
Use
the Name Manager Dialog box (Formulas->Defined
Names->Name Manager or press (Ctrl+F3). This method isn’t the most
efficient because it requires clicking the new button in the Name Manger dialog
box, which displays the New Name dialog box.
c.
Select
the cell or range and then type a name in the Name box and press Enter. The
Name box is the drop-down control displayed to the left of the formula bar.
d.
If
your worksheet contains text that you’d like to use for names of adjacent cells
or ranges, select the text and the cells to be named and choose Formulas->Defined Names->Create from Selection For example, B3:E3 is named Region_A, B4:E4 is named Region_B,
and so on. Vertically, B3:B6 is named Half_1, C3:C6 is named Half_2, and so on.
Note that Excel changes the names to make them valid. (A hyphen isn’t a valid
character in a name.)
Formula
Errors
On entering a formula in a cell
and result shown as error is uncommon. There are two possibilities First: The
formula you entered is the
cause of the error. Second: The
formula refers to a cell that has an error value.
Error
Value
|
Explanation
|
#DIV/0!
|
The formula
is trying to divide by 0 (zero), an operation that’s not allowed on this
planet. This error also occurs when the formula attempts to divide by a cell
that is empty.
|
#N/A
|
The formula
is referring (directly or indirectly) to a cell that uses the NA worksheet
function to signal the fact that data isn’t available. A LOOKUP function that
can’t locate a value
also returns
#N/A.
|
#NAME?
|
The formula
uses a name that Excel doesn’t recognize. This can happen if you delete a
name that’s used in the formula or if you have unmatched quotes when using
text. A formula
will also
display this error if it uses a function defined in an add-in and that add-in
isn’t installed.
|
#NULL!
|
The formula
uses an intersection of two ranges that don’t intersect.
|
#NUM!
|
There is a
problem with a function argument; for example, the SQRT function is
attempting to calculate the square root of a negative number. This error also
appears if a calculated
value is too
large or too small. Excel doesn’t support nonzero values less than 1E–307 or
greater than 1E+308 in absolute value.
|
#REF!
|
The formula
refers to a cell that isn’t valid. This can happen if that cell has been
deleted from the worksheet.
|
#VALUE!
|
The formula
includes an argument or operand of the wrong type. An operand is a value or
cell reference that a formula uses to calculate a result. This error also
occurs if your formula uses a custom VBA worksheet function that contains an
error.
|
#####
|
A cell
displays a series of hash marks under two conditions: The column isn’t wide
enough to display the result, or the formula returns a negative date or time
value.
|
Array
Formulas
An array is 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.
You enter an array formula by
pressing Ctrl+Shift+Enter
Let’s show you how an array
formula can occupy more than one cell, create the worksheet shown in the figure
and then try these steps:
1. Select the range B1:B3.
2. Type the following formula: =LEN(A1:A3)
3. Press Ctrl+Shift+Enter.
The preceding steps enter a
single array formula into three cells. Enter a SUM formula that adds the values
in B1:B3, and you’ll see that the total number of characters in A1:A3 is 11. Here’s
the key point: It’s not necessary to actually display those five array
elements. Rather, Excel can store the array in memory. Knowing this, you can
type the following single array formula in any blank cell (Remember: Don’t type
the curly brackets and make sure that you enter it by pressing
Ctrl+Shift+Enter):
{=SUM(LEN(A1:A5))}
This formula essentially
creates a three-element array (in memory) that consists of the length of each
string in A1:A3. The SUM function uses this array as its argument, and the
formula returns 11.
Mega
formulas
A formula requires intermediate
formulas to produce a desired result. In other words, a formula may depend on
other formulas, which in turn depend on other formulas. After you get all these
formulas working correctly, you can often eliminate the intermediate formulas
and use a single mega formula. The advantages of mega formula use fewer cells
(less clutter), the file size is smaller, and recalculation may even be a bit
faster. The main disadvantage is that the formula may be impossible to modify.
Let’s have an example: A worksheet that has a column with thousands of people’s
names. And suppose that you’ve been asked to remove all the middle names and
middle initials from the names — but not all the names have a middle name or initial.
Editing the cells manually would take hours, and even Excel’s Data➜Data Tools➜Text To
Columns command isn’t
much help. So you opt for a formula based solution. Although this task isn’t
difficult, it normally involves several intermediate formulas.
Above figure shows the results
of the more conventional solution, which requires six intermediate formulas
shown in Figure. The names are in column A; the end result goes in column H. Columns
B through G hold the intermediate formulas.
