Sometimes you need your data to tell you more. The IF formula as a function gives your data a voice and turns raw numbers into useful information. For example: Your wholesale company charges different rates depending on the size of bulk orders – customers who order more receive volume discounts. You want to quickly calculate the volume discount on a batch of orders so your sales team can deliver the correct quotes to their customers. In a nutshell, you need to calculate your cost and volume numbers into a price for your customers based on an additional bit of logic.
Here is how your data might look in a simple spreadsheet:
To set up an IF function, try thinking about your problem in natural language:
Problem: IF (the number of units is over 1000, the customer receives a volume discount of 3%, otherwise the customer receives no discount).
Now, just fill in the cells and values for the statement:
=IF(D2>1000, 0.03,0)
• D2 is the cell that shows the number of units sold
• 0.03 is the discount (3%) received if D2 is greater than 1000
• 0 is the discount received if D2 is not greater than 1000
If you are still confused about how to use an IF function, Excel offers some help:
1. Click on the Formulas tab, then click the Insert Function button.
2. Select IF from the Insert Function dialog box. Click OK.
3. Fill in the function arguments in the next dialog box. The dialog box includes prompts to help you set up your
IF function. Click OK. Excel will populate the cell with the function in the correct format.
5 Comments
HOW TO USE THE APPROPRIATE FUNCTION TO COMPUTE A DISCOUNT AS FOLLOWS:
5% MULTIPLIED BY THE FEE IF THE BIRTHDATE IS BEFORE JULY 1,2009, OTHERWISE NO DISCOUNT.
A 5% discount can be calculated at 95% of a fee (100% – 5% is 95%). We want to pose the question “IF the birthdate is less than July 7th, 2000.”
The IF() statement below will do just that. The DATE(2009,7,1) within the IF() is the comparison date.
=IF(A2<DATE(2009,7,1),B2*0.95,B2)
Reads like this:
“If the date in A2 is less than July 7th, 2000 then multiply the fee (B2 by 95%), or else, use the entire fee in B2)”
As you can see – that function can copy down to the next row and derive a different, correct, answer.
Note* The IF() function is very versatile and worth your study. The comas separate the condition, from the TRUE and FALSE responses.
How should you use it if the problem is :
“if the payment method was express miles or rewards, the customer should receive the discount (20%). otherwise, no discount”
The word “or” in your question will prove to be the sticky part but this can still be done.
We must use two IF() functions – one nested within another.
In English, the nested IF(), above would read:
“If cell A2 is ‘EXPRESS MILES’, then the answer is 0.2 or else, if cell A2 is ‘REWARDS’, then the answer is 0.2 or else, the answer is 0.”
0.2 is, of course, 20%
Because they are nested, the IF()s are evaluated one at a time, in sequence, from left to right.
If both are false (neither ‘EXPRESS MILES’ nor ‘REWARDS’ is true) then zero is the answer for that cell.
1 Trackback