Excel Functions: If, And, Or, Rank and IsError
The IF function allows you to select one of two alternative values which are
dependent upon a specified condition. It is in the format: =IF(Condition,
Value if True, Value if False). If the condition is true then the first value is returned,
otherwise the second is used.
| IF(C3 = "Y", B3 * 0.175, 0) |
If cell C3 contains the character "Y", then return a value of B3 x 0.175.
Otherwise return a value of zero. This could be used if an item may or may
not require VAT to be paid |
| IF(C5 = D5, "OK", "Warning: Does not balance") |
If cell C5 is equal to D5 then return "OK". Otherwise return a warning message.
This is a useful method of informing the user that calculations have been
reconciled and appear to be correct (e.g. cross-casting to ensure that the
sum of all of the rows equals the sum of the columns). |
| IF(C6>0, B6/C6, 0) |
If the value in cell C6 is greater than zero, return the value of B6
divided by C6, otherwise return 0. This is a very useful method of
preventing the #DIV/0! error message. |
IF() allows you to offer only two outcomes but this can be increased if further IF's
are embedded in either the true or false declarations e.g.
=IF(A1>B1, IF(B1>0, "A", "B"), "C"). You can also use the
logical functions AND() and OR().
These both allow many criteria to be specified and return either a true or false value.
They usually need to be incorporated within an IF function. AND() and OR() both
require you to provide a list of statements, each one separated by a comma. If all
of the AND() statements are correct it will return a True value. If any of
the OR() statements are correct it will return a True value. It could take the
form: =IF(AND(statement1, statement2, statement 3), ValueIfTrue, ValueIfFalse).
| - |
H |
I |
J |
K |
L |
M |
N |
| 1 |
Test Results |
|
|
www.meadinkent.co.uk |
| 2 |
|
|
|
|
|
|
|
| 3 |
Name |
Test 1 |
Test 2 |
|
|
|
|
| 4 |
Jenny |
3 |
10 |
|
|
Pass |
Pass |
| 5 |
Kathy |
9 |
9 |
|
Good |
|
Good |
| 6 |
Mary |
7 |
6 |
|
|
|
Fail |
| 7 |
Susan |
8 |
10 |
|
Good |
|
Good |
|
| L4 |
=IF(AND(I4>=8, J4>=8, I4+J4>=17), "Good", "") |
| Good if
both scores are greater than or equal to 8 and the total is 17 or more |
| M4 |
=IF(AND(I4+J4>12, OR(I4>=8, J4>=8), L4<>"Good"), "Pass","") |
| Pass if
the total is more than 12, at least one of the scores is 8 and 'Good'
has not been awarded |
| N4 |
=IF(L4&M4="", "Fail", L4&M4) |
| Fail if
not awarded a 'Good' or 'Pass' |
Note that often the SUMPRODUCT() function can be
adapted to be a more flexible alternative to using combinations of IF and AND.
ISERROR() will return a True value if its argument results in an error. Otherwise
it will return a False value. It takes the format: = ISERROR(range).
Excel can generate various errors if you try to perform invalid tasks such
as dividing by zero. If you include a cell containing an error within a
range used by another function a further error will result. Note that some
specific error codes such as #N/A can be recognised by other specialist logical
functions such as ISNA().
RANK() is a simple function which tells you the position of one number within
a sequence of numbers. It takes the format: = RANK(value, series, AorD).
The value normally represents a cell reference containing a number. The series
can represent a range of cells containing a collection of numbers. The AorD argument
instructs the function whether to calculate the ranking in Ascending or
Descending order. If AorD is set to zero or omitted, the order will be
descending. Anything other than zero will result in an ascending order.
| - |
A |
B |
C |
D |
E |
F |
| 1 |
Out Patient visits per In Patient episode |
|
|
| 2 |
|
|
|
|
|
|
| 3 |
Doctor |
In Pat |
Out Pat |
OP/IP* |
OP/IP** |
Rank |
| 4 |
Dr Smith |
32 |
61 |
1.91 |
1.91 |
3 |
| 5 |
Dr Lam |
0 |
42 |
#DIV/0! |
0.00 |
4 |
| 6 |
Mrs Dapper |
14 |
43 |
3.07 |
3.07 |
1 |
| 7 |
Prof. Plum |
53 |
123 |
2.32 |
2.32 |
2 |
| 8 |
|
|
|
|
|
|
| 9 |
Any errors detected in column? |
Yes |
No |
|
|
|
Formulae used in the example
| cells D4:D7 |
=C4 / B4 |
1.91 |
on row 5 this results in an error caused by
dividing by zero |
| cells E4:E7 |
=IF(B4<>0, C4 / B4, 0) |
1.91 |
Provided the cell in column B does not equal zero, perform the calculation.
Otherwise return a value of zero. |
| cells F4:F7 |
=RANK(E4, $E$4:$E$7) |
3 |
The value in cell F4 is the third lowest in the range E4:E7 |
| cells D11:E11 |
=IF(ISERROR(SUM(D4:D7)),"Yes","No") |
Yes |
If the value returned by the SUM is an error, return Yes. If not, return
No. |
In the example, the IF function in column E provides a safer alternative
to the simple formula in column D and prevents the division error from occurring. The ranking formula in column F used an
absolute reference indicated by the dollar sign ($). This fixed the range
of rows as being 4 to 7 when the formula was copied to cells below.
Get this information as a document
accompanied by Excel
worksheets |
 |
Click here for details about
obtaining this file |
