INTRODUCTION
A function is a predefined formula that performs calculations using specific values in a particular order. An excel is one of the software which is used in accounting purposes. Excel includes many common functions that can be used to quickly find the sum, average, count, maximum value, and minimum value for a range of cells. In order to use functions correctly, you'll need to understand the different parts of a function and how to create arguments to calculate values and cell references.
Functions are great time-saving
option for calculating data in a worksheet.
They can be used in place of formulas. When a formula becomes too long, or too complicated to write out manually we are advised to use functions.
The parts of a function
A function must be written in a specific way, which is called the
syntax. The basic syntax for a function is the
equals sign (=) or
at (@), the
function name (e.g
SUM, AVERAGE, e.t.c), and one or more
arguments. Arguments contain the information you want to calculate. Let us consider the function in the example below which will add the values of the cell range B2:B5.
Arguments
Arguments can refer to both
individual cells and
cell ranges and must be enclosed within
parentheses (brackets). You can include one argument or more than one argument, depending on the syntax required for the function.
For example, the function
=SUM(B2:B5) would calculate the
average of the values in the cell range
B2:B5. This function contains only one argument. When you want to use multiple arguments, separate the arguments by
comma (
;). For example, the function
=SUM(B2:B5, C4, D4:D9) will
add the values of all of the cells in the three arguments.
Creating a function
There are a variety of functions available in Excel. Here are some of the most common functions you'll use:
- SUM: This function adds all of the values of the cells in the argument.
- AVERAGE: This function determines the average of the values included in the argument
- COUNT: This function counts the number of cells with numerical data in the argument
- COUNTIF: This is a function used to count number of cells in the argument which will meet specific criteria.
- MAX: This function determines the highest cell value included in the argument.
- MIN: This function determines the lowest cell value included in the argument.
- ISBLANK: This is a function used to check if the cell contain something or not
- ISNUMBER:This is a function used to check if a cell contain a numeric value. The function will return a TRUE value if the cell contain number, date, and time
Let us discuss more on the following functions
1. ISNUMBER
The Excel ISNUMBER function returns TRUE when a cell contains a number, and FALSE if not. You can use ISNUMBER to check that a cell contains a numeric value, or that the result of another function is a number. For example, =ISNUMBER(A1) will return TRUE if A1 contains a number or a formula that returns a numeric value. If A1 contains text, ISNUMBER will return FALSE.
Syntax
=ISNUMBER (value)
=ISNUMBER(A1) //will return TRUE if A1 contains a number or a formula that returns a numeric value. If A1 contains text, ISNUMBER will return FALSE.
Arguments
- value - The value to check.
Notes
- Typically, value is supplied as a cell address, but you can wrap other functions and formulas inside ISNUMBER to test the result.
- ISNUMBER will return TRUE for Excel dates and times since they are numeric, and FALSE for numbers entered as text.
- ISNUMBER is part of a group of functions called the IS functions.
2. ISBLANK
The Excel ISBLANK function returns TRUE when a cell contains is empty, and FALSE when a cell is not empty. For example, if A1 contains "apple", ISBLANK(A1) returns FALSE. Use the
ISBLANK function to test if a cell is empty or not.
For example, =ISBLANK(A9) will return TRUE if A9 is empty, and FALSE if A9 contains text a formula (even if the formula returns an empty string "").
It's probably best to think of ISBLANK to mean "is empty" since it can return FALSE when cells look blank but aren't. For example, if A9 contains a space character (" "), or a formula that returns an empty string (""), A9 will look blank , but ISBLANK(A9) will return FALSE in both cases.
Syntax
=ISBLANK (value)
=ISBLANK(A9) //will return TRUE if A9 is empty, and FALSE if A9 contains text a formula (even if the formula returns an empty string " ").
Arguments
- value - The value to check.
3. COUNTIF
COUNTIF is a function to count cells that meet a single criteria and return number
representing cells counted. COUNTIF can be used to count cells with dates, numbers, and text
that match specific criteria. The COUNTIF function supports logical operators (>,<,<>,=) and
wildcards (*,?) for partial matching. The COUNTIF function in Excel counts the number of
cells in a range that match the supplied criteria. Non-numeric criteria needs to be enclosed in
double quotes but numeric criteria does not need quotes.
Syntax.
=COUNTIF(range, criteria)
Arguments
- range - This is then range of cells to count.
- criteria - The criteria that controls which cells should be counted.
=COUNTIF(D1:D10,50) // count cells equal to 50
=COUNTIF(D1:D10,">15") // count cells greater than 15
=COUNTIF(D1:D10,"nyeneu") // count cells equal to "nyeneu"
=COUNTIF(D1:D10,"<"&E2) // count cells less than value in E2
