Watch the video extract as seen on YouTube, then read the full tutorial below.

Click the Full Screen button on the player to watch it in HD.

Excel IF Function Explained

In this Microsoft Office Online Training tutorial we’re going to explain how to use the IF function (also known as IF Statement), and look at a couple of different applications for it.

With the IF statement you can tell Excel to perform different calculations depending on whether the answer to your question is true of false.

The function wizard in Excel describes the IF function as:

=IF(logical_test,value_if_true,value_if_false)

But let’s translate it into English and apply it to an example:

In the table below we want to calculate a commission in column G for each Builder based on the number of units in column D.

Excel-IF-Statements-Explained

We’ll say that for units over 5 we’ll pay 10% commission based on the Total $k figure in column F, and for units of 5 and under we’ll pay 5% commission.

Our IF statement for row 2 would read like this:

=IF(The number of units in cell D2 is >5,Then take the Total $k in cell F2 x 10%, but if it’s not > 5 then take the Total $k in cell F2 x 5%)

The actual formula we would enter into Cell G2 would be:

=IF(D2>5,F2*10%,F2*5%)

Remember; as the number of units in row 5 is not greater than 5 the formula would calculate a 5% commission.

Other applications of the Excel IF Function

We don’t have to use the IF function to perform a calculation.  We could use it to return a comment.   If we take the previous example again, we could have asked Excel to put a note in the cell like ‘Pay 5%’ or ‘Pay 10%’.  To do this our IF formula would look like this:

=IF(D2>5,"Pay 10%","Pay 5%")

Notice the difference between the two formulas are the inverted commas () surrounding the results we want Excel to produce.  These inverted commas tell Excel that the information between them is to be entered as text.

Below is a screen shot of how the formula looks in the Formula Bar and the result returned in column G.

Excel-IF-Statements-Explained

Try other operators in your IF function

Because the IF formula is based on logic, you can employ tests other than the greater than (>) operator used in the example above.

Other operators you could use are:

  • =             Equal to
  • <             Less Than
  • <=           Less than or equal to
  • >=           Greater than or equal to (if we’d used this operator in our above example row 5 which had 5 units would have returned Pay 10%)
  • <>           Less than or greater than

Now practice. Copy and paste the formulas used in these examples into your workbooks to practice, or download the spreadsheet used in the example above.

Want More Excel Formulas

Why not visit our list of Excel formulas. You’ll find a huge range all explained in plain English, plus PivotTables and other Excel tools and tricks. Enjoy :)

Plus sign up for our newsletter below and receive weekly tips & tricks to your inbox, plus you’ll get our 100 Excel Tips & Tricks e-book free.

Did you find this useful, or did it just confuse you?  Let us have your comments below.

Share This

Please share this or leave a comment and I'll make sure you get a personal reply.

Leave a Comment

Current day month ye@r *

{ 141 comments… read them below or add one }

zulfiqar July 2, 2014 at 5:48 pm

In if function I chosen some part of the text for logical test which is not accepted by Excel
Exampl

Reply

Mynda Treacy July 3, 2014 at 11:08 am

Hi Zulfiqar,

Sorry to hear you’re having trouble with an IF Function. If you can give me an example I might be able to help.

Mynda

Reply

Jerry Roth October 5, 2013 at 3:56 am

I have 5 cells (C5, C6, C7, C8, C9) that are being populated with numbers driven by if statements. I am now trying to sum all the colums utilizing the =sum(C5:C9) formula. The columns are populate with 4, 6, 2, 0 and 5 respectively and they are formated as a number. I would expect the result to be 17 but it is returning 0 as the result. What am i doing wrong?

Reply

Mynda Treacy October 5, 2013 at 5:13 pm

Hi Jerry,

It sounds like the values that the IF function is returning are actaully text as opposed to numbers.

If the numbers in your value_if_true and value_if_false arguments are in double quotes then Excel is returning text. e.g.

=IF(A1>0,"5","1")
 would return numbers that are actually text whereas:

=IF(A1>0,5,1)
 would return numbers.

I hope that helps. If you're still having problems please send me the workbook via the help desk so I can see what you're working with.

Kind regards,

Mynda.

Reply

Gireesh October 5, 2013 at 1:23 am

=IF(F23=19300,20,IF(F23>18500,19+(F23-18500)/800,IF(F23>17200,18+((F23-17200)/1300),IF(F23>12300,17+((F23-12300)/700),IF(F23>9900,10+((F23-9900)/600),IF(F23>8400,6+((F23-8400)/500),IF(F23>7200,3+((F23-7200)/400),IF(F23=7200,1))))))))

I want to make a series of 7200-7600-8000-8400-8900-….. -19300 how can i use this formula , its not working there . plz tell me sir !

Reply

Mynda Treacy October 5, 2013 at 5:19 pm

Hi Gireesh,

When I enter your formula into my worksheet it doesn’t return an error and appears to calculate each level correctly. When I say ‘correctly’ what I did was enter values at each logical test level and check that the result reduced, which it did.

