Microsoft Excel IF Functions

IF Function

You can create "intelligent" formulas which can be used to apply sets of rules to any worksheet calculation. You make a test and then, based on the outcome to this test, you can make your calculation follow different routes. You will need to use the Comparative operators to make the test and then the Excel IF function to process the results of the test. A formula like A1>100 returns a logical value.

 

For example, if you had the value of 100 entered into cell A1, the following formula would show "Big" in the cell but if the value in the cell was changed to a value less than 100 then it would show "Small". The recalculation is automatic.

=IF(A1>=100, "Big", "Small")

The True/False outcomes can be anything you like; text, numbers, cell  references, other calculations or other conditional tests.

Examples:

If you had the value of 100 entered into cell A1, the following formula would  show "Big" in the cell but if the value in the cell was changed to a value less than 100 then it would show no entry in the cell.

=IF(A1>=100, "Big", "")

If you had the value of 100 entered into cell A1, the following formula would multiply the value in A1 by 10% but if the value in the cell was changed to a value less than 100 then it would multiply the value by 2%.

=IF(A1>=100, A1*10%, A1*2%)

A common use of these formulas is to force out the display of divide by zero errors which look ugly when the worksheet is printed. When two empty cells are divided Excel always returns the #DIV/0! (Division by Zero) error value which many people consider unsightly, the formula is fine and will operate correctly when some numbers are entered into the cells but meanwhile only the error displays. If cell C1 contained the formula expression A1/B1 the cell will display #DIV/0! while A1 and B1 are empty. To force the formula to display zero instead of #DIV/0! use the IF and ISERROR functions:

=IF(ISERROR(A1/B1), 0, A1/B1)

Please note that the first argument in the IF function requires an expression that returns a logical value. The ISERROR function returns a logical value, therefore there is no requirement to explicitly test for the value TRUE. The logical test is ISERROR(A1/B1), it is not necessary for it to be ISERROR(A1/B1)=TRUE, although the formula would still calculate correctly.

Mouse Training Course Index

Contact Email

For Further Course Details Visit Our Homepage

Microsoft Accredited IT Training Provider

Microsoft Certified Training Materials

IT Training Courses delivered by Microsoft Accredited Facilitators

Microsoft MOS Test Centre London

More than Two Possible Outcomes

More than two possible outcomes 

The IF function returns either a TRUE or a FALSE result, to be able to choose from more than two possible outcomes then you need to have more than one logical test; one test yields two outcomes, two tests yields three possible outcomes etc.

Combine (or nest) more than one IF function inside another to give the possibility of having more than two possible outcomes from a decision structure.  The conditional tests are evaluated in turn to return the possible outcomes.

IF(Conditional Test, True value, IF(Conditional Test, True value, False value)) etc.

For example, based on the value in cell A1; we wish our formula to return either Small, Medium or Large.

Anything up to 20 is Small, Medium is over 20 but less than 50, Large is 50 or over.
=IF(A1<=20, "Small", IF(A1>=50, "Large", "Medium"))

or

=IF(A1<=20, "Small", IF(AND(A1>20, A1<50), "Medium", "Large"))
The nesting limit in Excel is 64 levels.

Mouse Training Course Index

Contact Email

For Further Course Details Visit Our Homepage

Microsoft Accredited IT Training Provider

Microsoft Certified Training Materials

IT Training Courses delivered by Microsoft Accredited Facilitators

Microsoft MOS Test Centre London

Microsoft Excel Array Formulas

Array Formulas.

Entering an Array Formula.

Array formulas are formulas which return a set (an array) of values and are usually entered into a range of cells. Any Excel formula can be entered as an array; some functions require that they are entered as arrays in order to return meaningful results.

Some array formulas are entered into a single cell; this is where the cells that they are referencing need to be considered as an array rather than a range of values. For example, where each individual cell in a range needs to be evaluated independently.

formulas other than that they offer an element of protection to the formula without having to use worksheet protection. The following examples illustrate the use of array formulas, firstly showing functions which have to be entered as arrays and finally where an array has to be used in order to obtain the correct result. Most array functions tend to be specialised statistical calculations, the statistical methods are not discussed.

Frequency Distribution.

A frequency distribution calculates how often values occur within a range of values and returns a vertical array of numbers. The calculation is done using the FREQUENCY function:

Mouse Training Course Index

Contact Email

For Further Course Details Visit Our Homepage

Microsoft Accredited IT Training Provider

Microsoft Certified Training Materials

IT Training Courses delivered by Microsoft Accredited Facilitators

Microsoft MOS Test Centre London