IF Formula
|
|
|
|
|
|
|
|
Name
|
Sales
|
Target
|
Result
|
|
|
|
Alan
|
1000
|
5000
|
Not Achieved
|
=IF(C4>=D4,"Achieved","Not
Achieved")
|
|
|
Bob
|
6000
|
5000
|
Achieved
|
=IF(C5>=D5,"Achieved","Not
Achieved")
|
|
|
Carol
|
2000
|
4000
|
Not Achieved
|
=IF(C6>=D6,"Achieved","Not
Achieved")
|
|
|
|
|
|
|
|
|
|
What Does It Do?
|
|
|
|
|
|
|
This
function tests a condition.
|
|
|
|
||
|
If the
condition is met it is considered to be TRUE.
|
|
||||
|
If the
condition is not met it is considered as FALSE.
|
|
||||
|
Depending
upon the result, one of two actions will be carried out.
|
|||||
|
|
|
|
|
|
|
|
Syntax
|
|
|
|
|
|
|
=IF(Condition,ActionIfTrue,ActionIfFalse)
|
|
|
|||
|
The Condition
is usually a test of two cells, such as A1=A2.
|
|
||||
|
The
ActionIfTrue and ActionIfFalse can be numbers, text or calculations.
|
|||||
|
|
|
|
|
|
|
|
Formatting
|
|
|
|
|
|
|
No
special formatting is required.
|
|
|
|||
|
|
|
|
|
|
|
|
Example 1
|
|
|
|
|
|
|
The
following table shows the Sales figures and Targets for sales reps.
|
|||||
|
Each has
their own target which they must reach.
|
|
||||
|
The =IF()
function is used to compare the Sales with the Target.
|
|||||
|
If the Sales
are greater than or equal to the Target the result of Achieved is shown.
|
|||||
|
If the
Sales do not reach the target the result of Not Achieved is shown.
|
|||||
|
Note that
the text used in the =IF() function needs to be placed in double quotes
"Achieved".
|
|||||
|
|
|
|
|
|
|
|
Name
|
Sales
|
Target
|
Result
|
|
|
|
Alan
|
1000
|
5000
|
Not Achieved
|
=IF(C31>=D31,"Achieved","Not
Achieved")
|
|
|
Bob
|
6000
|
5000
|
Achieved
|
=IF(C32>=D32,"Achieved","Not
Achieved")
|
|
|
Carol
|
2000
|
4000
|
Not Achieved
|
=IF(C33>=D33,"Achieved","Not
Achieved")
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Example 2
|
|
|
|
|
|
|
The
following table is similar to that in Example 1.
|
|
||||
|
This time
the Commission to be paid to the sales rep is calculated.
|
|||||
|
If the Sales
are greater than or equal to the Target, the Commission is 10% of Sales.
|
|||||
|
If the
Sales do not reach Target, the Commission is only 5% of Sales.
|
|||||
|
|
|
|
|
|
|
|
Name
|
Sales
|
Target
|
Commission
|
|
|
|
Alan
|
1000
|
5000
|
50
|
=IF(C43>=D43,C43*10%,C43*5%)
|
|
|
Bob
|
6000
|
5000
|
600
|
=IF(C44>=D44,C44*10%,C44*5%)
|
|
|
Carol
|
2000
|
4000
|
100
|
=IF(C45>=D45,C45*10%,C45*5%)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Example 3
|
|
|
|
|
|
|
This example
uses the =AND() within the =IF() function.
|
|
||||
|
A
builders merchant gives 10% discount on certain product lines.
|
|||||
|
The
discount is only given on products which are on Special Offer, when the Order
Value
|
|||||
|
is £1000
or above.
|
|
|
|
|
|
|
The
=AND() function is used with the =IF() to check that the product is on offer and
that
|
|||||
|
the value
of the order is above £1000.
|
|
|
|||
|
|
|
|
|
|
|
|
|
Special
|
Order
|
|
|
|
|
Product
|
Offer
|
Value
|
Discount
|
Total
|
|
|
Wood
|
Yes
|
£
2,000
|
£
200
|
£
1,800
|
|
|
Glass
|
No
|
£
2,000
|
£
-
|
£
2,000
|
|
|
Cement
|
Yes
|
£
500
|
£
-
|
£
500
|
|
|
Turf
|
Yes
|
£
3,000
|
£
300
|
£
2,700
|
|
|
|
|
|
=IF(AND(C61="Yes",D61>=1000),D61*10%,0)
|
||