How to use the IF function in Excel

Corporate finance, Excel function tutorials

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to Lynda.com. for professional help and Excel Tutorials
***************

A couple of days ago, I received a question from a reader asking how to use the 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 , 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.

***************************************************

***************************************************

71 Feedbacks on "How to use the IF function in Excel"

Flexo

Just today, I had to work with logical operators within the IF function. They didn’t work as I expected. I wanted the cell to return a certain value if one of two conditions, each referencing different cells, were true.

My programming-inspired intuition was that =IF((A1=”yes”) OR (A2=”yes”),”OK”,”Not OK”) would be the right way to notate it, but it turns out the function should be =IF(OR(A1=”yes”,A2=”yes”),”OK”,”Not OK”).

Maybe a nice tutorial on logical operators would be a good one to do. :-)

Ricemutt

Yup, Excel’s really awkward when it comes to handling two conditions (AND or OR). Actually, the reader’s original question had to do with just that, so hopefully my explanations in Part II will be useful and not just add to the confusion! Please feel free to comment and clarify anything I try to explain, too. Thanks Flexo!

An

The IF function rocks my world. I have used it so often at work, for different things. I’m glad I took the time to figure it out. COUNTIF is another fave.

Can you do a tutorial on the VLOOKUP and HLOOKUP? And maybe on Pivot Tables, too? Somehow those escape my understanding. Though I’ve never really sat down and tried to get into them.

Ricemutt

Sure, I was thinking of doing VLOOKUP and HLOOKUP anyway. (And the second part to the IF post.) :)

How to use nested IF statements in Excel with AND, OR, NOT | Experiments in Finance

[…] Following up on last week’s introductory post on using the IF function, here’s one on how to use Excel’s IF statement when you have multiple conditions to meet. […]

Ben

That’s nice, but what if I think wanted to highlight the whole row that says Bonus? So I could scroll throw and quickly she a different color for all users that were highlighted.

Ricemutt

If I understand your question correctly, you’re looking for conditional formatting, which is described in this post:

Using Excel’s Conditional Formatting

Shakir

The data used in the examples should be available for copying so that the user can copy it into a spreadsheet and work on it practically.

very good.

Chandan Kumar

this is quite useful for me. I am very happy to get this helpful topic on internet.

Thanks

sunnyday

thanks for your help i am becoming unstuck the brain needs a shake up in maths. I used to like it as a kid but then I stepped back when I thought I was’nt quick on the uptake. I can sing a song! m

Sanjeev Gupta

The tips on v-look up and IF are indeed very use ful and very nicely explained.

Sagar Chawla

What is the maximum no. of IF function can be used in one cell? (nested IF)

Sagar Chawla

1 Can Charts be automated without using macros, if data range keeps expanding? If yes, what formulae or technique will be used?

2 What command in VBA Excel is used to format all cells containing a certain text. Example Colour code all cells containing TRx. Cells may have values like Alzheimer’s TRx, Anti – Herpetic TRx, etc.

Getting more from customer comments cards - Part 2 | Tracking Tourism

[…] Conditional formatting, at Life Hacker The IF Function in action at Experiements In Finance Displaying images based on conditional results at ExcelTips and, just about everything on conditional formatting, aslo at ExcelTips […]

ross

i need help on a if statement,

i want to do something like

if a1=9 then c9=Ross

but i have typed in the following =if

(a1=9,c9=ross,c9=0) and it just says

error

paresh nautiyal

excellent place to learn

wayne

I need help I am making a schedule that instead of hours working it list station numbers, based on those station numbers you work x amount of hours. My boss asked me if i can add everyones hours up for a total at end of the week. Instead of manually doing this is there anyway i can use the If statements along with auto sum at the end of the week to add up total hours working for the week?? i know it sounds confusing lol any help or advice is appreciated.

deepaK

excellent explanation

Dave

Thanks, this was so helpful, I totally get it now!!

Frances

here is my question: Calculate Bonus assuming it is available to all employees with one or more years service (years Service) Employees with pay grade 1 get 3,000, employees with pay grade 2 get 6,000 employees with pay grade 3 get 8,000 if they are not eligible display NE if the pay garde is not 1,2, or 3 then let the cell show Invalid pay grade. PLease Please help me with understanding how to insert the nested if function to get this to work.

Tina

Short Question: Can you have a cell automatically display the written form of a dollar amount? Example: Estimate is for \$2050.00 – can the line below automatically convert to display Two Thousand Fifty and 00/100?

Ricemutt

@Tina – Unfortunately, no, Excel doesn’t have a default function that converts numbers into words. If you search for it, though, someone’s written a macro you can use (if you’re comfortable using VBA). See the official MS Excel help page here.

Tina

I figured but thought it was worth a shot. I’ll give this macro a shot – THANKS!!!

TOMMY

really need help with this statement im trying to do…
=IF(AND(\$F\$2>=3000,\$F\$2=3000,G2<=3500),0,1)
can you help ?

TOMMY

it didnt all show here !!, have you an email address i can contact you on ?
thanks

Roy

Need help:
The commission is 6% on all sales that are at least 20% above cost.

