Excel If Statement
The If statement is a logical function used when there are two possible outcomes to a given problem and we would like excel to recognise when to execute one possible outcome rather than another. For example, let’s say that we are trying to establish whether a given list of employees reached their targets. An employee’s income is listed in one column and his target is listed in the following column. If the employee has reached his target we would like the spreadsheet to write “Yes” in the last column, if he has not the formula should return “No”.
The function contains 3 arguments separated by comma’s:
The first argument is the condition. To specify a condition we use the operators greater than “>”, less than “<”, equal to “=”, greater than or equal to “>=” and less than or equal to “<=”.
The second argument is the value if the condition is true. Note; if you enter text it has to be between double quotation marks.
The third argument is the value if the condition is false.
Example
Excel And Function
The And Function is used when we want to use multiple conditions in a logical function. We can add a number of arguments into the And Function. The function will return as true ONLY IF ALL ARGUMENTS are true. If ONE ARGUMENT IS false the entire function will be false.
Excel OR Function
The OR Function is used when we want to use multiple conditions in a logical function. We can add a number of arguments into the OR Function. The function will return as true IF ANY OF THE ARGUMENTS are true. Only if ALL THE ARGUMENTS ARE FALSE the function will be false.
Excel SUMIF function
The SUMIF function is a logical function used when you would like to calculate the Sum of a range of cells ONLY IF a condition in another range of cells equal to true. For example if you would like to calculate the sales of a given employee when you have a large database with orders.
The SUMIF function has 3 arguments
The First Argument is the range of cells that contain the value that your condition will be based on (e.g the Employee Names Column)
The Second Argument is where you specify what the condition is.
The Third Argument is the range of cells that contains the values of the items you want to sum up (e.g. the Totals Column)
Example:
Employee names are listed in the range C2:C1000.
Sales are listed in the range D2:D1000
Calculate the Total Sales for John Smith
|
=SUMIF(C2:C1000,”John Smith”,D2:D1000) |
Excel AVERAGEIF function
The AVERAGEIF function is a logical function used when you would like to calculate the Average of a range of cells ONLY IF a condition in another range of cells equal to true. For example if you would like to calculate the average sales of a given employee when you have a large database with orders.
The SUMIF function has 3 arguments
The First Argument is the range of cells that contain the value that your condition will be based on (e.g the Employee Names Column)
The Second Argument is where you specify what the condition is.
The Third Argument is the range of cells that contains the values of the items you want to Average (e.g. the Totals Column)
Example:
Employee names are listed in the range C2:C1000.
Sales are listed in the range D2:D1000
Calculate the Average Sales for John Smith
|
=AVERAGEIF(C2:C1000,”John Smith”,D2:D1000) |
Excel COUNTIF function
The COUNTIF function is a logical function used when you would like to count a range of cells ONLY IF a condition in another range of cells equals true. For example if you would like to calculate the amount of sales of a given employee when you have a large database with orders.
The COUNTIF function has 2 arguments
The First Argument is the range of cells that contain the value that your condition will be based on (e.g the Employee Names Column)
The Second Argument is where you specify what the condition is.
Example:
Employee names are listed in the range C2:C1000.
Calculate the amount of Sales for John Smith
|
=COUNTIF(C2:C1000,”John Smith”) |