Formula Techniques - Examples

The purpose of this section is to reduce the Logical Errors made by users when creating formulas. Users should build simple formulas but at the same time avoid entering an excessive number of formulas if there are easier ways to do their tasks.

The illustration below shows an example of an Array Formula. This formula computes the total cost of a finished product. A single Array formula replaced a number of common formulas needed to calculate the same result. The way to insert an Array formula is to enter the formula and press <CTR> <SHIFT> <ENTER>. When you enter this formula, Excel automatically inserts the formula between { } (braces).

Another way to reduce the number of formulas is to compact them into one as shown in the example below. The followings are the steps to do that:
1. Go to the cell containing the final formula (F3) and look for the cell references that have other formulas ie E3;
2. Go to cell E3, select the formula in the formula bar (except the sign "=")  and press the copy button;
3. Press Esc, go back to cell F3 and in the formula bar select the reference E3, press paste and then enter. Cell F3 will now have the following formula:
=ROUND(IF(D3,0,C3),0)
4. Follow again steps 1 to 3 and copy the formulas from cells D3 and C3 to the formula of Cell F3;
The formula will now become:
=ROUND(IF(ISERROR(C3),0,A3/B3),0)
The above formula still contains a reference to a cell (C3) which has a formula in it. Follow again steps 1-3 and the formula will now become:=ROUND(IF(ISERROR(A3/B3),0,A3/B3),0) which is the final formula.

If you find the above method difficult you can use the "Spreadsheet Composer", a free Add-in which can be downloaded from the Dust Free Solutions.