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.
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
No comments:
Post a Comment