Logical Functions

A function is a predefined formula that performs a particular type of computation. All you have to do to use a function is supply the values that the function uses when performing its calculations; these are the arguments of the function.

SUM, COUNT, MAX, AVERAGE are all examples of functions. The function IF is a logical function.

A logical function can only return one of two possible results: TRUE or FALSE. For example:

=A1<25 produces the logical value TRUE if cell A1 contains a value less than 25 (otherwise the formula produces the logical value FALSE).

The IF function is frequently used because it can test for a particular condition in the worksheet and use one value if the condition is true and another value if the condition is false.

=IF(logical_test, value_if_true,value_if_false)      

For example:

=IF(B2>=1000)

The IF condition is TRUE if B2 contains a value of 1000 or larger; the condition is FALSE if the value in B2 is less than 1000.

You can have the IF function enter text. Text must be enclosed in quote marks.

For example:

=IF(B2>=100000, “Book the cruise tickets!”,”Tidy the house – we’re staying home!”)

If you want Excel to put the value of 100 in the cell when B2 contains a value greater than or equal to 1000, but you want 50 in the cell if the B2 value is less than 1000, the formula is:

=IF(B2>=1000,100,50)

If you want Excel to put 10% of the value in B2 in the cell when B2 contains a value greater than or equal to 1000, but only 5% of the value when B2 is less than 1000, put

=IF(B2>=1000,B2*10%,B2*5%)

References

A range of cells (for example, A1:A10):

• Uses a full colon to create one reference to all the cells in between and including the two end references in block.

Individual cells (for example, B4,D4):

• Uses a comma. Often used in the arguments of a function.

For example:

=SUM(A2,F5)

Other sheets in a workbook:

• To refer to other sheets in a workbook, either click and select, or type:

The name of the worksheet, then an exclamation mark (to separate sheet reference from cell reference), then the cell reference.

For example:

=Sheet2!A1

Spanning two or more sheets in a workbook

• To sum a range of cells that span two or more sheets in a workbook:

=SUM(Sheet2:Sheet6!$A$2:$C$5)

Relative References

Excel adjusts the cell references and copies in a formula relative to the direction of the copying.

If the original formula in B9 is =SUM(B3:B8), when Excel copies the original formula next door to C9 it changes the formula to =SUM(C3:C8).

All new formulas naturally contain relative cell references unless you specify otherwise.

Absolute References

There will be times when you want to compare a range of values to a single value (for example, where you want to compute what percentage each part is to the total).

Start your formula and select the cell first, then press F4 (or type $ before the column number and the row number).

For example:

=B9/$E$12

Then you can AutoFill the formula to other cells.

A List of Some Useful Functions