What results are you expecting exactly? I don’t know what you mean by ‘make a series’. Your IF formula returns the calculation in your value_if_true arguments.

Perhaps you can send me an example workbook via the help desk.

Kind regards,

Mynda.

Reply

Scott September 6, 2013 at 8:53 am

Hi Mynda -

I was wondering if you might be able to help with this … I’m trying to create a credit card authorization form that when choosing “AX”, “MC”, or “VI” from a drop-down in cell B1, will adjust the custom formatting in B2 to either ####-####-####-#### for VI or MC, and ####-######-##### for AX. Any thoughts? Thanks in advance.

Reply

Mynda Treacy September 6, 2013 at 2:13 pm

Hi Scott,

You can’t toggle between custom number formats using a formula. You’d have to use VBA to do this.

Also, you can’t display 16 digits in a cell with spaces or hyphens and keep it as a number. This means you can’t type in the 16 digits consecutively and have Excel reformat it in the same cell (without VBA).

You’d have to type your card number in another cell and then use this combination of LEFT, MID and RIGHT functions to reformat the number in a separate cell:

=LEFT(A1,4) & " " & MID(A1,5,4) & " " & MID(A1,9,4) & " " & RIGHT(A1,4)

You could use an IF function (say in cell B1) with the above formula that tests the length of the card number in cell A1 and applies the above formula for 16 digits and a variation for 15 digits for the Amex. e.g.:

=IF(LEN(A1)>15,LEFT(A1,4) & "-" & MID(A1,5,4) & "-" & MID(A1,9,4) & "-" & RIGHT(A1,4),LEFT(A1,4) & "-" & MID(A1,5,6) & "-" & MID(A1,1,5))

I hope that helps.

Kind regards,

Mynda.

Reply

Scott September 7, 2013 at 5:03 am

Thanks, Mynda. Although I wasn’t really wanting to add another cell for the data fill, then having it format in yet a different cell, I will try to figure a way to make that work. I appreciate your skills.

On another note, the formula and formatting work great, but for some reason that I can’t seem to grasp is why the data cell (for the card # input), which is only formatted as number without decimals and data validation text length of 15-16, when 16 digits are entered it changes the last one to “0″ (i.e., 4128111122223333 becomes 4128111122223330 and formats as 4128-1111-2222-3330).

Any thoughts? Thanks

Reply

Mynda Treacy September 7, 2013 at 2:26 pm

Hi Scott,

Excel only stores 15 significant digits in a number. It changes digits after the fifteenth place to zeroes. This is why the last digit in a 16 digit credit card number is changed to a zero, and is also the reason you can’t use a custom number format for your credit card numbers.

There is a more in depth explanation on numbers in Excel here.

Your only options are to either have the number input as text with the hyphens typed in at the same time, or use a formula in another cell to convert the number to text and format it how you want.

Kind regards,

Mynda.

Reply

Lincoln McCauley August 22, 2013 at 7:24 am

Hi Mynda

This is by far the best site I have ever visited.
Thanks for all your hard work. Could you please put a VBA course on your site. I would’nt mind paying for the tutorials. I am new to programming and would like all the help I can get.

Reply

Mynda Treacy August 22, 2013 at 2:15 pm

Wow, thanks Lincoln :)

We’re currently working on a VBA course. I’ll let you know when it’s ready.

Kind regards,

Mynda.

Reply

abdulwaheed adeyemi August 9, 2013 at 3:15 am

this is my first time on this site, and i can say with all sincerity, is one of the best site have ever seen. keep the good work going mynda…. more power to your elbow

Reply

Mynda Treacy August 9, 2013 at 9:11 am

Hi Abdulwaheed,

Thank you very much for your kind words :) I appreciate it, and my elbow is feeling stronger already!

Mynda.

Reply

rose June 22, 2013 at 12:33 am

thanks ,was useful.but i need to show me in fun and animated way if possible as i cannot getiteasily,meanwhile i need it for work seriously.
Rdgs,
Rose

Reply

Mynda Treacy June 22, 2013 at 2:49 pm

Hi Rose,

If you prefer video tutorials you might like to look at our Excel Training course which is all video tutorials.

Kind regards,

Mynda.

Reply

Roch.Mbia June 6, 2013 at 4:51 pm

this is fabulous!

Reply

Mynda Treacy June 6, 2013 at 5:24 pm

Thank you, Roch :)

Reply

Danyel April 28, 2013 at 1:17 am

Hello,

I am trying to figure out what is wrong with my if and then statements in this equation.

= IF ((F6 >=.80), F6 x (-1)), IF ((I6=0,781350),(((0.8-F6)/(I6))*(SUM(C10:D10))/(1-((0.8-F6)/(I6)))))

Everything after (-1)), works but the new IF command I put in is not working. I am trying to say IF cell F6 is greater than or equal to .80, then multiple F6 by -1.

If you have a chance can you please help with this? Thanks so much.

Reply

Mynda Treacy April 28, 2013 at 8:12 pm

Hi Danyel,

It should be like this:

