In this tutorial we’re going to explain how to use the Excel 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.
Watch the video extract as seen on YouTube, then read the full tutorial below.
Excel IF Function Explanation
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.
 |
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 formula 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 Uses for IF
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.
Try Other Operators
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* |
| <> | Less than or greater than |
*If we’d used this operator in our above example row 5 which had 5 units would have returned Pay 10%.
Download the Workbook
Now it's your turn to practice. Download the Excel file used in the example above.
Enter your email address below to download the sample workbook.
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 🙂
Amy says
HELP!!!!
We use google sheets for our time sheets and I am sure I am over thinking this, but I don’t have time to waste. So here goes….
Let’s say Employee A works 56 hours in one week. I need a formula that would take the total hours worked and subtract anything over 40 hours. I have the current formula to subtract the OT which is =IF(AD1>40, AD1-40, ” “) and that works great to take the hours over 40 and put them in that column and then calculates the OT pay. So I need to add a formula that does something similar.
So, something that does IF CELL “X” IS OVER 40 THEN IT TAKES THE NUMBER OVER 40 AWAY AND LEAVES 40 AND IF IT IS LOWER THAN 40 THEN IT JUST LEAVES THE NUMBER.
Hope that made any sense!
Mynda Treacy says
Hi Amy,
Try:
Mynda
SABIN GEORGE says
HI,
I AM ENTERING SOME COMPANY INFORMATION ON EXCEL SHEET LIKE COMPANY NAME, PHONE NUMBER, FAX NUMBER ETC. IF I REPEAT THE COMPANY NAME THERE IS ANY POSSIBILITY OR EQUATION THE PHONE NUMBER AND FAX NUMBER COMES AUTOMATICALLY?
Mynda Treacy says
Hi Sabin,
You can use a VLOOKUP formula to lookup a list of items and return corresponding details like phone number etc. Here is a VLOOKUP tutorial.
Mynda
JJ says
Hi. On Worksheet 1, if the response in Column D2:D73 is, of the various options available, “Cash”, then I’d like the corresponding Columns F and G in the same row darkened. How might I do that? Thank you.
Catalin Bombea says
Hi,
You should use conditional formatting for this.
Select those 2 columns: F2:F73 and G2:G73, then add a conditional formatting rule based on a formula: =$D2=”Cash”
Choose any format you wish for this rule, cell interir color, borders, font color.
See this article to see how to use conditional formating.
Catalin
Kirk Patrick says
I am looking for a formula that will do the following;
Well, I have a workbook with TWO sheets.
Sheet ONE has a template that calculates data, depending on some primary figure entered in a certain cell. These figures are distributed through a list of names in the same sheet. What I want is that, when these figures get calculated, they should be relayed in sheet TWO that contains the same names in the same order….and because we have different figures for different values entered in the primary cell, that means I’ll need to have these distributions relayed in different columns of sheet TWO as different values are entered in the primary cell of sheet ONE. Help? Maybe I could get you a copy of the document and understand it please.
Mynda Treacy says
Hi Kirk,
Please post your question and sample Excel file on our Excel Forum where we can help you further.
Cheers,
Mynda
Diane says
Hello – I’m stuck on an “IF” formula, can anyone help.
The question is: If the Repeat? value is “YES”, calculate the amount paid by subtracting 5 from the Fee, otherwise, the amount paid is the Fee value.
Repeat (F4) Fee (G4)
I had =IF (F4=”YES”,G4-5) My problem is, keeping the “NO” fee the same? What am I missing in the formula? Any help would be appreciated. Thanks
Catalin Bombea says
Hi Diane,
IF function has 3 arguments: first is the logical test(F4=”YES”), the second is the value for a true logical test (G4-5), the third argument is the value if logical test is false, this is also the argument you missed.
It should be: =IF(F4=”YES”,G4-5,G4)
Diane says
Hello Catalin,
Is that the only possible way of writing it? I turned in the assignment in Cengage with G4 on the end of the formula and it was kicked back saying the formula was not correct? So, I thought maybe I had the G4 wrong? Thanks, Diane
Catalin Bombea says
Type again the double quotes, most probably the ones you copy pasted are different than the ones used in excel. Or type the entire formula again manually, there is no reason not to work, other than double quotes.
Diane says
Hello again,
I have one more that was kicked back, maybe you can help with it too. Using the DSUM
The question is: Use the entire Student table including the heading row H3, use the amount paid header as the field argument, use the range J5:J6.
I have the formula =DSUM(Student[#ALL],H3,J5:J6
L.E.
Let me re-write the above question.
Use the entire Students table including the header row, use the amount paid header in cell H3 as the field argument, use the range J5:J6 as the criteria.
I had – =DSUM(Students[#ALL],H3,J5:J6 – which was incorrect
Catalin Bombea says
Hi Diane,
Can you please upload a sample file on our forum? It will be a lot easier to help you. Create a new topic and add a sample file, we will gladly help you.
Cheers
Catalin
Karla says
Hello I need help, to say if AD1 is greater than zero and AG1=coaching then take AE1 and put in AD25 and in the end I want to sum all items AE1throughAE24.
Mynda Treacy says
Hi Karla,
I’m mot sure what you mean by “take AE1 and put in AD25″.
I’ll assume your formula is in AE1:
=IF(AND(AD1>0,AG1=”coaching”),AD25,0)
Mynda
Karla says
Thank You Mynda, it works. I gave you the wrong columns yet I made it work. Here is the formula
=IF(AND(AE1>0,AH1=”coaching”),AE2,0)
Now my next step is if AE is greater than 0 and AH equals coaching then put it in cell AD 25 but I want to do this with all the rows so at the end I have a sum amount (total amount in AD25) of all rows that were greater than 0, and were labeled coaching. I hope this makes sense?
Mynda Treacy says
Hi Karla,
You need SUMIFS for that. You haven’t said what column you want to SUM so I can’t give you a formula, but you can learn how to use SUMIFS here.
Mynda
nichelle says
Can you please explain this equation for me?
=IF(F6=””,””,WORKDAY(F6,ROUND(Gates!$B$2,0)))
Mynda Treacy says
Hi Nichelle,
In English your formula reads:
IF F6 is blank/empty, then return a blank, otherwise return the date that is +/- the number of working days in cell B2 rounded up/down to the nearest whole number.
Working days exclude Saturday and Sunday.
Mynda
Elinor says
good day!
please help me find a formula for this; in C1:G1,or more columns to be added, are the spare parts of a car, and on B2:down are dates, and on C2:G10, or more columns and rows to be added, are amount of spare parts.
the problem is,
on sheet2, A2 down are dates, and on B2 down are the spare parts with amounts. for example if on sheet1 there is amount on C2, then on sheet2 B2 it will copy C1 on sheet1. and if there is amount on G3 on sheet1, then on sheet2 B3, it woll copy G1 on sheet1. what i mean is if there is amount on a certain spare part then it will copy or specify what is that spare part.
thank you and God Bless!!
Mynda Treacy says
Hi Elinor,
Thanks for your question.
Unfortunately, it’s difficult to visualise your example. Can you please post your question on our Excel Forum and upload a sample Excel file so we can help you further.
Mynda
Thomas says
I am looking for a Formula to track a players number of goals scored.
For instance Player A has 10 goals this season and scores 2 goals tonight.
The data query will update to 12 goals.
If the cell the goals are in is A1 I would want the changed data to show “+2” in B1 to reflect the data increase in A1.
Mynda Treacy says
Hi Thomas,
I’m not sure what form this “The data query will update to 12 goals.” is in so it’s difficult to give you a solution, but you can certainly track change from one week to the next but you need to keep that data in separate cells.
It would help if you can post your question on our Excel forum with a sample Excel file containing your data and we can give you a specific solution.
Cheers,
Mynda
MOHAMMAD AHSAN HABIB says
Good evening I need some help please. I need to formula ( Blue card value=100 taka)
if purchase 5000 he get 1 blue card,
if purchase 15000 he gets 3 blue card,
if purchase 30000 he gets 6 blue card and 1 blue bonus card.
if purchase 50000 he gets 10 blue card and 2 blue bonus card,
if purchase 100000 he gets 20 blue card and 5 blue bonus card
if purchase 150,000 he gets 15 Green card ( green card value=250)
if purchase 200,000 he gets 20 Green card and 1 Green bonus card
if purchase 300,000 he gets 30 Green card and 2 Green bonus card
if purchase 500,000 he gets 50 Green card and 10 Green bonus card
please helf
Catalin Bombea says
Hi Mohammad,
Try this one:
=INDEX({"-","1 blue card","3 blue cards","6 blue cards + 1 bonus blue card","10 blue cards + 2 bonus blue card","20 blue cards + 5 bonus blue card","15 green cards","20 green cards + 1 bonus green card","30 green cards + 2 bonus green card","50 green cards + 10 bonus green card"},MATCH(A13,{0,5000,15000,30000,50000,100000,150000,200000,300000,500000},1))Leslie says
Good evening I need some help please. I need to select information from 2 different cells to give a value in a 3rd cell,
The example being of cell AV73=1 and if cell AV87=0.8 then the cell AV75 should have an answer of 1. Similarly if cell AV73=1 and if cell AV87=1.1 then the cell AV75 should have an answer of 1.5. Similarly if cell AV73=2 and if cell AV87=0.8 then the cell AV75 should have an answer of 1.2. and finally if cell AV73=2 and if cell AV87=1.1 then the cell AV75 should have an answer of 1.4.
I hope that this makes sense. You help will be most welcome.
Catalin Bombea says
Try this in AV75:
=IF(AND(AV73=1,AV87=0.8),1,IF(AND(AV73=1,AV87=1.1),1.5,IF(AND(AV73=2,AV87=0.8),1.2,IF(AND(AV73=2,AV87=1.1),1.4,”Other”))))
Leslie says
Thank you so very much Catalin. I really appreciate it. As I am doing a research paper I would like to reference you by name and title and job title. Can you provide this. Thank you once again. It works perfectly. Am truly grateful.
Catalin Bombea says
Hi Leslie, Glad to hear that it works!
I think you should reference the creator of this article, Mynda Treacy, Co-Founder of MyOnlineTrainingHub, I am the Excel Support Guru on this website.
Regards,
Catalin Bombea
Kevin Lim says
=IF(H7=””,””,DATE(RIGHT(H7,4),MID(H7,4,2),LEFT(H7,2)))
What does above formula do?
Mynda Treacy says
Hi Kevin,
The formula is converting text dates into proper Excel date formats. In English the formula reads:
If cell H7 is blank then return a blank, otherwise return a date made up of the 4 right-hand characters, the middle 2 starting at the 4th character and the left 2.
Mynda
Kaiser says
Thank you so much for sharing. I was trying another problem similar to your issue, how can I write the formula for such problem:
This is your formula =IF(D2>5,”Pay 10%”,”Pay 5%”) for two options, but I was trying to put another formula which is not working, like: = IF(A2100001,”MD”). Can we get 3 options in 1 formula. Actually I want the selection of HORB, DMD, MD depending on the value provided in A2. Is this possible? Please advise.
Catalin Bombea says
Can you provide the values range for HORB, DMD, MD?
Like:
HORB: from 1 to 4
DMD: from 5 to 10
MD: >= 11
Is the result depending on multiple cells evaluation, not just D2 (or A2, you mentioned more than 1 cell) from your example?
Kate says
Hello!
I guess my question goes a little more in depth! I have a multi spreadsheet document and I am trying to do a If/than type of formula with many brackets (6). The first one works accurately, but when add the last 5, I believe it has something to do with the way I am adding the brackets around the continuing formula…Here is what I am attempting…
=IF(‘4 – Lead Line Equ.’!A3=1,”7″,(‘4 – Lead Line Equ.’!A3=2,”5″),[‘4 – Lead Line Equ.’!A3=3,”4″],[‘4 – Lead Line Equ.’!A3=4,”3″],[‘4 – Lead Line Equ.’!A3=5,”2″],[‘4 – Lead Line Equ.’!A3=6,”1″])
Mynda Treacy says
Hi Kate,
You need this tutorial : nested IF formulas
If you get stuck please post your question on our Excel forum with your sample Excel file and we can help you further.
Mynda
Ashley Dickieson says
Great and thorough explanation!
I can’t seem to figure out if this formula could work with what I want to do.
If column A in my spreadsheet has a certain date, I want column B to have a status.
For example,
If the date in column A is less than 9 months ago, column B should say current.
If the date is 9-11 months ago, I want B to say 9-11 months. And so on for 1yr + etc
Any suggestions ?
Mynda Treacy says
Hi Ashley,
It’s best to use a VLOOKUP on a sorted list for this.
If you get stuck please post your question on our Excel Forum with your sample Excel file.
Mynda
schnaider says
Can some one help on this every time I do it it’s never returning the values for those >400 and values > to 500.
I wanted to do a payroll where someone gets 40% if they can make 300$ and 45% if they can make 400$ and 50% if they can make 500$. Can someone help me please?
thanks.
Catalin Bombea says
Hi Schnaider,
Try this one:
=INDEX({0,0.4,0.45,0.5},MATCH(A1,{0,300,400,500},1))
Catalin
Ray Passave says
Very useful and, my goodness!, very well explained.
Mynda Treacy says
Thanks, Ray. Glad I could help.
Mynda
Uche Uche says
I found it very useful because it is very clear to understand.
Thanks
Mynda Treacy says
Glad we could help, Uche 🙂
faezeh says
=IF(AND(“f2:m2”>Q2,”F2:M2″<R2),1,0)
Mynda Treacy says
Hi Faezeh,
It’s not clear what you’re asking here. Does F2:M2 contain values you want to sum and then compare to Q2 or do you want to compare each value in the range F2:M2 to Q2? Please provide a sample file of your data and desired outcome and post it on our Excel Forum so we can help you.
Mynda
Jess says
Hello, I have read few a number of these posts and I am unable to sort out my formula yet.
I am working across multiple sheets in one excel document and basically I want to copy the name (contents) from A61 on sheet one to B73 on sheet two IF O61 on sheet one is YES, if not I want the cell to be blank. Is this possible, hope it makes sense.
Catalin Bombea says
Hi Jessica,
in sheet2, in cell B73, you should have a formula similar to:
=If(Sheet1!O61=”YES”,””,Sheet1!A61)
You an also use our forum to get help, you will be able to upload sample files there.
Catalin
Fahmida Ferdous Maria says
Very helpful tutorial. Easy to understand , I am pleased.
Henry Hauck says
If a cell is less than 3 – I want another cell to say “s”, if between 3 and 4.2 “m” and if 4.8 to 6 “L”
Philip Treacy says
Hi Henry,
Your criteria do not take into account if the value in the cell being tested actually has the value of your upper and lower limits. So if the value is 3, 4.2, 4.8 or 6 what do you want the result to be?
You can use this formula and to get the result you asked for. The value being tested is in cell A1. If you want to include the upper and lower limit values, change the < and > to < = and >=
Cheers
Phil
Henry Hauck says
Works fine thanks. As we sell timber by lengths I need to know short ,medium or long
Philip Treacy says
You’re welcome.
Donna kilroy says
I am costing a project so if a person whose name is in c4 is working that day then e4 is £20.00
Catalin Bombea says
Hi Donna,
Can you upload a sample file on our forum? How do we know that the person whose name is in C4 is working that day?
Cheryl says
Really helpful. I’ve recently been trying to employ the IF formula, and the way you clearly EXPLAIN the formula logic in sentence form is incredibly helpful in understanding and being able to do it myself moving forward. Thank you!
Mynda Treacy says
That’s great to hear, Cheryl. No stopping you now 🙂
Leia Morgan says
I am trying to work out a formula.
In Column E4:E400 1 of 3 options will apprear. (CAM, C$1 or CNG)
I would like to work out in Column G4
IF CNG appears in E4:E400 then cell will = Cell F4 (same line) +70% or
IF CAM appears in E4:E400 then cell will = Cell F4 (same line) or
IF C$1 app rears in E4:E400 then cell will = Cell F4 (same line) +50%
Thank you
Mynda Treacy says
Hi Leia,
You need a nested IF statement like this in cell G4, then copy down column:
Mynda
Gwen says
ENTER A FORMULA IN CELL J4 USING THE IF FUNCTION TO CALCULATE THE BONUS FOR SM. SM RECEIVES A BONUS IF HER PERFORMANCE RATING IN CELL H4 IS GREATER THAN OR EQUAL TO 4 IN EXCEL
I have been working on this all day and I have tried many formulas: IF(h44,”10000″,”0″) + others. I cannot get it to work no matter how I try it.
Catalin Bombea says
Hi Gwen,
This one should work. Note that you don’t need double quotes around numbers:
Catalin
June Vendetti says
These tips and tricks are great! I’m enjoying my review of Excel, and am learning more in the process.
Mynda Treacy says
That’s great to hear, June. Glad we can help.
Mynda
LJ says
Hi, I hope you can help me.
I am working on a formula to look for a match in another tab within the same document. If there’s a match, it should appear as YES and NO if there isn’t.
Can you please help me?
Thank you.
Catalin Bombea says
Sure, you can upload a sample file and details on our Help Desk (create a new ticket).
As a general formula, this should work:
=IF(ISNUMBER(MATCH(find_what,in_range,0)),”Yes”,”No”)
Replace the 2 generic arguments in MATCH function and it should work.
Catalin
suresh balaji says
I have condition where I need to mark leave for my team and I want to apply the formula.
Imagine a series of cell from B2 till the next 30 cell (for a month) I am marking “P” as present and “L” as leave, In the last cell in want to know how many leaves and how many present using formula. The formula should calculate the all the “P” and sum it and update the total and sum all the L and HL(Half leaves) and update the two summation separately.
Take “P as 1”, “L as 1”, and “HL as 0.5”
Please can you find the formula to apply the above Condition.
Thanks
Catalin Bombea says
Hi Suresh,
Can you please open a new ticket on our Help Desk? Upload a file with sample data to see your data structure, it will be easier to follow your details.
You mentioned that ” In the last cell in want to know how many leaves and how many present using formula.” Are you sure you want 2 different results in the same cell?
Catalin
Anne Fernando says
Many thanks for explaining the ‘IF” Formula clearly
Catalin Bombea says
Thank you for your feedback Anne, glad to see you’re happy 🙂
Cheers,
Catalin
Ramkumar says
100 Tips & Tricks book is very useful to me.
Thanks a lot…
Mynda Treacy says
Glad you liked it, Ramkumar 🙂
abdelfattah says
if i wanna to make formula to write a date of today if another cell bigger than 0
Mynda Treacy says
How about something like:
Where B2 contains the value in the ‘other cell’.
Mynda
Mark D says
Hi,
I really like your dashboard course however having recently been made surplus to requirements the pricing is above what I can afford to pay.
I will keep following your emails and I think the Excel courses you discuss are really easy to follow. Keep up the good work and hopefully there will be another offer on again.
I do find it hard to absorb information so downloading the workbooks and then going over them again is what I need.
I would also suggest a lifetime access would be appealing way to go too, where you don’t offer tutorial support but people can login after 12 months. This would also ensure you have people coming back to the site where you could offer other products.
Mynda Treacy says
Hi Mark,
Sorry to hear about your job. I hope we can help you continue with Excel while you search for a new one.
We offer a download membership option which is effectively ‘lifetime’ since you get to keep the videos forever. I appreciate that’s even more $$ though.
All the best with your job search.
Mynda
Faridz Ridzuan says
Hi Mynda , thank you very much .
Mynda Treacy says
You’re welcome, Faridz 🙂
Sujit Pokhrel says
I need help with a function.
e.g if (E6=16, “AA” or E6=40,”BB”or E6=50,”CC”)
Can anyone help me?
Catalin Bombea says
Hi Sujit,
Maybe this is what you want?
Catalin
Mohamed Saliha says
Hi Mynda
i am very thanks to you, coz i learned most of the important excel option in your tips…
i want to know there is any option in excel if anything changes data in cell its will automatic change color of cell.
Thanx / Regard Saliha
Mynda Treacy says
Thanks, Saliha. I’m glad we could help.
You can learn about Contitional Formatting here to change cell colour automatically:
And here with formulas.
Kind regards,
Mynda
Grim says
I need help with a function.
I need the value in a cell to equal 6 if the value in that cell is greater than 5.99. and if it is less than 6 that value should be shown.
So, If C14>5.99 then I need B4 to equal 6 and if C14<6 then B4 should read the value of C14.
Can anyone help me?
Catalin Bombea says
Try this:
=IF(C14<=5.99,C14,6) Catalin
raymark says
Hi mynda
Can you please help me understand this kind of formula.
=IF(IF(IF(IF(CONCATENATE(IF(AND(E12=”-“,C12=”-“),””,IF(AND(E12>0,C12=”-“),”PTA with no or not within Time entries in TKG”,IFERROR(IF((E12-C12)<-(1/1440),"PTA with no or not within Time entries in TKG",IF((D12-F12)0,C12=”-“),”PTA with no or not within Time entries in TKG”,IFERROR(IF((E12-C12)<-(1/1440),"PTA with no or not within Time entries in TKG",IF((D12-F12)0,C12=”-“),”PTA with no or not within Time entries in TKG”,IFERROR(IF((E12-C12)<-(1/1440),"PTA with no or not within Time entries in TKG",IF((D12-F12)0,C12=”-“),”PTA with no or not within Time entries in TKG”,IFERROR(IF((E12-C12)<-(1/1440),"PTA with no or not within Time entries in TKG",IF((D12-F12)0,C12=”-“),”PTA with no or not within Time entries in TKG”,IFERROR(IF((E12-C12)<-(1/1440),"PTA with no or not within Time entries in TKG",IF((D12-F12)0,C12=”-“),”PTA with no or not within Time entries in TKG”,IFERROR(IF((E12-C12)<-(1/1440),"PTA with no or not within Time entries in TKG",IF((D12-F12)0,C12=”-“),”PTA with no or not within Time entries in TKG”,IFERROR(IF((E12-C12)<-(1/1440),"PTA with no or not within Time entries in TKG",IF((D12-F12)0,C12=”-“),”PTA with no or not within Time entries in TKG”,IFERROR(IF((E12-C12)<-(1/1440),"PTA with no or not within Time entries in TKG",IF((D12-F12)0,C12=”-“),”PTA with no or not within Time entries in TKG”,IFERROR(IF((E12-C12)<-(1/1440),"PTA with no or not within Time entries in TKG",IF((D12-F12)0,C12=”-“),”PTA with no or not within Time entries in TKG”,IFERROR(IF((E12-C12)<-(1/1440),"PTA with no or not within Time entries in TKG",IF((D12-F12)0,C12=”-“),”PTA with no or not within Time entries in TKG”,IFERROR(IF((E12-C12)<-(1/1440),"PTA with no or not within Time entries in TKG",IF((D12-F12)0,C12=”-“),”PTA with no or not within Time entries in TKG”,IFERROR(IF((E12-C12)<-(1/1440),"PTA with no or not within Time entries in TKG",IF((D12-F12)0,C12=”-“),”PTA with no or not within Time entries in TKG”,IFERROR(IF((E12-C12)<-(1/1440),"PTA with no or not within Time entries in TKG",IF((D12-F12)0,C12=”-“),”PTA with no or not within Time entries in TKG”,IFERROR(IF((E12-C12)<-(1/1440),"PTA with no or not within Time entries in TKG",IF((D12-F12)0,C12=”-“),”PTA with no or not within Time entries in TKG”,IFERROR(IF((E12-C12)<-(1/1440),"PTA with no or not within Time entries in TKG",IF((D12-F12)0,C12=”-“),”PTA with no or not within Time entries in TKG”,IFERROR(IF((E12-C12)<-(1/1440),"PTA with no or not within Time entries in TKG",IF((D12-F12)<0,"PTA with no or not within Time entries in TKG","")),""))),"/",IF(C12="-",IF(E12="-","","Missing LAN"),""),IF(E12="-",IF(C12="-","","Missing PTA"),""))))))
Catalin Bombea says
Hi,
To understand a formula like this, which is just a comparison between a set of values and a set of conditions, you need to understand that specific data requirements and the meaning of every calculation like D12-F12. Those conditions and rules are known only by the one who created that formula, we have no idea of what the requirements are.
Regards,
Catalin
zulfiqar says
In if function I chosen some part of the text for logical test which is not accepted by Excel
Exampl
Mynda Treacy says
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
Jerry Roth says
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?
Mynda Treacy says
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.
Gireesh says
=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 !
Mynda Treacy says
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.
Scott says
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.
Mynda Treacy says
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:
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.:
I hope that helps.
Kind regards,
Mynda.
Scott says
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
Mynda Treacy says
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.
Lincoln McCauley says
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.
Mynda Treacy says
Wow, thanks Lincoln 🙂
We’re currently working on a VBA course. I’ll let you know when it’s ready.
Kind regards,
Mynda.
abdulwaheed adeyemi says
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
Mynda Treacy says
Hi Abdulwaheed,
Thank you very much for your kind words 🙂 I appreciate it, and my elbow is feeling stronger already!
Mynda.
rose says
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
Mynda Treacy says
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.
Roch.Mbia says
this is fabulous!
Mynda Treacy says
Thank you, Roch 🙂
Danyel says
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.
Mynda Treacy says
Hi Danyel,
It should be like this:
BTW, I also removed some brackets that weren’t required.
Kind regards,
Mynda.
nelly thabiso says
hi i have little confused about IF Statement calculation
Carlo Estopia says
Hi Nelly,
what part of IF are you confused about?
send it here: HELP DESK.
Cheers,
CarloE
Harihara Achary says
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 ?
Carlo Estopia says
Hi Harihara,
To avoid complications, please send a mock file that will
illustrate what you want via help desk.
Cheers,
CarloE
Trevor Carpenter says
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.
Carlo Estopia says
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
Farhan Ali says
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")
Carlo Estopia says
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
elbert says
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
Carlo Estopia says
Hi Elbert,
Please do send your concerns via HELP DESK.
Cheers.
CarloE
pradip says
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!
Carlo Estopia says
Hi Pradip,
You need to describe this properly.
Why don’t you send your file via HELP DESK.
Cheers.
CarloE
Catherine says
Very useful tips, and easy to understand.
Carlo Estopia says
Hi Catherine,
On Behalf of Mynda,
Thank You!
Cheers.
CarloE
Linear says
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!
Carlo Estopia says
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
ali hassanein says
IF(I515003000<5001;I5*12.5%;I5*10%))) is this troe
ali hassanein says
I FIND IT
IF((I5>5000);(I5*0.1);IF((I5>3000);(I5*0.125);IF((I5>1500);(I5*0.15);(I5*0.2))))
Carlo Estopia says
Hi Ali,
Good that you found it.
Anymore questions?
Cheers.
CarloE
Chamnan Rodpai says
Very good explanation,thankx for your kind.
Howard says
this is very helpful – thanks
Mynda Treacy says
You’re welcome, Howard 🙂
Gene Papin says
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.
Carlo Estopia says
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
fredy liem says
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
Carlo Estopia says
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.
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.
Read More: IF FUNCTIONS
VLOOKUP
Cheers.
CarloE
Abuthahir Aboobacker says
It’s very useful to me
Mynda Treacy says
Cheers, Abuthahir 🙂
Art says
Perfectly done….very straight forward presentation, even the beginners won’t get lost. Thanks
Mynda Treacy says
Cheers, Art 🙂
Elnes says
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.
Mynda Treacy says
Thanks, Elnes 🙂
NLN says
Very useful and easy to understand.
Mynda Treacy says
Cheers, NLN 🙂
Asmae says
Thanks that is very clear, but if we have 2 conditions (if and or) at the same time
Carlo Estopia says
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
Mohammed Sofi says
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.
Mynda Treacy says
You’re welcome, Mohammed 🙂
Brenden says
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.
Mynda Treacy says
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:
This will return a TRUE or FALSE outcome.
Kind regards,
Mynda.
bob mcgynn says
nested IF’s ?
Mynda Treacy says
Bob,
Nested IF’s here.
Kind regards,
Mynda.
ashik says
Best excel site I have been to ….thanks !!
Mynda Treacy says
Cheers, Ashik 🙂
Shan says
Thats very helpful, but can i get weekly emails with (pdf files of learning ) .
Mynda Treacy says
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.
Naz says
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
Mynda Treacy says
Hi Naz,
Yes, this is possible.
Kind regards,
Mynda.
Naz says
thanks so much! This has helped alot.
Mynda Treacy says
🙂 Great.
tony says
i want to have more ideas on how to use the excel.
thanks.
Mynda Treacy says
Hi Tony,
You can find an index of Excel formulas and other tools here.
Kind regards,
Mynda.
joseph says
kudos, excellent job
Mynda Treacy says
Thanks, Joseph 🙂
linda says
That was so easy to understand. thanks
Mynda Treacy says
🙂 Thanks, Linda. That’s kind of you to say.
Sanat says
This is too good dear
Lou says
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!!!
Mynda Treacy says
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.
Jacob says
very good explanation of IF statement. easy to understand.
Mynda Treacy says
Cheers, Jacob 🙂
Bernie Squire says
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?
Mynda Treacy says
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:
Kind regards,
Mynda.
Sachin says
Excellent
Mynda Treacy says
Cheers, Sachin 🙂
Angie says
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
Mynda Treacy says
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.
Maile Uluave says
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.
Mynda Treacy says
Thanks, Maile. Glad to have helped you out.
M says
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
Mynda Treacy says
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.
M says
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
M says
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
Mynda Treacy says
Hi again, M,
Let’s say your dates are in cells A2:A9. Enter this formula in cell B1:
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 says
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.
M says
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
Mynda Treacy says
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.
Shan says
thats good……
Darren says
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?
Carlo Estopia says
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.
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
Darren says
Thank you CarloE
Carlo Estopia says
Darren,
On behalf of Mynda, You’re Welcome.
We should thank Mynda for this
wonderful site.
Cheers.
Carlo
Darren says
I solved it;
=IF(ISBLANK(C4),””,IF(A4>0,(A4-C4)/0.25,IF(B4>0,((C4-B4)/0.25),””)))
this works.
Carlo Estopia says
Darren,
I’m glad you pulled it.
Cheers.
Carl
Dan Kim says
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
Mynda Treacy says
Hi Dan,
You can do this with Conditional Formatting.
Kind regards,
Mynda.
jack says
pls provide me the pdf
Mynda Treacy says
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.
Melissa says
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?
Mynda Treacy says
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.
Shyam says
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
Mynda Treacy says
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.
nancy says
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
nancy says
Or a macro?
nancy says
=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
Mynda Treacy says
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.
Mohammed Shukur Sheikh says
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
Mynda Treacy says
Thanks, Mohammed 🙂
faisal bashir says
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.
Mynda Treacy says
Thanks Faisal. I sincerely appreciate your kind words.
Mynda.
Rose Tegg says
I am learning so much, your explanations are very easy to follow. I have enjoyed doing the learning the IF statement. Thank you
Mynda Treacy says
Thanks, Rose 🙂
Ashit Shroff says
Cool stuff very useful.
Explained clearly and nicely.
Thanks.
Mynda Treacy says
Cheers, Ashit 🙂
charles says
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?
Mynda Treacy says
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.
vickie says
great stuff!
Mynda Treacy says
Cheers Vickie. I appreciate your feedback.
Mia Baum says
Extremely helpful…this 4 minute video must have just saved me 3 hours. Thanks.
Mynda says
Thanks, Mia. Glad we could help.
Romeo Levanza Jr. says
I really appreciate your tutorial video.
God Bless,
Debbi says
thanks. I’ll try using other operators as you suggest
Debbi
magnific says
this is the way a blog should be! thanks!
Mynda says
Thanks. Glad we could help.
harshman insurance says
This is the BEST page I have read all week!!!
Philip Treacy says
Thx! 🙂 Glad you enjoyed it
Conor says
excellent explanation, thanks very much, just what I was looking for
Conor
Ahmed Ahmed says
good job. thanx for your efforts, it was very helpful to me to read this page
Mynda says
@Conor – thanks for your feedback.
@Ahmed Ahmed – Glad I could help.
godofgore says
cool explanation, if only eveything was this well explained.
Excel Statement says
I agree, I once had problems trying to nest IF functions but this cleared them up.