Functions used in this book 1 2 3 4 5 pg 221 222 223

29. Functions Used In This Book

Functions Used In Formulas

The file uses the functions listed here. You will notice that they are not many, and it is easy for you to master them.

1. =SUBTOTAL

This function in a cell is used to get totals of figures in other cells which are visible. If figures are not visible (e.g. due to being filtered), it will not be included in the total. The full syntax of this function is:

SUBTOTAL(9, Range address)

The figure 9 represents SUM. The Range address is the address of the range you want to total, e.g. A3:A40.

Example: SUBTOTAL (9, A3:A40)

If this function is in cell A1, cell Al will show the total of all the figures from A3 to A40. If cell A20 is hidden due to filtering, the figure in it will not be included in the sum.

NOTE : For more information, you can search for 'SUBTOTAL worksheet function' in Excel Help.

2. =INDIRECT

This function is used inside another function to make the cell or range address permanent. For example, in cell A1, we want to sum figures in range address A3:A40. So, in cell Al, we write =SUM(A3:A40). If we subsequently delete row 20, the range address under normal conditions will change to A3:A39. To make the range address permanent, we use INDIRECT function, i.e. =SUM(INDIRECT("A3: A40")). This way, even if we delete rows in the original range, the address will still be the same. (Notice that there is a double-quote mark" before and after the address).

221

The syntax is:

=INDIRECT("cell or range address")

NOTE : For more information, you can search for 'INDIRECT worksheet function' in Excel Help.

3. =IF

The full syntax of this function is:

=IF(Condition, return this part if condition is met, return this part if condition is not met)

Example:

In cell A1 we want it to display "Good" if the figure in cell B1 is more than 50 and show "Not Good" if it is less than 50. Then in cell A1, we would write:

=IF(B1>50, "Good", "Bad").

NOTE : For more information, you can search for 'IF worksheet function' in Excel Help.

4. =VLOOKUP

This function can be used to check whether an item exists in a list. The full syntax of this function is:

=VLOOKUP(Item to find, area to find, 1, FALSE)

The parameter "1" and "FALSE" must always be used.

Example:

In cell K5 in sheet DATA, we want to check whether the account name "LAND" in cell G5 in sheet DATA exists in the data area "A5:G1000" in sheet TRIAL BALANCE. We would then write:

=VLOOKUP(DATA!G5,'TRIAL BALANCE'!A5:G1000, 1, FALSE)

If the item exists, the function will return TRUE, otherwise it will return #ERROR. We will see TRUE or #ERROR in cell K5.

NOTE : For more information, you can search for 'VLOOKUP worksheet function' in Excel Help.

5. =ISERROR

This function is usually used together with function IF and one other function to check whether the other function returns #ERROR. The full syntax of this function is:

=ISERROR(Other function)

Example:

The function VLOOKUP may return #ERROR. We don't want to see #ERROR in the cell where the function VLOOKUP is written. So, if VLOOKUP returns #ERROR, we want to see some other more meaningful word(s) such as "NOT OK". Thus we can write:

=IF(ISERROR(VLOOKUP(G5,INDIRECT("TB!A5:A1000"),1,FALSE)), "NOT OK", "OK")