| | Feedback | ISNA errors | Graphs | | ||
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() only offers 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").
| - | 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 | ||
You can also use the logical functions AND() and OR() to increase the complexity of a formula. They usually need to be incorporated within an IF function and allow many criteria to be specified and return either a true or false value.
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).
| [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.
| [D4] =C4 / B4 | On row 5 this results in an error caused by dividing by zero |
| [E4] =IF(B4<>0, C4 / B4, 0) | If the cell in column B does not equal zero, perform the calculation. Otherwise return a value of zero |
| [F4] =RANK(E4, $E$4:$E$7) | The value in cell F4 is the third lowest in the range E4:E7 |
| [D9] =IF(ISERROR( SUM(D4:D7)), "Yes","No") | If the value returned by the SUM is an error, return Yes. If not, return No |
| - | 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 | |||
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. It has been rewritten for Excel 2010. |
| file: xlifrankerrors.htm | Page last updated Jan14 | © MeadInKent.co.uk 2015 | CMIDX S2 P3 Y |