i dont get it :(

Jagjit kooner

It is very helpful for me to understand the if function.I tried the example on a Excel worksheet;its works well.

Thanks

Sebastian

Hi,
Thank you for all the help already posted regarding if statements in Excel. However, I have a problem. I have designed a drop down function in one colum with the options of Yes or No. And I would like to translate the Yes to 3 and No to 0 in a different column. My logic is IF(A3=”Yes”; 3, A3=”No”;0) I cannot get the No part. I would greatly appreciate any help.
Sincerely,
Sebastian

Milind

I want to know how to use if command in following data

Exceeds Rs 2500 but do not exceed Rs 3500 deduct Rs 60/-

Rs 3501 but do not exceed Rs 5000 deduct Rs 120/-

Rs 5001 but do not exceed Rs 10000 deduct Rs 175/-

Above Rs 10001 deduct Rs 200/-

Sharon

Hello trying to do an if statement on age groupings. example if age is betwen or equal to 1 -4 then show “1-4, if age is beetween 5& 9 then show “”5-9 all the way to 85+

Thanks

John Gray

Is it possible to use a list as part of a function. For example,
if cell B2=Australia, list exp1,list exp2.

Poonam

I am working with a report where in i have to compare the figures of Two Financial Years for 8 offices. The formula which i am using is (2008-2007)/2007. it works till we have all the figures i mean to say if 2007 comes somewhere zero it the value will be zero as any thing divided by Zero is Zero. so can any body help to put the if formula here to divide 2007 number only if its 1 or more than one.

Thanks & Regards,

Poonam

Cebene

I am trying to write a function in which if cell A1=June 2009 it will display the formulas from the June 2009 workbook. If the date in cell A1=July 2009, it will display the formulas from the July 2009 workbook. is there a way to write this? Thanks!

abd al rahman

plz i know nothing about how to use excel function or formulas
can you prvise me some usefull links

Prathap

Appricate your help to solve the below issue:

I have numbers in one cell and aplhabets on the another cell. Know i need if my aplhabet is satified then copy the numbers and paste it in different cells. Example in given below:
12345 Pay
12434 Col

Sregei

using the if function,this has become a fustrating problem for me.
A1 value 1;A2 value 3;A3 value 5.the first part of the formula
A4=min(a1:a3) result 1(Which would be inA4.A5 the result that I’m looking for would be 2,which is gotten from taken the smallest value and subtracting from the next smallest value. When you compaire all the values(1,3,5).Again A6 the result I’m looking for is 2;Which would be the result of subtracting the value 3 from the value5.Now what i’m looking for is if when I need to change the values(1,3,5) to say (8,2,4) the formula needs to be so that regardless of what values I use;whether the value is an even numbers or odd.It would do the calculation as describe above.

Nick D

Hi there,

I need some help with the following formula. I know it’s easy but I know it’s a long formula and I can’t seem to figure it out. It goes like this.

If my sales people, hit a certain amount in net sales sales, than the bonus that they receive should change according to the target that they hit.

Net Sales Bonus
1,000 25
1,500 50
2,000 75
2,500 100
3,000 125
3,500 150
4,000 175
4,500 200
5,000 225

So if they hit \$ 1,000 they receive \$ 25, if they hit \$ 1,500, they receive \$ 50 etc. The net sales will be cell (D47).

Nick

Alex

Hi there does anybody have a clue why this formula will not work?

=IF(‘[Client Sales Tax Input Form by Phone With Discrepency.xls]Analysis’!\$A\$13>20%,”Yes”,”No”)

All it does is show that text in the cell. I can’t figure out why it won’t compute. It is in a shared document. Could that be involved?

kapil

If i put a dropdown list (having two words) in a column A and i want if i select one word from the dropdown then in colunm B, a particular word is inserted in that cell from a list of two/three words. Is it possible, if so please help me

latoya

If the Minutes are less than 6 then show the word short
If the minutes are greater than 5 and less than 12 then show the word medium
If the minutes are greater than 11 then show the word long

thank you!

Richie

Hi, i have the score of a football match in two different cells, i.e
cell A1 has the home score – 2 goals.
cell B1 has the away score – 1 goal.

In cell C1 I want to display whether the match was a home win, an away win or a draw.

But i’m struggling to create a formula that does this, any help would be much appreciated. cheers

Paul H

I’m struggling with the IF formulae! I am using it with dates the same as you say above my problem occurs when the info is equal to as below:-

=if(10+1>11,(true),(false))

to me that should come to false but it doesn’t with dates for some reason is there something I am doing wrong! Please help as I am very frustrated with this sheet I am doing but would hate to have to throw away a lot of hard work!

Rakesh Kushwaha

please tell me excel bonus farmulas
Example:-
3500*8.33%=
4500(3500)*8.33%=
2500*8.33%=

Frank McMahon

Hi,
I have a question about how (or if it is possible) to increment a counter with a value in a cell based upon the existence of an “x” in another cell.
I can figure out a clunky way to do it but I’m sure there is a much better way.
I’d greatly appreciate your assistance. I could send you part of the worksheet so you could see the actual structure.
Please let me know if this is possible.
Thank you for posting such informative and helpful information.

Best Regards,

Frank

Kerry

I am struggling with writing an IF statement. I am using it with dates in column C that if over 7 days I want highlighted Yellow in column A, if over 14 days highlighted Gold, and if over 21 days highlighted Red.

Any help would be appreciated. Thanks.

Shalaka

Hi
i am Calculate Salary the Profession tax fill up manually thats why i need the formula of ” IF ” in Profession Tax is Calcualte on Gross Salary If Gross Salary is Betwen in 5000 to 10000 then PT is Deduct 175/- & Gross Salary is more than 10000 then 200/- dedut. Wht formula use pls reply me.
Thanks

John

I am a ham & egger with excel, doing basic stuff. ESpecially after seeing what you all know. I am at the last stage on a spreadsheet & lost in how to write the formula. Once it’s correct I want to copy it into the following 10,000 cells in the same column with all cells in the formula staying exactly the same. Any help would be much appreciated. The formula I need to write is:
IF cell G6 is less than cell A2 multiply cell G6 x cell C2 IF cell G6 is more than Cell A2 Enter value in cell D2.

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
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

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.

Lorraine Ryan

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

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

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