=IF(F6>=0.8,F6*-1,IF(I6=0,781350,0.8-F6/I6*SUM(C10:D10)/1-0.8-F6/I6))

BTW, I also removed some brackets that weren’t required.

Kind regards,

Mynda.

Reply

nelly thabiso April 12, 2013 at 8:19 pm

hi i have little confused about IF Statement calculation

Reply

Carlo Estopia April 13, 2013 at 6:31 pm

Hi Nelly,

what part of IF are you confused about?
send it here: HELP DESK.

Cheers,

CarloE

Reply

Harihara Achary April 8, 2013 at 2:45 am

Can u help me is there any formula which autimatically change the dates from moth first to end according to the month if i will put the month in A1 ?

Reply

Carlo Estopia April 8, 2013 at 2:40 pm

Hi Harihara,

To avoid complications, please send a mock file that will
illustrate what you want via help desk.

Cheers,

CarloE

Reply

Trevor Carpenter April 5, 2013 at 12:36 pm

I want to look up a value say 0.75, which is between cells, A1 has 0.5 and cell A2 has 1.0 and return a value from cell B1, as this is the lower value.

Reply

Carlo Estopia April 7, 2013 at 12:00 am

Hi Trevor,

I really don’t think there’s a specific look-up for this type of setup ; hence,
I need you to send your file via help desk.
I need to see a concrete example of your data.

Cheers,

CarloE

Reply

Farhan Ali March 29, 2013 at 5:00 pm

I would appreciate if you could guide me that I am using correct formula for below condition

I am trying to create a formula for if condition to check if an event occurs to a particular day between 17:00 hours to next day 03:00 hours then show Case 2

case 2
Reporting time is between 17:00 hours to next day 03.:00 hours

=IF(AND(DAY(A2),HOUR(B2)>=17,(DAY(A2)+1),HOUR(B2)<3),"CASE4")

Reply

Carlo Estopia March 30, 2013 at 11:04 pm

Hi Farhan,

Pardon me, But your requirement is not clear to me.

Please send a file through HELP DESK and create some scenarios or example
So I can understand this one.

Cheers,

CarloE

Reply

elbert March 27, 2013 at 1:40 am

good day!
i would like to seek for your help on your spreadsheet i am working on. i would like to get the value to appear on a single cell for the following logics to consider:

if A1 is blank then check B1, if B1 is blank then check C1, if C1 has a value the cell value will be the value on C1 example1: A1=BLANK, B1=BLANK, C1=PCS the value on the cell that will appear is PCS from C1 example2: A1=BLANK, B1=PCS, C1=BLANK the value on the cell that will appear is PCS from B1 example3 A1=PCS, B1=BLANK, C1=BLANK the value on the cell that will appear is PCS from A1

if either or all of A1, B1 or C1 has a value, the cell value will consider any of the values on A1, B1, C1 considering A1, B1 and C1 are of equal value
example A1 = PCS, B1 = PCS & C1 = PCS thus th value that appears on the cell id PCS

if either one of the A1, B1 & C1 has no value considering still that the value for A1=B1 or B1=C1 or C1=A1 the cell value will take either of the value found on A1, B1 and C1 example A1=PCS, B1=BLANK, C1=PCS the value that will appear on the cell will still be PCS.

hoping you could help me on this, am using microsoft excel 2003. thanks in advance

Reply

Carlo Estopia March 27, 2013 at 9:30 pm

Hi Elbert,

Please do send your concerns via HELP DESK.

Cheers.

CarloE

Reply

pradip March 26, 2013 at 6:30 pm

