A couple of days ago, I received a question from a reader asking how to use the IF function in Excel for a particular scenario he needed to do at work. I thought other people might benefit from having a short tutorial on this, so I’m providing it here, divided into a few parts.
The first part (here) will go into the basic IF statement, and subsequent parts, such as how to use nested IF functions with other logical operators, will describe how to use it in slightly more complicated scenarios or with additional features. For example, the reader’s question had to do with using an IF statement given two conditions, e.g. if a number falls between two other numbers. I address those in the separate post in the link above.
But, let’s go over the basic IF function here, first.
Let’s say that you have a series of numbers in Excel like this that represent sales figures that 10 of your salesmen have made this quarter, in column B:
Before we go further, if you’d like to work through the examples yourself, here’s the raw data you can copy into an Excel worksheet. First, open up a blank excel worksheet. Next, highlight the table below. Copy it, and then go back to your excel worksheet. Go to cell A4 (or another empty cell, if you want to put the data elsewhere), and then select “Edit” from the menu bar. Select “Paste Special” and then “Text” from the popup box. Click “OK”. The data should appear in your Excel worksheet just as it does above.
| Sales | |
| Salesman A | 87925 |
| Salesman B | 100000 |
| Salesman C | 145000 |
| Salesman D | 200750 |
| Salesman E | 178650 |
| Salesman F | 99555 |
| Salesman G | 147000 |
| Salesman H | 213450 |
| Salesman I | 122680 |
| Salesman J | 92500 |
Ok, now back to the tutorial.
Next, suppose that you give them a bonus commission of 12% if they’ve exceeded a threshold value, say, $100K. Rather than going through each sales figure yourself and manually checking to see if that number is greater than $100K, and if so, calculating the commissions in another column (column C in this case), you can use the IF function to do this for you.
First, let’s put the 12% commission percentage in B1 so we can change it later if we want to, and the $100K figure in B2 for the same reason:
Here’s the syntax for the IF statement in Excel. You put the function in the cell or cells where you want the result. The IF function takes three inputs:
-
=IF(condition to test for, what to do if the condition is true, what to do if the condition is NOT true)
The first two arguments are required, and the last one is optional. If you leave out the part about what to do if the condition isn’t true, then Excel will return “FALSE” automatically. This can be useful in certain situations. In our case, just to avoid confusion, we’ll put in a specific action for what to do when the condition is not true.
So, in our case, we’d start in cell C5, where we want to calculate the salesman’s bonus, if any. The IF statement would be written as.
- =IF(B5>$B$2, B5*$B$1, “No bonus”)
Like this:
Here’s what that says in plain English: If the number in cell B4 is greater than the number in cell B2, then multiply B4 by B1. If not, then write “No bonus” in the cell instead. Once you enter in this formula, the result will be printed in the cell. In this case, Salesman A didn’t make enough sales, so “No bonus” appears in the cell.
By the way, the dollar signs by $B$2 and $B$1 are simply an ugly way that Excel uses to keep a cell “fixed”. Later on, we’ll copy and paste the IF function we wrote in cell C4 and the formula will automatically adjust for each row. For example, if we paste the formula in cell C6, the formula will automatically change to:
-
=IF(B6>$B$2, B6*$B$1, “No bonus”)
If we hadn’t put “$” by B2 and B1, then these would have automatically adjusted as well when we pasted the formula in another cell. Of course, you could avoid all this and put 100,000 and 12% into the IF statement directly, but if you needed to change these parameters later on, it’d be much harder to do.
After copying and pasting the IF formula down the column, here’s our result:
And voilá. It’s done. Now you can format all the numbers and alignments nicely so it’s more readable.
If you have several rows of data, the IF statement is a lot easier a tool to use than going through each case manually! In an upcoming post, I’ll try to describe some other nifty things you can do with the IF statement.
***************************************************
Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss
***************************************************
George Gray
C40 =IF(D391000000,1000000,D39)
If Cash available in C39 is less than $0 spend nothing but if C39 is between 0 & 1000000 use the amount in C39 if over 1M only use 1M
kat
I am learning excel and need help with this question
Bonuses are calculated based on two criteria. 1. They have to have met
the target of $20K in each month in order to qualify. Cumulative does not count.
It’s not $40 for both months. Anything less than $20k in any month disqualifies
for from any bonus. Then if they meet the criteria the bonus is calculated at
the tiers shown. Of course anything less than $40k get nothing but you have to
calculate the right percentage for the total of both months for each qualifying
person.
Employee January February Bonus
Smith, Joe $9,286.78 $26,531.88
Jones, David $26,432.03 $31,028.46
Doe, Jack $20,587.52 $32,057.38
Blank, Pat $19,071.14 $25,472.54
Orlando, Grace $20,668.00 $25,485.13
Davey, Laurie $17,044.13 $24,422.06
Leal, Jean $19,238.26 $22,891.51
Rodgers, Richard $28,062.67 $14,415.38
Scaggs, Chris $23,087.47 $21,801.39
Owen, Vicky $26,521.91 $19,193.79
Walsh, Margaret $23,470.40 $25,340.72
Palmer, Ben $32,802.33 $21,764.91
Adams, Tom $21,965.45 $26,465.77
Post, Mark $21,966.63 $18,856.54
Russ, Ola $17,346.49 $9,736.54
Bonus Totals $-
Monthly Target $20,000.00
Bonus Tiers
Up to $40,000.00 0
Up to $50,000.00 5%
Up to $60,000.00 10%
Over $60,000.00 15%
shenny
Please help with excel if function on how to find recommendation using the ff ex…
SPH HOURS QA Recommendation
0.08 40 75%
Use the word “Full Pass” if the Agent was able to meet all three targets (SPH, HOURS and QA).
Use the word “EOC” if the Agent did not meet all three target.
Thank you.This is new to me
I’m getting error with the formula I used.
CatFox
I am trying to write this to excel…
If F12=NameHere then Yes if not then No I have tried and tried and now I need someone to help me
Boyd Mooso
Kat, yours is just like the original example.
Catfox, what are you asking?
Lorraine Ryan
Help please with the following:
Contents of cell E4 is Time Taken for Deliveries in hours i.e. 52, 63, 11, etc
Question as follows:
Accommodation is not paid for when the journey is less then 8 hours,
When a journey is greater than 8 hours but less then or equal to 20 hours, one nights accommation is paid at a rate of €79
If the journey takes between 21 and 40 hours then accommodation costa re paid at a rate of €150
When the journey takes longer than 40 hours a lump sum of €250 is paid
Ejay
very helpful. I’m working my wife’s assignment about worksheet., now I’m done! thanks a lot….
Sharalee
You have just saved the day thank you so much! I was going insane trying to finish an assignment on IF functions for University and could not for the life of me get it to work, but thanks to this page I can now say I DID IT and best of all the formula’s worked!
JIM
i like use if statment
coln if coln f3>g3 then answer should be in red font
if g3>f3 then answer should be in black font
please help me
JIM
i like to use if statment saying
=$f3>g3 then answer should in red color font, otherwise black
can you please formate this formula
Daniel
I am wanting to use an “if statement” to count the number of days sales for “Item 1” or “Item 2” that are above $100. How would I write this formula?
jody
All full-time (FT) employees are eligible for a bonus. Pay Grade A employees receive $3,000 (cell X2), Pay Grade B employees receive $6,000 (cell X3), and Pay Grade C employees receive $8,000 (cell X4). In the Bonus Amount column, enter nested IF functions to calculate the bonus. For employees not eligible for a bonus, display the text NE.
I need help please, I’ve tried everything but nothing works, I’m having a hard time understanding nested if functions. Thank you.
Moya
I am trying to do this formula but I am having some difficulty. NEED some help.
For every 5 hours, you get $500*(30/100). I was using (INT(S3/5)*500)*1.3, with decimal but I changed it over to time eg. (fr 5.3 to 5:30) and the formula stops working. Could someone please help me on this please? Would appreciate this so much.
anam
Great help, thanks :)
Ifeanyi
How do I design an excell template that calculates due date based on account credit and balance?
Eg, when will an account credited with $3000 expire (ie with exact expected date for account to be replenishes)
debanjana
Hi. actly i want to know the formula of IF which i need to create in my salary slab of P.tax which automatically calculates when i put the next salary slab so please help me . My salary slab is 0-8500- 0,8501-10000-90,10001-15000-110,15001-25000-130,25001-40000-150, above 40000 is 200 so what will be the condition of IF
YUKATHARSA
GOOG ANSWER
YUKATHARSA
Good answer
YUKATHARSAN
but this answer good,please tell mor if question and answer.
YUKATHARSAN
please tell me excel bonus farmulas and examle
dirty harry
how would I do this. if checking balance is less than 250, put in low. if its between 250, but less than 2000, put medium. and high otherwise
Philip Morris
I’ve been tasked with doing a spreadsheet for stock levels, i was hoping someone would be able to help with a formula. I have 3 fields that I need to interact with each other if possible they are Qty, Qty Used and Running Total. I was wanting what ever is entered in the Qty Used field to be deducted from the Qty field and added to the Running Total field but then I also wanted the Qty Used field to rest to zero. Is any of this even possible, Many Thanks
Wil
Need Excel help.
I understand most functions in the Excel system but this one’s got me stumped.
Example:
There is financial information in column C.
In column J there are some “Yes” entries corresponding to a sales person.
I need Excel to keep a running total of the finances in column C only if column J has a “Yes” in it. If it does not contain a “Yes” then it should not add that rows earnings in column C.
I’ve tried some IF statements with no luck.
Can someone help?
Nauman
i recieved shed amounts or balances i want a formula which can apply by using amount nil or balance as a text
Lindsay
Hi I am wondering if someone could help me with an excel formula. I am trying to combine multiple functions.
I have employees in Union groups lets say, A, B and C and if they have greater than 10 years of service some groups receive 25% of a value and some receive 35% of a value.
I have the formula to return if they the value is greater than 10 which is this =IF(K2>9,N2*25%) but how to I add if Cell C1 is any one of these values (Group A, Group B or Group C)
I want to say if C1 is “group B” use the above calc, but if C1 is Group A use a different percentage of 35
Please help.
ajay
what is logical formula of this condition calculate commission 2%of total sales is>=4000000,1.5%of total sales if total sales>=2500000,otherwise 1%of total sales but commission is not exceed 75000
Alex Luna
Very confused. how would I do this. if checking balance is less than 250, put in low. if its between 250, but less than 2000, put medium. and high otherwise
Muhammad Haroon
Target of a worker is 50 shirts but if he exceeds 50 the excess 6 shirts will be considered as bonus shirts and the remaining shirts are normal shirts. But the excess amount should be deducted in a way that the remaining shirts should not be less than 50.Following is the illustration:-
Shirts done Bonus shirts Normal shirts
60 6 54
50 0 50
54 4 50
49 0 49
Please give any excel formula to manage this. Thanx alot
R.THULASSIRAMAN
salary paid monthwise for 6 months(i.e. april to september) if salary paid more than rs.75,000/- amount of Rs.1095/-tobe deducted towards Professioanl Tax, if paid less than Rs.75000/- amount to be deducted Rs.860/- how to do in Excel
home
vpiggkfwrazihmcqvbcysrnxqjlzuoeidjgynqqfqozkzjtfufhvcpecxkk
Nowren
A=60000,B=50000,C=40000,they paid 50000,40000,30000,if due>5000,then add 5000 each thousand and find out due with payment
Madtown
I really need help with this IF function I’ve been stuck on..
In cell G6, create a formula using the IF function to determine the bonus based on the following criteria:
a. A bonus is awarded for review scores (in cell D6) higher than 85.
b. If the review score is greater than 85, the bonus amount is calculated by multiplying the salary (in cell F6) by .05.
c. If the review score is 85 or lower, the bonus amount is 0.
When I put the formula in excel it just comes out as true or false. I have the correct answers and I can’t get them. They are supposed to be numbers not true or false. Please help I’m so confused on what I’m doing wrong! Thank you!
95Margareta
Hi admin, i must say you have high quality posts here.
Your page should go viral. You need initial traffic only.
How to get it? Search for; Mertiso’s tips go viral