I have a question, I need a formula in MS Excel 2010 to perform the following example: =if(D5=23,then(H5>11,(I5+I5*11%),if(H5>9,(I5+I5*5%),if(D5=37,then(H5>11,(I5+I5*10%),if(H5>9,(I5+I5*5%)))). I need to do this for 13 dfferent enteries in whatever contains, is that possible? Please help!

Reply

Carlo Estopia March 26, 2013 at 10:15 pm

Hi Pradip,

You need to describe this properly.

Why don’t you send your file via HELP DESK.

Cheers.

CarloE

Reply

Catherine March 20, 2013 at 12:48 pm

Very useful tips, and easy to understand.

Reply

Carlo Estopia March 21, 2013 at 1:37 am

Hi Catherine,

On Behalf of Mynda,
Thank You!

Cheers.

CarloE

Reply

Linear March 20, 2013 at 9:01 am

I have a question, I need a formula in MS Excel 2010 to perform the following example: If(A2=1, then (d2<8,5,if(d2<=10,4,if(d2<=12,3,if(d213,1,if(a2=2,then(d2<6,5,if(d2<=8,4,if(d2<=10,3,if(d212,1,if(a2=3,then….. I need to do this for 13 dfferent enteries in whatever A2 contains, is that possible? Please help!

Reply

Carlo Estopia March 21, 2013 at 8:35 am

Hi Linear,

I have been figuring out this one, but honestly I really couldn’t get what you want here.
Please send a file via HELP DESK with sample data and results of your logic here.

Cheers,

CarloE

Reply

ali hassanein March 6, 2013 at 11:55 pm

IF(I515003000<5001;I5*12.5%;I5*10%))) is this troe

Reply

ali hassanein March 7, 2013 at 12:26 am

I FIND IT
IF((I5>5000);(I5*0.1);IF((I5>3000);(I5*0.125);IF((I5>1500);(I5*0.15);(I5*0.2))))

Reply

Carlo Estopia March 7, 2013 at 2:35 pm

Hi Ali,

Good that you found it.

Anymore questions?

Cheers.

CarloE

Reply

Chamnan Rodpai March 5, 2013 at 11:54 pm

Very good explanation,thankx for your kind.

Reply

Howard February 21, 2013 at 1:37 am

this is very helpful – thanks

Reply

Mynda Treacy February 21, 2013 at 7:47 am

You’re welcome, Howard :)

Reply

Gene Papin February 19, 2013 at 1:07 am

All I want to know is how to divide my list of expenses for my hobby into categories for my accountant. (Such as office supplies, tools, etc.

Reply

Carlo Estopia February 19, 2013 at 5:08 pm

Hi Gene,

I kind of don’t get your excel problem here.
So why don’t you send your file to us through
HELP DESK so we can have a good look at it.

Cheers.

CarloE

Reply

fredy liem February 16, 2013 at 12:20 am

Does any one can help me, if i want to fill the data and number without type one by one. Which formula do i need to use, for example if i am typing strawberry, the price automatic come out, from the price list which is kept in other in the other list.
Thank you

Reply

Carlo Estopia February 16, 2013 at 12:22 pm

Hi Fredy,

All you need is VLOOKUP and IF.

Assumptions:
Fredlist – is your Sheet where your masterlist is
CurrentSheet – where your formula is

Fredlist
—-A——- —-B——
1 Strawberry….3…….
2 Apple………4…….
CurrentList
—-A——- —-B——
1 InputCell…Vlookup Formula
2 InputCell…Vlookup Formula

Here’s what you’re going to do,
Copy and paste the formula below.

VLOOKUP(A1,FredList!$A$1:$B$2,2,FALSE)

Please note that your lookup Value is in A1 in this example. You can adjust it depending where your
input cell (where you type your value) is.Also the table array is only from A1 to B2, You can also
expand it depending on how big your price list is from the other sheet. 2 is the column to return.
Since your price is in column B then it’s in the second column starting from A. FALSE simply means
Exact Match.

Vlookup may return an error.
So If you want to avoid the error,
you can have this formula instead.

=IF(ISERROR(VLOOKUP(A1,FredList!$A$1:$B$2,2,FALSE)),"",VLOOKUP(A1,FredList!$A$1:$B$2,2,FALSE))

Read More: IF FUNCTIONS
VLOOKUP

Cheers.

CarloE

Reply

Abuthahir Aboobacker February 1, 2013 at 1:34 pm

It’s very useful to me

Reply

Mynda Treacy February 1, 2013 at 8:52 pm

Cheers, Abuthahir :)

Reply

Art January 26, 2013 at 7:16 am

Perfectly done….very straight forward presentation, even the beginners won’t get lost. Thanks

Reply

Mynda Treacy January 26, 2013 at 2:10 pm

Cheers, Art :)

Reply

Elnes January 25, 2013 at 6:27 pm

Thank you very much. I am a bit of a novice and found the tutoral about the IF Statement very informative. Keep up the good work and God bless.

Reply

Mynda Treacy January 25, 2013 at 9:01 pm

Thanks, Elnes :)

Reply

NLN January 18, 2013 at 5:48 pm

Very useful and easy to understand.

Reply

Mynda Treacy January 19, 2013 at 1:11 pm

Cheers, NLN :)

Reply

Asmae January 17, 2013 at 9:49 pm

Thanks that is very clear, but if we have 2 conditions (if and or) at the same time

Reply

Carlo Estopia January 22, 2013 at 2:08 pm

Hi Asmae,

I couldn’t find a previous discussion with regards to your question. Please clarify it further and I would gladly appreciate it.

Sincerely,

CarloE

Reply

Mohammed Sofi January 7, 2013 at 10:14 pm

Dear Sir,

My online training is really helpful to day day activities. I have Learn most of the things like Pivot, Vlookup, H Lookup and Sumproduct.

I really thanks to you.

Thanks & Warm Regards

Your online student.

Mohammed Sofi.

Reply

Mynda Treacy January 8, 2013 at 7:32 am

You’re welcome, Mohammed :)

Reply

Brenden January 6, 2013 at 12:35 pm

Hello,
I’m trying to highlight a cell with a numerical value if it is not +or- 4 of an adjacent cell. Any help would be great.
Thank you.

Reply

Mynda Treacy January 6, 2013 at 7:04 pm

Hi Brenden,

I’m not sure how you want to highlight a cell. Were you thinking of Conditional Formatting? Either way you can use this formula where your value you are testing is in A1:

=A1=MEDIAN(A1,-4,4)

This will return a TRUE or FALSE outcome.

Kind regards,

Mynda.

Reply

bob mcgynn January 5, 2013 at 4:33 am

nested IF’s ?

Reply

Mynda Treacy January 5, 2013 at 8:14 am

Bob,

Nested IF’s here.

Kind regards,

Mynda.

Reply

ashik December 20, 2012 at 3:23 pm

Best excel site I have been to ….thanks !!

Reply

Mynda Treacy December 20, 2012 at 4:05 pm

Cheers, Ashik :)

Reply

Shan December 14, 2012 at 5:20 pm

Thats very helpful, but can i get weekly emails with (pdf files of learning ) .

Reply

Mynda Treacy December 14, 2012 at 8:55 pm

Hi Shan,

Thanks for your kind words.

Unfortunately the weekly emails cannot come with PDF attachments, but if you click on the link in the emails to comment on the blog posts you can print the blog post as a PDF from the link at the bottom of the page above the comments area.

I hope that helps.

Kind regards,

Mynda.

Reply

Naz December 12, 2012 at 1:53 pm

Hi Mynda,

I am trying to make a formula to copy the cell of another worksheet in the same workbook if it has a value next to it otherwise leave a blank cell. Eg. If cell c4in another worksheet has a value of greater than 0 then i want to display what is in cell C3 in that same worksheet other wise leave it blank. Is this possible with an IF statement?

thanks

Reply

Mynda Treacy December 12, 2012 at 4:15 pm

Hi Naz,

Yes, this is possible.

=IF(Sheet2!C4>0,Sheet2!C3,"")

Kind regards,

Mynda.

Reply

Naz December 20, 2012 at 10:01 am

thanks so much! This has helped alot.

Reply

Mynda Treacy December 20, 2012 at 4:04 pm

:) Great.

Reply

tony November 20, 2012 at 4:16 am

i want to have more ideas on how to use the excel.

thanks.

Reply

Mynda Treacy November 20, 2012 at 6:58 pm

Hi Tony,

You can find an index of Excel formulas and other tools here.

Kind regards,

Mynda.

Reply

joseph November 12, 2012 at 5:39 pm

kudos, excellent job

Reply

Mynda Treacy November 12, 2012 at 10:33 pm

Thanks, Joseph :)

Reply

linda October 26, 2012 at 11:31 pm

That was so easy to understand. thanks

Reply

Mynda Treacy October 28, 2012 at 4:46 pm

:) Thanks, Linda. That’s kind of you to say.

Reply

Sanat October 16, 2012 at 7:25 am

This is too good dear

Reply

Lou October 4, 2012 at 1:56 pm

Best site for Excel wannabe experts!

How would I display current attendance points?
Date and points are entered on Employee worksheet. Points worksheet has dates down column A and names across row 1. Points are displayed in column below each employee’s name in row for correct date. Formula on Employee worksheet should return current points in past 365 days from Points worksheet.

Thank you!!!

Reply

Mynda Treacy October 4, 2012 at 3:06 pm

Hi Lou,

The way your data is structured limits the solutions available, however I think perhaps the easiest is to use a DSUM function like this:

=DSUM($A$1:$D$10,H1,$F$1:$G$2)

Where A1:D10 is your data table, H1 containes the name of the employee you wanted to lookup, and cells F1:G2 contained your date criteria.

You can read more about the DSUM and other database functions here.

I hope that helps.

Kind regards,

Mynda.

Reply

Jacob September 26, 2012 at 1:44 am

very good explanation of IF statement. easy to understand.

Reply

Mynda Treacy September 26, 2012 at 7:35 am

Cheers, Jacob :)

Reply

Bernie Squire September 20, 2012 at 2:32 pm

Hi, I’m trying to find a formula that will do the following:

Calculate the amount of commission a person is due to be paid on an ascending scale e.g. based on the amount entered into a particular cell (lets say $30,000) I need the formula to be able to pay at 18% up to $10,000 – 35% from $10K to $15K and 45% >$15,000.

I thought I could use an IF statement but I’m having trouble getting one to do what I need???

Are you able to help me please?

Reply

Mynda Treacy September 20, 2012 at 6:35 pm

Hi Bernie,

I think it’s better to use a VLOOKUP on a sorted list for this type of calculation because it’s better to house your commission rates in a separate table that you can easily update. However, if you really want to use an IF statement then this is how you’d do it:

=IF(A1< =10000,A1*18%,IF(A1<=15000,A1*35%,A1*45%))

Kind regards,

Mynda.

Reply

Sachin September 14, 2012 at 2:54 pm

Excellent

Reply

Mynda Treacy September 14, 2012 at 3:27 pm

Cheers, Sachin :)

Reply

Angie September 8, 2012 at 6:33 am

Hi, I am trying to create a formula that would make C3=5 if B3 has anything typed in it. How do I do that?

Thank you!!!

Angie

Reply

Mynda Treacy September 8, 2012 at 2:59 pm

Hi Angie,

=IF(B3<>”",5,”")

The above formula in English reads:

If B3 is not <> blank “”, then return 5, otherwise leave blank “”.

Kind regards,

Mynda.

Reply

Maile Uluave September 4, 2012 at 3:22 pm

I found excel is a very hard module that I also do not like and found this training hub very helpful and I am still trying to learn more and find more support for my study. This training explains more details of my misunderstood words in excel.

Reply

Mynda Treacy September 4, 2012 at 5:03 pm

Thanks, Maile. Glad to have helped you out.

Reply

M August 31, 2012 at 8:50 pm

Hi,
i have come across a problem where i need to change a particular cell number in an excel formula to a new cell number where ever that number is coming in the formula (note:cell number only)
eg here is the formula lets say master formula
=IF((Q8-$Z$6)>21,Z8*60,IF((AND((Q8-$Z$6)>14,((Q8-$Z$6)<22))),Z8*30,IF(((Q8-$Z$6)21,AA8*60,IF((AND((Q8-$AA$6)>14,((Q8-$AA$6)<22))),AA8*30,IF(((Q8-$AA$6)=$AB$6),IF((Q8-$AB$6)>21,AB8*60,IF((AND((Q8-$AB$6)>14,((Q8-$AB$6)<22))),AB8*30,IF(((Q8-$AB$6)=$AC$6),IF((AND((Q8-$AC$6)>14,((Q8-$AC$6)<22))),AC8*30,IF(((Q8-$AC$6)=$AD$6),(IF((Q8-$AD$6)>21,AD8*60,IF((AND((Q8-$AD$6)>14,((Q8-$AD$6)<22))),AD8*30,IF(((Q8-$AD$6)<=14),AD8*20)))))

and where ever the number 6 is coming in the above formula, i want to replace it with 102 keeping the rest formula same. I'l b glad if u could help me out.
Regards

Reply

Mynda Treacy September 2, 2012 at 5:12 pm

Hi M,

1. Select the cells containing the formulas you want to change.
2. CTRL+H to open Find and Replace:
3. In the Find field enter: $6
4. In the Replace field enter: $102

Kind regards,

Mynda.

Reply

M September 6, 2012 at 4:42 am

Mynda i can’t imagine how you respond to all these queries so promptly, all my appreciation for that.
i want help in another thing, i want to use the following three formulas together such that if any one of the formula holds for the cell AA16, then it gives that respective value.so my question is how these can be combined when i know that Z17 can take either of the three scenarios but not all or even two at a time.

IF((Z17>=0),(AA16))
IF((Z17=-1),(AA16-Z17-N17))
IF((Z17=”"),(AA16-N17))

best regards

Reply

M September 6, 2012 at 5:05 am

i also want to know is there a way to bring in a row certain values from a range of cells..i’l explain, lets say in column B i have a range of dates say like this
5-july
5-july
5-july
5-july
8-aug
8-aug
10-sep
10-sep
i want to make a formula, that will make for me a row (using aforementioned column) 5-july 8-aug 10-sep placed in cells E6 to G6.so my question is, can i get single values of all the different dates present in the columnB in row6 without giving a command to specifically look for 5-july 8-aug 10-sept, what it should look for is rather single values of different dates present and bring them in ascending order in the row.
i’m sorry for not being concise.
many thanks

Reply

Mynda Treacy September 6, 2012 at 1:40 pm

Hi again, M,

Let’s say your dates are in cells A2:A9. Enter this formula in cell B1:

=IFERROR(INDEX($A$2:$A$9,MATCH(0,COUNTIF($A$1:A1,$A$2:$A$9),0)),"")

This is an array formula so you need to enter it with CTRL+SHIFT+ENTER

You can then drag to copy the formula across the remaining columns.

Kind regards,

Mynda.

Mynda Treacy September 6, 2012 at 1:14 pm

Hi M,

You’re almost there with your formula. It’s:

IF(Z17>=0,AA16,IF(Z17=-1,AA16-Z17-N17,IF(Z17=”",AA16-N17,0)))

Kind regards,

Mynda.

Reply

M September 6, 2012 at 3:28 pm

Thanku so very much Mynda.
i am somehow encountering a problem when i enter these formulae.
excell pops up a box for both the formulas that “formula contains an error”. i’l be grateful if u can give it another look.

kind regards

Dan Kim August 18, 2012 at 2:13 am

thanks. very succinct.

was wondering about how to do the following…

I want a cell to be outlined with a border if the value of a specific different cell is less than or equal to 40%…

If $C$7 <=40%… not sure about the rest. btw…using excel07

thanks in advance

Reply

Mynda Treacy August 19, 2012 at 9:41 pm

Hi Dan,

You can do this with Conditional Formatting.

Kind regards,

Mynda.

Reply

jack August 11, 2012 at 10:57 pm

pls provide me the pdf

Reply

Mynda Treacy August 12, 2012 at 9:09 am

Hi Jack,

I’m sorry, we don’t have a PDF of the above tutorial. You could try printing the page as a PDF if you wanted to keep it offline.

Kind regards,

Mynda.

Reply

Melissa July 25, 2012 at 6:26 am

How do I use the “IF” Statement to determine how many days are in between two cells and if they are more is it possible to add an “X” next to those that are?

Reply

Mynda Treacy July 25, 2012 at 8:35 am

Hi Melissa,

You can use the DATEDIF function to calculate the number of days between two dates.

Let’s say you want to know if the number of days between the date in cell A1 and date in cell B1 is > 5. If it is put an “X” if not enter nothing “”, you’d use this formula in column C:

=IF(DATEDIF(A1,B1,”D”)>5,”X”,”")

I hope that helps.

Kind regards,

Mynda.

Reply

Shyam September 2, 2012 at 4:43 pm

Hey Mynda,

Its absolutely made the way I use excel easier….it looks so beautiful
Hah….nonetheless, I couldn’t find a clue as to why “D” in the formula – =IF(DATEDIF(A1,B1,”D”)>5,”X”,””) – is used. I tried to put a different alphabet, but it didn’t work. Could you please shed some light on the role of “D” in the formula in question?

Thank u

Reply

Mynda Treacy September 2, 2012 at 5:15 pm

Hi Shyam,

The ‘D’ in the DATEFIF function indicates that you want to calculate the number of days. You can find out the other ‘letters’ that work with the DATEDIF Function here.

Kind regards,

Mynda.

Reply

nancy July 13, 2012 at 4:13 pm

I am looking for help I want to use the if formula but I want the if to post the whole row on a separate tab in the workbook with 5 different options keeping the information on my work sheet so as I add information the information will change on the other tab ….link …..maybe that would be better

Reply

nancy July 13, 2012 at 4:17 pm

Or a macro?

Reply

nancy July 13, 2012 at 4:25 pm

=if(column b) is Shoes row 22 will post. Information on shoe tab and keep in shipping date order (column f)

If that in not possible can you direct me to where I should be asking or am I totally asking for the impossible

Reply

Mynda Treacy July 15, 2012 at 10:48 am

Hi Nancy,

I have many questions about what you’re trying to do which would be best answered by you sending me the file so I can see exactly how your data is laid out and what you mean by ‘keep in shipping date order’ etc.

Please go to the help desk and fill out a ticket and attach your file with the example and instructions of what you want to do.

Thanks,

Mynda.

Reply

Mohammed Shukur Sheikh May 1, 2012 at 5:00 am

Sir, It is good and excellent explained and I would like to say nobody explain like this.And further I hope you would explain all the difficult problems and the equations speciaaly

Reply

Mynda Treacy May 1, 2012 at 9:19 am

Thanks, Mohammed :)

Reply

faisal bashir April 13, 2012 at 4:12 am

Video tutorial is the most effective way of learning with enjoyment.
Thank you so very much Mynda treacy and team for your excellent vision, efforts and the lust to spread knowledge.

Wishing you all the best

Regards,
Faisal Bashir.

Reply

Mynda Treacy April 17, 2012 at 9:43 pm

Thanks Faisal. I sincerely appreciate your kind words.

Mynda.

Reply

Rose Tegg March 20, 2012 at 8:27 am

I am learning so much, your explanations are very easy to follow. I have enjoyed doing the learning the IF statement. Thank you

Reply

Mynda Treacy March 21, 2012 at 4:48 pm

Thanks, Rose :)

Reply

Ashit Shroff February 24, 2012 at 3:32 am

Cool stuff very useful.
Explained clearly and nicely.
Thanks.

Reply

Mynda Treacy February 24, 2012 at 8:52 am

Cheers, Ashit :)

Reply

charles December 23, 2011 at 2:43 am

how do I test to see if a number falls within a range and how do I write the formula? should I use the IF AND function?

Reply

Mynda Treacy December 23, 2011 at 7:52 pm

Hi Charles,

You could use a formula like this:

=IF(AND(A1>=5,A1<=10),"TRUE","FALSE")

Which says, IF the value in cell A1 is between 5 and 10 then return 'TRUE', if not return'FALSE'.

I hope that's what you're after.

Kind regards,

Mynda.

Reply

vickie October 4, 2011 at 2:46 am

great stuff!

Reply

Mynda Treacy October 4, 2011 at 9:13 pm

Cheers Vickie. I appreciate your feedback.

Reply

Mia Baum April 30, 2011 at 5:58 am

Extremely helpful…this 4 minute video must have just saved me 3 hours. Thanks.

Reply

Mynda April 30, 2011 at 2:32 pm

Thanks, Mia. Glad we could help.

Reply

Romeo Levanza Jr. February 17, 2011 at 10:42 pm

I really appreciate your tutorial video.

God Bless,

Reply

Debbi November 8, 2010 at 3:52 pm

thanks. I’ll try using other operators as you suggest

Debbi

Reply

magnific November 1, 2010 at 5:52 pm

this is the way a blog should be! thanks!

Reply

Mynda November 2, 2010 at 1:58 pm

Thanks. Glad we could help.

Reply

harshman insurance October 5, 2010 at 5:03 pm

This is the BEST page I have read all week!!!

Reply

Philip Treacy October 6, 2010 at 10:56 am

Thx! :) Glad you enjoyed it

Reply

Conor September 1, 2010 at 1:36 pm

excellent explanation, thanks very much, just what I was looking for

Conor

Reply

Ahmed Ahmed September 13, 2010 at 9:47 am

good job. thanx for your efforts, it was very helpful to me to read this page

Reply

Mynda September 29, 2010 at 8:39 pm

@Conor – thanks for your feedback.

@Ahmed Ahmed – Glad I could help.

Reply

godofgore November 25, 2010 at 4:35 pm

cool explanation, if only eveything was this well explained.

Reply

Excel Statement April 21, 2011 at 5:04 am

I agree, I once had problems trying to nest IF functions but this cleared them up.

Reply

Mynda Treacy September 6, 2012 at 3:36 pm

Hi M,

It’s most likely the formatting of the double quotes. I’d delete them and type them in again. When I enter them in the comments the format of them is changed and not compatible with Excel.

Please let me know if that doesn’t fix it.

Kind regards,

Mynda.

Reply

Shan December 14, 2012 at 5:21 pm

thats good……

Reply

Darren March 1, 2013 at 5:02 pm

I have thought about how to ask my question correctly, I believe this more appropriate.

I am using this formula in excel 2010, “=IF(ISBLANK(P7),”",IF(P70,H7-P7)/0.25),IF(I7>P7-I7)/0.25)))”

which is not being accepted and displays a message “too many arguments for this function” with only the last section 0.25 highlighted.

Values only are required to be entered in either H7 or I7 but not both. P7 is the end result of entry from either H7 or I7 with Q7 providing a breakdown of P7 result, the reason for /0.25.

If I enter the formulas individually into Q7, “=IF(ISBLANK(P7),”",IF(P70,H7-P7)/0.25))” the correct result is displayed when a value is entered into H7 & when reversed “=IF(ISBLANK(P8),”",IF(I7>0,P7-I7)/0.25))” with an entry in I7 a correct answer is given as it should, though not with the combined formula as when I join the two I cannot get it to work.

I have tried ending with; ,”"))) & removing P7>0, & I7>0, also tried SUM in place of IF also without success.

What have I done incorrectly?

Reply

Carlo Estopia March 1, 2013 at 7:54 pm

Hi Darren,

Basics First:
The IF function, nested IFs
IF(CONDITION, TRUE VALUE, FALSE VALUE)
IF(CONDITION, TRUE VALUE) —FALSE VALUE is assumed FALSE if left out
NESTED:
IF(CONDITION, TRUE VALUE, FALSE VALUE: another IF(CONDITION, TRUE VALUE, FALSE VALUE)
Note : It’s easy… don’t mess your condition with your true values and false values.

Let’s break your formula down: “=IF(ISBLANK(P7),””,IF(P70,H7-P7)/0.25),IF(I7>P7-I7)/0.25)))”
good:=IF(ISBLANK(P7),””, – IF(CONDITION, TRUE VALUE,
bad:=IF(P70…. what’s your condition here?P7>0?
bad:IF(P70,H7-P7)/.025 … you’re mixing your condition with your true value
however you said it works when isolated it’s because IF(P7>0,H7-P7) complies with IF(CONDITION,TRUE VALUE)
bad: ,IF(I7>P7-I7)/0.25))) this part is in excess of the first IF’s arguments. see you have the “” -TRUE VALUE and
the second IF as your FALSE VALUE… you can’t have another IF unless you will put it in the FALSE Value part of \
the second IF.

I have here your formula but honestly I do not know your conditions and what you want to do
when those conditions are true or not.

=IF(ISBLANK(P7),"",IF(P7>0,(H7-P7)/0.25,IF(I7>(P7-I7)/0.25,"TRUE VALUE HERE","FALSE VALUE HERE")))

EXPLANATION OF FORMULA: you have 3 nested IFS
IF condition: P7 = blank then
true value:formula result is blank
false value: another IF
IF P7>0 then
true value:(H7-P7)/.025
false value: another IF
IF I7>(P7-I7)/.025
true value: “TRUE VALUE HERE”
false value: “FALSE VALUE HERE”

Read More on NESTED IFS

Cheers.

CarloE

Reply

Darren March 3, 2013 at 12:53 am

Thank you CarloE

Reply

Darren March 3, 2013 at 7:46 am

I solved it;

=IF(ISBLANK(C4),”",IF(A4>0,(A4-C4)/0.25,IF(B4>0,((C4-B4)/0.25),”")))

this works.

Reply

Carlo Estopia March 3, 2013 at 11:19 am

Darren,

On behalf of Mynda, You’re Welcome.
We should thank Mynda for this
wonderful site.

Cheers.

Carlo

Reply

Carlo Estopia March 3, 2013 at 11:20 am

Darren,

I’m glad you pulled it.

Cheers.

Carl

Reply

Previous post:

Next post: