• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel IF Statement Explained

You are here: Home / Excel Formulas / Excel IF Statement Explained
excel if function
August 25, 2010 by Mynda Treacy

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

Subscribe YouTube

Download Workbooks

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook from the video and follow along. Or Download the workbook from the written tutorial below. Note: These are .xlsx files please ensure your browser doesn't change the file extension on download.

IF Formula Builder

IF Formula Builder

Our IF Formula Builder does the hard work of creating IF formulas.

You just need to enter a few pieces of information, and the workbook creates the formula for you.

Excel IF Function Written Tutorial

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 function

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.

formula bar

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

excel if function

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.
Category: Excel Formulas
Previous Post:Will Your Business Survive a PC Disaster?Will Your Business Survive a PC Disaster?
Next Post:Want To Find Out Who Sent An Email?

Reader Interactions

Comments

  1. Shanell

    January 16, 2023 at 6:06 pm

    I am unable to formulate this correctly : =IF(E11>100002000040000,E11*3/100)))

    Please assist me. It answers to false.

    Reply
    • Catalin Bombea

      January 17, 2023 at 10:59 pm

      Hi Shanell,
      You don’t have the value_if_false argument, you only put E11*3/100 for value_if_true:
      =IF(E11>100002000040000,E11*3/100,”Replace with value needed if logical test returns false”)
      The 100002000040000 value looks quite large, there may not be any value larger than that condition.

      Reply
  2. Carmen

    December 30, 2022 at 5:28 am

    If I want a cell to return a value of 50% if another cell is between 25-49 what would the formula be?

    Reply
    • Catalin Bombea

      December 30, 2022 at 3:34 pm

      Try this:
      =IF(AND(A1>=25, A1<=49), 50%, 0)

      Reply
  3. Jeremy

    November 10, 2022 at 6:03 am

    If I have a month name (no date) in a cell *=(ex. A1) that changes and I want another cell to change to the next month (ex. A10), what formula do I use? So every time I change the name in A1, cell A10 will change to the name of the following month.

    Reply
    • Mynda Treacy

      November 10, 2022 at 10:28 am

      Hi Jeremy,

      You can convert the month name to the following month’s name with this formula:

      =TEXT(DATE(2022,MONTH(DATEVALUE(A1&”1″))+1,1),”MMM”)

      Mynda

      Reply
    • Tanmay Newase

      January 6, 2023 at 4:44 am

      Using IF statement, apply discount of 2.50% for Particulars having price less than or equal to Rs. 3800/- and 5.00% for Products having price greater than Rs. 3800/-

      Reply
      • Catalin Bombea

        January 6, 2023 at 2:15 pm

        Discount% =IF(A1<=3800,2.5%,5%)

        Reply
  4. Flor Danica

    October 19, 2022 at 3:52 am

    Please help me with this in google sheet:

    If the time difference between the two values in cells A1 and A2 is greater than 15 minutes and A1 falls outside of 12 midnight, then the cell will be highlighted.

    Thank you very much!

    Reply
    • Mynda Treacy

      October 19, 2022 at 8:49 pm

      Hi Flor,

      You need conditional formatting for this. If you get stuck, please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Reply
      • Flor Danica

        October 21, 2022 at 3:44 am

        My formula in conditional formatting is =B1-A1>TIME(0,15,0) but for the example below…

        Cell A1=(11:57:19 PM) – Cell B1 (12:23:51 AM)
        its difference is 0:26:31 and It should be highlighted because it lasts longer than 15 minutes, but it wasn’t highlighted.

        I don’t know what’s something wrong or lacking in my formula.

        Reply
        • Mynda Treacy

          October 21, 2022 at 6:37 am

          Hi Flor,

          Please post your question on our Excel forum where you can also upload a sample file and we can help you further.

          Reply
  5. Deidra Cooper

    October 6, 2022 at 12:43 am

    =IF(AND(A2:A9<=0,C2:C9<=0),"BLANK","CELLVALUE") – Using this formula….how do I get the data that is not blank to show? So what do I need to put in where "CELLVALUE" is? Thanks!

    Reply
    • Mynda Treacy

      October 6, 2022 at 7:39 am

      Hi Deidra,

      Enter the cell reference in place of “CELLVALUE”. e.g. If you want to return the data from cell A2 you’d write this:

      =IF(AND(A2:A9<=0,C2:C9<=0),"BLANK",A2) Note that unlike text, cell references are written without the double quotes. If you want to return a range, then you have to tell Excel how you want to aggregate that range e.g. wrap it in SUM: =IF(AND(A2:A9<=0,C2:C9<=0),"BLANK",SUM(A2:A9)) Or if you have Microsoft 365 or Excel 2021, you can spill a range: =IF(AND(A2:A9<=0,C2:C9<=0),"BLANK",A2:A9) If you're still stuck please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Mynda

      Reply
  6. johannes

    October 4, 2022 at 8:11 pm

    im trying to find a formula like [ =if( D4>=$200,Discount 3%,no discount)

    Reply
    • Mynda Treacy

      October 5, 2022 at 8:27 am

      Hi Johnnes,

      If the value_if_true and value_if_false arguments are text, they should be wrapped in double quotes:

      =IF(D4>=$200,”Discount 3%”,”no discount”)

      Otherwise, your formula looks fine. If you’re still stuck please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Mynda

      Reply
  7. Helen

    September 21, 2022 at 1:46 am

    Hi, I’m trying to create an if function allowing for VAT calculations.

    I’ve got the formula to work for part of it but am stuck for the second part.

    Where o16 is blank, return E16,
    =IF($O16=””,$E16,xxxxx
    for the second part I want it to work out where o16 contains an amount, perform the sum of o16 + R16. Any help would be really appreciated!

    Reply
    • Mynda Treacy

      September 21, 2022 at 8:54 am

      Hi Helen,

      You’re almost there:

      =IF($O16="",$E16,O16+R16)

      You may need to play around with the absolute references as I’m not sure where you’re copying the formula.

      Mynda

      Reply
      • Helen

        September 21, 2022 at 6:22 pm

        Thank you so much Mynda, I had been looking at is so long I couldn’t see for frustration!

        Reply
  8. Sankar

    July 25, 2022 at 9:45 pm

    if Column No.1 is Yes
    Column No.2 is Value represent in Colum No.3

    Reply
    • Catalin Bombea

      July 26, 2022 at 2:33 pm

      Hi Sankar, use this formula in C3:
      =IF(AND(A1=”Yes”,B1=”Value”),True,False)

      Reply
  9. blakeaugust

    July 25, 2022 at 3:30 pm

    =IF(C3>=10000),20% discount, “Donate”) please help me with this, thank you very much:)

    Reply
    • Philip Treacy

      July 25, 2022 at 3:34 pm

      Hi,

      What are you trying to do with the IF?

      What is the 20% discount being applied to?

      Or do you just want the text 20% discount? If so, try this

      =IF(C3>=10000, “20% discount”, “Donate”)

      Regards

      Phil

      Reply
  10. howard

    July 23, 2022 at 1:03 am

    Hi, I am stuck please help,

    I am in VBA and the the code is below but if I remove the “a” in G10 or L10 then E12 must become “” (Blank)

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range(“G10:L10”)) Is Nothing Then
    ‘ MsgBox “It will Add a 0 to Cell E12 that you need to remove manually once you delete the (a)”
    Range(“E12”).Value = 0

    End If

    End Sub

    Reply
    • Mynda Treacy

      July 23, 2022 at 9:51 am

      Hi Howard,

      This tutorial is for the spreadsheet IF function, not VBA. Please post your question on our Excel VBA forum where you can also upload a sample file and we can help you further.

      Thanks,

      Mynda

      Reply
  11. naso khan

    June 26, 2022 at 6:30 pm

    ifcell(B2)>0 but cell(B2)=ifcell(B2)>600000 but cell(B2)=ifcell(B2)>1200000 but cell(B2)=ifcell(B2)>1800000 but cell(B2)=ifcell(B2)>2500000 but cell(B2)=<3500000,then company will charge you 17.2%+195000 money

    Reply
    • Mynda Treacy

      June 27, 2022 at 9:41 am

      Hi Naso,

      You should use a VLOOKUP formula on a sorted list rather than IF for this type of formula.

      Mynda

      Reply
  12. Celina Gill

    May 28, 2022 at 4:38 am

    If O1 is <=1000, divide by K4. If results of O1/K4 is less than 50, return a blank cell. For the life of me I can not get the IF statement to get this result.

    Reply
    • Mynda Treacy

      May 28, 2022 at 11:09 am

      Hi Celina,

      =IF(O1<=1000,O1/K4,IF(O1/K4<50,""))

      Mynda

      Reply
  13. Nabeel

    April 28, 2022 at 5:16 am

    ” Up to 100000 – 0%
    Above 100000 to 200000 – 20%
    Above 200000 – 30%”

    How to solve above equation in excel

    Reply
    • Mynda Treacy

      April 28, 2022 at 9:15 am

      Hi Nabeel,

      For this you should use a VLOOKUP formula on a sorted list, not IF.

      Mynda

      Reply
  14. Leslie

    April 7, 2022 at 5:20 am

    Losing my mind trying to recall the formula to highlight blank cells within a column, IF the date in another column exceeds 14days from the current date.
    i.e. cells from J7 down, to be highlighted pink if the sum of the date in I7 down is greater than 14 days past the current date in cell J1 (which is a “TODAY” formula)

    Reply
    • Mynda Treacy

      April 7, 2022 at 10:02 am

      Hi Leslie,

      Please see this tutorial on conditional formatting formulas. If you’re still stuck please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Mynda

      Reply
  15. Heidi

    March 24, 2022 at 4:33 am

    Is there a way to: turn Cell R12 red IF cells C12, D12, L12, M12, N12, P12, Q12 = N (for “no”). IF any of those cells have a “N” R12 should turn red.

    Reply
    • Mynda Treacy

      March 24, 2022 at 9:41 am

      Hi Heidi,

      Yes, you can use conditional formatting for this.

      Mynda

      Reply
  16. lee

    March 22, 2022 at 2:35 pm

    i need a formula when
    every 1000dollar will be charge 1.5 dollar

    Reply
    • Catalin Bombea

      March 22, 2022 at 3:04 pm

      Try:
      =RoundDown(A1/1000,0)*1.5

      Reply
  17. Kevin C

    February 24, 2022 at 12:32 pm

    How would you enter and IF statement that displays the work “No” if an amount is less than or equal to zero and “Maybe” if the amount is greater than zero?

    Reply
    • Mynda Treacy

      February 24, 2022 at 12:54 pm

      Hi Kevin,

      =IF(A1<=0,"No","Maybe")

      Mynda

      Reply
  18. Erika F

    February 5, 2022 at 1:20 pm

    I don’t know how to explain what I want in short so here goes nothing.

    Assuming today is the 15th of the month

    I want collumn E to show PAID on a light green back-fill if collumn D has a value of 15 or less. Or UNPAID on a light orange back-fill if the value of collumn D is 16-31

    Assuming today is the 1st of the month only cells valued at 1 in collumn D would display PAID in collumn E

    Assuming today is the 31st ALL cells in collumn E should display PAID

    I would like it to be irrelevant to month or year only relevant to day.

    I have my bills set on auto pay and I’d like my budget spreadsheet to auto update PAID bills vs UNPAID based on what day it is. I have the day they are scheduled to pay listed in collumn D currently.

    Reply
    • Mynda Treacy

      February 5, 2022 at 3:47 pm

      Hi Erika, you need both an IF formula and conditional formatting. Please post your question and sample Excel file on our forum where we can help you further: https://www.myonlinetraininghub.com/excel-forum

      Reply
  19. Mia

    January 25, 2022 at 6:25 am

    Trying to figure out if cell C1 is equal to or greater to $100 then charge 10% or $15, which ever is greater, not to exceed $50.

    Reply
    • Philip Treacy

      January 25, 2022 at 8:20 am

      Hi Mia,

      It would be more efficient to use the LET function but you can do it this way

      =IF(IF(C1>=100,IF(C1*0.1>15,C1*0.1,15),””)>50,50,IF(C1>=100,IF(C1*0.1>15,C1*0.1,15),””))

      Regards

      Phil

      Reply
      • Mia

        January 25, 2022 at 11:58 am

        Hi Phil,

        Thank you so much for your help. I will try it out. I’ve never used the LET function and will also attempt to learn this.
        thanks again!

        Mia

        Reply
        • Philip Treacy

          January 25, 2022 at 3:17 pm

          No worries

          Reply
  20. Ch sankararao

    January 4, 2022 at 2:42 am

    >200000 , AnD 200000 formula send me

    Reply
    • Philip Treacy

      January 4, 2022 at 9:13 am

      Hi,

      You need to supply more information. What is the result of the IF? What are the tests you want to perform?

      Phil

      Reply
  21. Matilda

    December 9, 2021 at 1:32 pm

    Hi – I am trying to create an excel document that will date stamp something when data in two different columns return something.
    The data in each of these columns could reflect, upgrade, downgrade or no change.
    Column a for example might reflect no change but column b might reflect upgrade or vs versa Column a and b could also reflect upgrade or downgrade.

    Reply
    • Catalin Bombea

      December 9, 2021 at 1:52 pm

      Hi Matilda,
      A date stamp needs to be permanent i suppose? If you are looking for a formula, a date function will always recalculate and return today’s date, will not remain at the date when that change occurred.
      Therefore, you might need a visual basic solution.
      Can you please clarify and provide a sample file on our forum (create a new topic after you sign-up/in), for a better understanding of your situation?

      Reply
  22. Madhuri Narravula

    November 9, 2021 at 4:24 pm

    Scenario is We have tasks which need to be completed with in the scheduled timelines however employees take extra days to finish the task.. existing excel has 3 dates …start time, end time and actual end date. In actual end date employee updates the date they completed the task. I need to get duration of days and if they have met or not met the deadline. Accordingly I have to find out the compliance percentages too. There is so much stuff in it and I need to get a dashboard out of this excel file on a MTD and Daily.
    Please let me know if I provide u the excel can you help me on the outcome.

    Reply
    • Mynda Treacy

      November 9, 2021 at 7:14 pm

      Yes, it’s possible to do in Excel. Please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Reply
  23. Md.Iqbal Hossain

    September 11, 2021 at 5:08 pm

    IF A1 is < =75, then 4.19*A1
    IF A1 is between 76-200, then 4.9*A1 +1*5.72(note:if A1=76; if A1=77 then it will be 2*5.72)

    Reply
    • Mynda Treacy

      September 11, 2021 at 8:54 pm

      Not sure how you can have two criteria for 77 i.e.

      IF A1 is between 76-200, then 4.9*A1 +1*5.72

      AND

      If A1=77 then it will be 2*5.72

      Please post your question and sample Excel file on our forum where we can help you further and our answers can also help others: https://www.myonlinetraininghub.com/excel-forum

      Mynda

      Reply
  24. Krishna Wagle

    September 1, 2021 at 11:10 pm

    Needs excel formula to calculate following:-
    If value of C2 (in a cell) is greater than 150000, take value greater than 150000 but less than 200000 in C3(in a cell) but if value of C2 is less than or equal to 150000 than ignore it. Please enlighten by providing the formula in MS Excel.

    Reply
    • Mynda Treacy

      September 2, 2021 at 9:02 am

      Hi Krishna,

      You haven’t provided all the information, but you can start with this and modify as required:

      =IF(C2<=150000,"",IF(AND(C2>150000,C3<200000),C2))

      If you get stuck please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Mynda

      Reply
      • Krishna Wagle

        September 2, 2021 at 4:34 pm

        Thanks for replying. However, the formula while applying is shown as false. My question is follows:-
        The value of C2 is Rs.240000. From the value of C2, amount exceeding Rs.150000 but less than Rs.200000 is to be taken in next cell (say cell C5) and if value of C2 is equal to or less than Rs.150000 (C2) take as zero.

        Hope you will clarify and enlighten with formula for calculating the same.

        Reply
        • Mynda Treacy

          September 2, 2021 at 4:51 pm

          This is not clear “From the value of C2, amount exceeding Rs.150000 but less than Rs.200000 is to be taken in next cell”

          Please post your question and sample Excel file on our forum where you can show us your desired result in an example and we can help you further: https://www.myonlinetraininghub.com/excel-forum

          Reply
          • Krishna Wagle

            September 2, 2021 at 10:52 pm

            Thanks. My question is that employees are submitting Income tax saving documents such as Rs.100000, Rs.134000, Rs. 200000, Rs.260000, etc. Exemption limit is Rs.150000 only irrespective of saving documents submitted by them under particular section. If employees submitted saving documents for Rs.120000, the whole amount is exempted as it is less than Rs.150000 but if employee submitted saving documents for Rs.210000 his exemption is limited to Rs.150000 as per Rules. However, saving documents beyond Rs.150000 subject to a maximum of Rs.50000 can be exempted under another section. As the employee submitted saving documents of Rs.210000, he can get additional exemption of Rs.50000 in another section and excess amount of Rs.10000 to be ignore.
            My question is that an employee has submitted saving documents amounting to Rs.240000 and maximum amount of Rs.150000 is exempted in particular section and amount exceeding Rs. 150000 is to be exempted in another section subject to the limit of Rs.50000 only and rest amount to be ignore. However, if saving document submitted by employee is less than Rs.150000 the same to be ignore for exemption in another section. (If the value of C2 is greater than 150000 takes value greater than 150000 but less than 200000 in another cell and if value of C2 is less than or equal to 150000 ignore or takes zero).
            Hope you will reply to my query.

          • Mynda Treacy

            September 3, 2021 at 9:42 am

            Please post your question in the forum and include your Excel file. It is much easier to understand an example file than try and follow paragraphs of text. Thanks for understanding.

  25. Gulrez Rizvi

    August 11, 2021 at 5:55 pm

    How to create report of inventory list of a particular commodity and date or a daily sales report of a flight by giving flight number and date , to obtain the ticket numbers with passenger details.

    Reply
    • Mynda Treacy

      August 11, 2021 at 10:08 pm

      Hi Gulrez, please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Reply
  26. Krista Stephan

    July 22, 2021 at 2:21 am

    Hello. I am trying to use my excel spreadsheet to identify when a date is out of compliance based on a category assigned. For instance tier 1 (g) requires a visit within 3 months of its last date (h) if > 3 months I need excel to identify that out of compliance but if (g) is tier 2, I need alert on (h) for non compliance > 6 months. Is that possible?

    Reply
    • Catalin Bombea

      July 22, 2021 at 2:44 am

      Hi Krista,
      Can you prepare a sample file with some examples? Will be much easier to help you.
      Use our forum to create a new topic and upload the sample file.
      Catalin

      Reply
  27. Candace

    July 1, 2021 at 7:38 pm

    I need to do a formula that if there is a Y in column I then the following formula must apply 0.01*(AM4+AK4+AX4+AR4+M4) for column BC. If column I is blank then it should return a 0 in column BC.

    Reply
    • Catalin Bombea

      July 1, 2021 at 10:24 pm

      Try this in BC2:
      =IF(LEN(I2)=0,0,0.01*(AM4+AK4+AX4+AR4+M4))

      Reply
  28. Makesense

    June 15, 2021 at 3:55 am

    Hello, please i want to convert a column of data from one range 0 – 50000 to another range 5 – 90. How do i go about it? I have tried to form a formula but haven’t been successful so far

    Reply
    • Mynda Treacy

      June 15, 2021 at 8:41 am

      Not sure what you mean. Please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Reply
  29. Zahir Sabit

    April 19, 2021 at 8:22 pm

    Very interested.

    Reply
  30. Mike

    March 17, 2021 at 5:46 pm

    =EDATE(G10,(DATEDIF(G10,H10,”m”)+1)*1). This calculates the end date in terms of months and years. Can this string be used for showing “days” instead of months and years?

    Many Thanks

    Reply
    • Mynda Treacy

      March 18, 2021 at 9:58 am

      Hi Mike,

      This formula returns a date. Perhaps if you provide some inputs for the dates in G10 and G10 and an example of the desired result we’ll better understand what you’re wanting to see and can help you further. Please post your question and sample Excel file on our forum: https://www.myonlinetraininghub.com/excel-forum

      Mynda

      Reply
  31. wes

    March 16, 2021 at 10:06 pm

    I have a simple formula needed but cannot figure it out. I need the column cell in G to turn red or green if it does not or does equal that value in column F.

    Reply
    • Mynda Treacy

      March 17, 2021 at 9:00 am

      Hi Wes, you can use conditional formatting with formulas to do this. The formula will be something like:

      =G2<>F2

      No IF required because conditional formatting requires a TRUE/FALSE result. More in the tutorial linked to above.

      Mynda

      Reply
  32. Rajesh Barman

    March 14, 2021 at 5:00 pm

    “HIGH” if revenue is more than equal to Rs
    1000 and “LOW” if revenue is less than 1000. how can do it?

    Reply
    • Philip Treacy

      March 15, 2021 at 9:11 am

      Hi Rajesh,

      Assuming you have your revenue value in cell A1 use this

      = IF(A1 >= 1000, “High”, “Low”)

      Regards

      Phil

      Reply
  33. Tom

    March 10, 2021 at 8:23 pm

    I have got this so that if B3 contains Pass the cell with the formular changes to 10:

    If =value(IF(B3=”Pass”,”10″)

    How can I get the same cell to change to 20 if B3 becomes Merit? (and then 30 if it becomes Distinction)

    Reply
    • Mynda Treacy

      March 10, 2021 at 10:00 pm

      Hi Tom,

      Let’s say the value is in cell B3, the formula would be:

      =IF(B3=”Pass”,10,IF(B3=”Merit”,20,IF(B3=30,”Distinction”)))

      Mynda

      Reply
  34. Ben McFadden

    March 8, 2021 at 9:49 am

    Hi,
    Formula I currently have is as follows;

    =IF(B45<=50,450,450+((B45-50)*D45))

    I still need the above but I also want to add to it that if B45 is left blank, a value of 0 is shown.

    Thanks in advance!

    Reply
    • Mynda Treacy

      March 8, 2021 at 9:53 am

      Hi Ben,

      You need a nested IF formula:

      =IF(B45=0,0,IF(B45<=50,450,450+((B45-50)*D45))) Mynda

      Reply
      • Ben McFadden

        March 8, 2021 at 10:15 am

        Perfect! Thanks for your help!

        Reply
  35. IVA RANK

    February 26, 2021 at 2:38 am

    How can i make this formula continue from line 6 to line 57
    =IF(G5+G6-E6>=480,480,G5+F6-E6)

    Reply
    • Mynda Treacy

      February 26, 2021 at 8:50 am

      Copy and paste? Perhaps I’m not understanding what you mean. If so, please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Reply
  36. Maureen Lymer

    February 10, 2021 at 9:30 pm

    I have been using this formula for years

    ‘=IF(OR(C3>140,D3>90),”Yes”,”No”)

    but now they want to find C3>140 but less than 160

    or D3 >90 but less than 100. Can’t seem to do the range bit.

    Please help.

    Reply
    • Mynda Treacy

      February 10, 2021 at 9:42 pm

      Hi Maureen,

      Try this:

      =IF(OR(AND(C3>140,C3<160),AND(D3>90,D3<100)),"Yes","No")

      Mynda

      Reply
  37. Imran

    January 10, 2021 at 1:07 am

    Hey,
    Could help me with the problem?

    Q1. Five different companies sold five different products by you. And gave you the commissions as 5%, 10%, 15%, 20%, 25%. Make the formula in excel sheet.

    Reply
    • Catalin Bombea

      January 10, 2021 at 4:40 pm

      Hi Imran,
      We do our best to reply within 24 hours, but it’s not an instant service, sorry to disappoint you.
      However, we do not answer school test questions and your question seems like it.
      Just a tip: read comments below, you’ll find an INDEX MATCH solution.

      Reply
  38. John Lim

    December 19, 2020 at 2:34 pm

    Hi all, can you help me on this

    =IF(Q10=BW10,”PAID”,IF(BW10Q10,”OVERPAYMENT”)))

    i want to add additional formula: incase the input value is 0.00 (zero) the result is Unpaid

    Q10 = 10,000.00
    BW10 where you input the amount

    Reply
    • Mynda Treacy

      December 20, 2020 at 1:50 pm

      Hi John,

      Try:

      =IF(Q10=BW10,"PAID",IF(BW10=0,"UNPAID",IF(BW10>Q10,"OVERPAYMENT")))

      Mynda

      Reply
      • John Lim

        December 23, 2020 at 2:12 am

        thank you so much Ms. Mynda

        Reply
  39. KAREN WIGREN

    September 23, 2020 at 3:16 am

    I need to create a nested formula with the IF and SUM functions that will check the total number of hours worked in 1 week and is equal to zero If the cell should display nothing I want “”

    Reply
    • Mynda Treacy

      September 23, 2020 at 8:49 am

      Hi Karen, It’s difficult to give you a solution without seeing the layout of your data. Please post your question and sample Excel file on our forum where we can help you further: https://www.myonlinetraininghub.com/excel-forum

      Reply
  40. KOLINI TAUILIILI

    July 4, 2020 at 10:19 pm

    I want a formula to calculate If the customer brought 3 or more add-ons, then my answer is 15%, otherwise my answer is 0 or “” (Blank)

    Reply
    • Mynda Treacy

      July 5, 2020 at 3:14 pm

      Hi Kolini,

      =IF(A1>2,15%,0) Assuming A1 contains the number of add-ons.

      Mynda

      Reply
  41. AWAIS MAZHAR

    June 25, 2020 at 4:19 pm

    =IF(F4=400000,F4*0.001)+IF(F4>400000,(F4-400000)*0.0015)+IF(F4>500000,(F4-500000)*0.004)

    What formula function is that please anybody?

    Reply
    • Philip Treacy

      June 26, 2020 at 10:26 am

      Hi,

      Not sure what to say, it’s an IF formula and looks like it’s calculating something like a commission based on the value in F4.

      Regards

      Phil

      Reply
      • AWAIS

        June 27, 2020 at 12:25 pm

        THANKS,

        Yes this formula is commission based. (D4= cell)

        IF D4=400000 then commission D4 * 0.10%

        Plus OVER 400000 LESS 500000 (D4-400000) * 0.15%

        Plus OVER 500000 (D4-500000) * 0.40%

        Please guide me.
        Thanks

        Reply
        • Philip Treacy

          June 28, 2020 at 6:16 pm

          Hi Awais,

          That commission structure seems odd. The first tier is only paid if D4 is exactly 400000?

          Try this

          =IF(D4>=400000,400000*0.001,0) + IF(D4>400000,IF(D4<500000,(D4-400000)*0.0015,100000*0.0015),0) + IF(D4>500000,(D4-500000)*0.004,0)
          

          Regards

          Phil

          Reply
          • AWAIS

            June 29, 2020 at 4:56 pm

            Your Question:

            That commission structure seems odd. The first tier is only paid if D4 is exactly 400000?

            Answer: YES

            But this formula not work.

            =IF(D4>=400000,400000*0.001,0) + IF(D4>400000,IF(D4500000,(D4-500000)*0.004,0)

          • Philip Treacy

            June 29, 2020 at 5:57 pm

            How exactly does it not work?

            Please start a topic on the forum and attach your workbook so I can see what you are doing in your worksheet.

            Regards

            Phil

  42. Jonathan

    April 23, 2020 at 12:56 am

    Hi, I am Jonathan, an excel novice.
    I had the opportunity to learn it a long time ago, but I refused (that decision has come back to haunt me today).
    My question is this, how do I return a column value to another column when the third column has another value.
    For example. if a1=4 return b1 in c1

    Reply
    • Catalin Bombea

      April 23, 2020 at 2:17 am

      Hi Jonathan,
      In cell C1, use this: =IF(A1=4,B1,””)

      Reply
  43. David

    March 28, 2020 at 4:09 am

    I’ve input the formula: =DAYS(C1,A1) where these are dates. I’m wanting the total of days from when a document was submitted (A1) to when it was completed (C1). The formula works fine. However, our company is migrating to Google Sheets (hate it) when possible. Fine for the company, but not fine for us “superusers” that use excel for everything. My problem is the excel spreadsheet is stored on my Google Drive. When I go in to edit, all three columns setup with the above formula all show #NAME? instead of the days we seek. Plus, the formula line has this =xludf.DAYS(C1,A1).
    If I delete the “xludf.” in the formula line and hit enter I get my days result. However, when I close the spreadsheet and come back a short while later. The =xludf.DAYS(C1,A1) is back again. Question, why is this happening and can I get a permanent fix?

    Any help is appreciated. Thank you!

    Reply
    • Mynda Treacy

      March 28, 2020 at 11:11 am

      Hi David,

      Perhaps you can just change your formula to =C1-A1 as this returns the same result as =DAYS(C1,A1) and should be recognised by Google Sheets. Commiserations by the way. No more Excel will be very sad.

      Mynda

      Reply
      • David

        April 8, 2020 at 8:27 am

        I’ll try that thank you Mynda.

        Reply
      • David

        April 9, 2020 at 2:44 am

        It worked great. Thanks again!

        Reply
  44. Schwann

    March 7, 2020 at 11:41 am

    I am really struggling with nesting formulas. add formulas to complete the table of hours used. in cell B17, create a nested formula with the IF and SUM functions that check if the total number of hours worked in week 1 (cells B9:F9) is equal to 0. if it is, the cell should display the total number of hours worked in week 1. copy the formula from cell B17 to fill the range B18:B20

    Reply
    • Catalin Bombea

      March 7, 2020 at 2:13 pm

      hi Schwann,
      Please upload a sample file and describe where you need help, it’s hard to see a problem without a file.
      Use our forum for upload.

      Reply
  45. Peter

    March 6, 2020 at 6:56 pm

    Hi, Can you tell me formula for buy every 50000 and get 1 point, please…

    Reply
    • Catalin Bombea

      March 7, 2020 at 2:11 pm

      Hi Peter,
      I think you should just divide the amount by 50.000:
      =A1/50000
      If A1 is 100.000 for example, the result will be 2. There may be fractional results, so you should use ROUNDUP, ROUNDDOWN or INT to remove the decimal places.

      Reply
  46. ACHIRE ANDREW EMMANUEL

    November 15, 2019 at 4:03 pm

    Greetings Mynda Treacy. I want some help in developing an IF statement for the Schedule below. It is for our Local Service Tax for employment income deductions in Uganda.

    Exceeding 100,000/= but not exceeding 200,000/= 5,000
    Exceeding 200,000/= but not exceeding 300,000/= 10,000
    Exceeding 300,000/= but not exceeding 400,000/= 20,000
    Exceeding 400,000/= but not exceeding 200,000/= 30,000
    Exceeding 500,000/= but not exceeding 200,000/= 40,000
    Exceeding 600,000/= but not exceeding 200,000/= 60,000
    Exceeding 700,000/= but not exceeding 200,000/= 70,000
    Exceeding 800,000/= but not exceeding 200,000/= 80,000
    Exceeding 900,000/= but not exceeding 200,000/= 90,000
    Exceeding 1,000,000/= 100,000

    Just to clarify, for instance in the first line, if the gross exceeds 100,000/= but does not exceed 200,000/=, then 5,000/= is the charge that they incur as tax. Waiting for your positive feedback. A full IF statement

    Reply
    • Catalin Bombea

      November 15, 2019 at 5:03 pm

      Hi Achire,
      You can use a simple Index-Match combination:
      =INDEX({0,5000,10000,20000,30000,40000, 50000,60000,70000,80000,90000 ,100000},MATCH(A1,{0,100001, 200001,300001,400001,500001 ,600001,700001,800001,900001 ,1000001},1))
      You will have to play around with the limits: in this version, a number between 100001-200000 will return 5000.

      Reply
    • pabitra kumar poddar

      January 13, 2020 at 7:27 pm

      formula may be required

      Reply
    • Priyant

      August 6, 2022 at 12:01 am

      =IF(I4>1000000,100000,(IF((I4900000),90000,(IF((I4800000),80000,(IF((I4700000),70000,(IF((I4600000),60000,(IF((I4500000),50000,(IF((I4400000),40000,(IF((I4300000),30000,(IF((I4200000),20000,(IF((I4100000),10000,0)))))))))))))))))))

      I4 = in this cell you can put your salary

      Reply
  47. Cat Vaillancourt

    November 8, 2019 at 12:56 am

    URGENT

    I’m looking to do a FAST input of my formula =IF(Oct!R2,Oct!P2,Oct!Q2), onto multiple worksheets
    Such as
    Tab 1 is titled “oct”,
    Tab 2 through Tab 500, in the cell marked date,
    I want to NOT have to go worksheet by worksheet to make changes the formula to reflect the correct line (example tab 3 – =IF(Oct!R3,Oct!P3,Oct!Q3), tab 4 =IF(Oct!R4,Oct!P,Oct!Q4) to pull from for each tab.
    Is this possible?

    Reply
    • Mynda Treacy

      November 8, 2019 at 9:34 am

      Hi Cat,

      If you group the sheets first, then input the formula, it will insert it on each sheet relative to that sheet’s name. To group sheets, hold down SHIFT to select a range of contiguous sheets, or CTRL to select multiple non-contiguous sheets.

      Mynda

      Reply
  48. Frank

    November 6, 2019 at 10:48 am

    I am trying to create a “shopping list” where someone would free text the item number and then have the price, UOM and vendor and some other fields autopopulate. Can this be done with this if/then formula? It is probably at least 500 items. I have all the info on excel file, i just need to make the shopping list

    Reply
    • Mynda Treacy

      November 6, 2019 at 3:20 pm

      Hi Frank,

      Better to use VLOOKUP to lookup the item number and return the relevant price etc.

      Mynda

      Reply
  49. Debasish Paul

    October 30, 2019 at 11:30 pm

    I’ve a question , in excel left side column with two clause ” yes/no ” and in right side columns value with 1 1 1 1 that means sum 4 , but things is that , if i’m set a formula in a column left side clause is yes the sum sum should be done , if i’m select no the sum should be “0” .

    if any formula is the then please help me.

    Reply
    • Catalin Bombea

      October 31, 2019 at 1:07 am

      Try this one:
      =IF(A1=”Yes”,SUM(C1:F1),0)

      Reply
  50. Joshua

    October 18, 2019 at 10:33 pm

    Hi There,

    I am looking for a formula which allows me to type 6M (which is a description of a shipping container meaning size 6 meter) in cell A1 and in cell A6 it brings up the transport price for a 6M container which is R3500.00.

    Thank you 🙂

    Reply
    • Mynda Treacy

      October 19, 2019 at 8:37 am

      Hi Joshua,

      VLOOKUP would be better for this.

      Mynda

      Reply
  51. Vinay Vyas

    October 17, 2019 at 10:33 am

    Hey,
    I have put an extra space in the nested IF formula e.g. IF(G2<25, "10", IF(G2=30, "15", "0")) and got the answer.

    Will it be considered wrong in my exam due to extra space instead i got the right answer?

    Please clarify!!

    Reply
    • Philip Treacy

      October 17, 2019 at 10:58 am

      Hi,

      Spaces in a formula like this are irrelevant, so no this should not be considered wrong.

      Regards

      Phil

      Reply
      • Vinay Vyas

        October 18, 2019 at 8:46 pm

        Thanks for your reply Phil, I have one more doubt :

        The question in the exam that was like: We have to allot grade to the student on the basis of hours. If hours are less then 25 then grade 10, if hours are equal to 25 than grade 15, if hours are equal to 30 than grade 20 and if hours are equal to 40 than grade 30. I used the formula : IF(G2<25, "10", IF(G2=25, "15", IF(G2=30, "20", IF(G2=40, "30", "0")))) .

        My doubt is that, there wasn't any false condition that was given so I put "0" in the formula {I solved in this way because I couldn't remember another way at that time} because all the entries in the question were among above-given conditions. So in spite of getting the right answers for all the entries will it be considered wrong?

        Some are saying there will come 3 if, some are saying 4 if and some are saying this way. So please clear the confusion.

        Reply
        • Catalin Bombea

          October 20, 2019 at 2:43 pm

          Hi Vinay,
          It does not make much sense, what if a student has 27 hours? Will receive a zero and this is wrong, from 25 up to 30 it should receive 20 as the grade.
          Try this, will take care of intermediate values as well:
          =INDEX({10,15,20,30},MATCH(A1,{0,25,30,40},1))

          Reply
  52. Robert Paterick

    September 13, 2019 at 10:10 pm

    How to move text from cell 1in sheet 1 to sheet 2 in cells 1,2, etc.sucessifly using vba code.

    Reply
    • Catalin Bombea

      September 14, 2019 at 11:36 pm

      Hi Robert,
      Can you clarify? Is it the same entire cell that needs to be duplicated in multiple sheets? Or you need to split the text (in this case- which are the criteria/delimiters for splitting)?

      Reply
  53. Waqas Munir

    August 27, 2019 at 5:27 am

    Hello
    if cell A1 have any text then d1 should get 2000 and if a1 and b1 both have some text then at the same time cell d1 should be filled with 4000
    and if a1 is empty then b1 will also b empty in this case d1 should empty
    if a1 have some text and b1 is empty then d1 should get 2000 i need a formula to resolve this query.

    Reply
    • Philip Treacy

      August 27, 2019 at 8:44 am

      Hi Waqas,

      Try this

      =IF(A1<>"",IF(B1<>"",4000,2000),"")

      Regards

      Phil

      Reply
      • Waqas Munir

        August 27, 2019 at 5:43 pm

        Thank you so much Philip Treacy. Your given formula is working and problem is resolved.
        In future if c1 also have some text then answer should be 6000,
        i will be thankful if you guide for third situation.
        Thanks for your cooperation.

        Best Regards

        Waqas Munir

        Reply
        • Philip Treacy

          August 30, 2019 at 1:48 pm

          Hi Waqas,

          If there’s a value in C1, how does that work with possible values in A1 and B1 e.g.

                       
          AAA BBB   4000
          AAA 2000
          AAA BBB CCC ???
          AAA CCC ???
          ??? ??? CCC ???

          Regards

          Phil

          Reply
        • Catalin Bombea

          August 30, 2019 at 2:29 pm

          Try:

          =IF(A1<>"",IF(B1<>"",4000,2000),IF(C1<>"",6000,""))
          
          Reply
          • Waqas Munir

            September 17, 2019 at 7:42 pm

            Hello Phil & Catalin – Sorry for late reply on this and the formula for C1 is not working.

            Condition is that= if c1 have value then a1 and b1 will not be empty and a1 b1 also contains some text.
            now I facing another problem which i have to resolve.
            =IF(A1″”,IF(B1″”,4000,2000),IF(C1″”,6000,””)) after entering it i face an error.
            please add one more condtion for A1 with this upper mention formula.
            if A1 have specific text that is A1=2stops then answer 1000 and in case any other text answer 200

            =IF(A1″”,A=”2Stops”,IF(B1″”,4000,2000,1000),IF(C1″”,6000,””))

            please guide me the right formula as i mention the condition.

            A1 B1 C1 Answer Cell
            Any Text 2000
            Any Text Any Text 4000
            Any Text Any Text Any Text 6000
            2Stops 1000

            in last case A1 have just text B1 C1 will empty.
            please guide me on formula for upper mention conditions.

            Thanks in advance for your help.

            Regards

            Waqas Munir

          • Catalin Bombea

            September 18, 2019 at 12:36 am

            Hi Munir,
            Please use our forum to upload a sample file with a few manual expected results, it’s hard to follow your instructions, a file with examples will be better hopefully.

  54. NEIL

    August 23, 2019 at 8:43 am

    =IF(B5=”P”,P5*0.1+P5),IF(B5=”C”,P5*0.2+P5)
    HOW TO MAKE THIS FOMULA CORRECT

    Reply
    • Philip Treacy

      August 23, 2019 at 9:00 am

      Hi Neil,

      Try this

      =IF(B5=”P”,P5*0.1+P5,IF(B5=”C”,P5*0.2+P5,0))

      Regards

      Phil

      Reply
  55. ernest

    August 20, 2019 at 7:55 pm

    i need faster and easier way to do my analysis as I don’t have much idea in vlook up and ifs.

    Reply
    • Philip Treacy

      August 21, 2019 at 3:44 pm

      Hi Ernest,

      Not sure what to say. Learning things like VLOOKUP and IF is the way to do your analysis quicker. there are no shortcuts, you’ll just have to put in the time.

      Phil

      Reply
  56. James Guy

    July 6, 2019 at 8:30 am

    Can a dashboard be created for online stock trading. Taking a course and would love to track using excel 2013, thanks. Oh, you are awesome. I’m looking forward for your teachings.

    Reply
    • Catalin Bombea

      July 6, 2019 at 5:03 pm

      Hi James,
      Generally speaking, a dashboard can be created for anything, as long as you have data for that.
      I guess it’s not just a dashboard you’re interested in, you might think about getting data too.
      You can find info here for collecting live data.

      Reply
  57. Suth

    June 27, 2019 at 12:53 am

    =Ncb(0,0,P5,R5,C8,C8,1)

    What formula function is that please anybody?

    Reply
    • Catalin Bombea

      June 27, 2019 at 1:36 pm

      Hi Suth,
      That does not look like an excel formula, looks more like a custom UDF (user defined function, built in Visual Basic).
      Cheers,

      Reply
  58. Mohamed Moqdad

    June 13, 2019 at 4:03 am

    Excellent Job.

    Thanks a ton..

    Reply
  59. karabo

    June 7, 2019 at 6:42 pm

    IF(B2>R1000,5%,10%)

    Reply
  60. steve

    June 6, 2019 at 9:32 pm

    How do I make a row turn green if Column N is greater than Column M, but do nothing if Col M is greater than Col N?

    And how do I make a row turn yellow if the text in Col B is made red using a conditional format?

    Thank you

    Reply
    • Mynda Treacy

      June 7, 2019 at 8:50 am

      Hi Steve,

      You need conditional formatting for this. Have a go and if you get stuck please post your question on our Excel forum where you can upload your Excel sample file and we can help you further.

      Mynda

      Reply
  61. Levi

    May 10, 2019 at 11:50 pm

    I have this formula entered in, it does exactly what i want.

    =IF((AND(C10>C9)), (G9+750), (G9))

    But if C10 is blank I want this cell blank as well…

    Please help, Thanks!!

    Reply
    • Catalin Bombea

      May 11, 2019 at 12:35 pm

      Try this:
      =IF(C10=””,””,IF(C10>C9, G9+750, G9)) (Your AND function is redundant, there is only one condition)

      Reply
  62. JoKN

    April 14, 2019 at 10:41 pm

    Yes, you are right, it does have a typo, my apologies.

    =SUMIFS(A1:A3,(B1:B3),”>0″)

    Reply
    • Mynda Treacy

      April 15, 2019 at 9:21 am

      Which yields the same results as the SUMIF formula Phil provided you:
      This:

      =SUMIFS(A1:A3,B1:B3,">0")

      And this:

      =SUMIF(B1:B3,">0",A1:A3)
      

      Do exactly the same thing. You don’t need SUMIFS because you only have one criteria, so SUMIF will suffice.

      Mynda

      Reply
  63. JoKN

    April 11, 2019 at 9:38 pm

    Thank you for your speedy reply.

    I tried your formula but it was still returning an error. Perhaps this is because I am using Excel 2007?

    I tweaked it a bit and found the solution
    =SUMIFS(A1:A3),(B1:B3),”>0″

    Thank you once again for your invaluable help in solving this for me!

    Reply
    • Mynda Treacy

      April 14, 2019 at 1:39 pm

      That formula must have a typo in it, because that’s not going to work as is.

      Reply
  64. JoKN

    April 11, 2019 at 1:01 am

    Hi,

    I’m compiling a spreadsheet to keep track of payments made. There are 2 columns, one with the amount to be paid and the other with the date that the payment was made.

    How do I make a formula that will keep a total of all that has been paid? I want the formula to only add the amounts that have a date entered in the column next to them.

    Thanks

    Reply
    • Philip Treacy

      April 11, 2019 at 10:41 am

      Hi,

      If your payment amounts are in Col A and your dates are in Col B try this

      =SUMIF(B1:B3,”>0″,A1:A3)

      Regards

      Phil

      Reply
  65. Susan

    April 4, 2019 at 8:29 pm

    my formula is great and works for what I want, but if there is nothing in the cells, it still shows KPI met. Where the previous cell is blank, can I get it to realize that and stay blank itself?
    formula is =IF(E2<=72,"KPI Met")
    Once I've put the formula in the cell, it states KPI met although the preceding cell is blank. Any ideas?
    Many thanks

    Reply
    • Catalin Bombea

      April 5, 2019 at 4:30 am

      Hi Susan,
      Try:
      =IF(AND(E2<=72,F1<>“”),”KPI Met”,””)

      Reply
  66. Sam

    March 15, 2019 at 4:26 pm

    What will be the function for all the cells to be replaced with P

    Reply
    • Mynda Treacy

      March 15, 2019 at 9:40 pm

      If you just want to replace everything with P you can select the cells and type in P then press CTRL+ENTER.

      Reply
  67. Sam

    March 14, 2019 at 3:28 pm

    I want a function in excel where when I enter “A” as absent in respective Cells for a month then other cells are marked as “P”.

    Reply
    • Mynda Treacy

      March 15, 2019 at 9:28 am

      Hi Sam,

      Something like this:

      =IF(B2="A", "P")

      Where cell B2 contains ‘A’ for absent.

      Mynda

      Reply
  68. Craig

    March 6, 2019 at 1:21 pm

    Guys, I need your help. I am creating a spreadsheet to calculate times, but I only wants excel to display the results if the HH:MM is less than 00:00. So in other words I only want the -00:00 times to be shown. I also want the negative result to shown with green font. Any brave soul wants to give this a try?

    Reply
    • Philip Treacy

      March 6, 2019 at 3:12 pm

      Hi Craig,

      You can’t have negative time. Maybe if you open a topic on the forum and supply your workbook + data we might be able to figure something out.

      Regards

      Phil

      Reply
  69. Imelda Sanchez

    March 1, 2019 at 1:30 am

    Good Morning,

    I have a list of Salaries and they vary from salary to hourly, i would like to specify Hourly or Salary on another column is the if function the appropriate function i should be using?

    Reply
    • Mynda Treacy

      March 1, 2019 at 1:55 pm

      Hi Imelda,

      Yes, you could use IF for this assuming there is some way to tell the difference between hourly pay and salary pay.

      Mynda

      Reply
  70. Dean

    February 1, 2019 at 11:51 pm

    Hello,
    I have written a spread sheet for horse racing,
    I would like to remove an amount of percentage from the winning bets (Commision) but not the losing bets,
    The amount to be removed (%) is in a seperate column,

    G5 is either a win amount say 2/1 showing as 2 or a lost bet showing as -1

    I5 is the percentage to be removed =SUM(J5*H5) stake times by percentage to be removed

    K5 is the column I need the formula in to show the total after % removed =SUM(J5-I5*G5) (win) or lossed stake in full from column J5 (Stake amount)

    Thank you
    Kind Regards
    Dean

    Reply
    • Mynda Treacy

      February 3, 2019 at 12:35 pm

      Hi Dean,

      There is information missing from your question e.g. what is in cell J5, and it’s difficult to visualize the contents of all of the cells mentioned. Please post your question in our Excel forum where you can upload your sample Excel file and we can help you further.

      Mynda

      Reply
  71. Yeswanth

    January 27, 2019 at 1:43 am

    How to calculate it in excel?

    Rs.100 Upto 20 Box, Above 20 Box – Rs.5/- Per Box

    Reply
    • Catalin Bombea

      January 28, 2019 at 2:08 pm

      Hi Yeswanth,
      Try this one:
      =IF(A1<=20,100, 5)

      Reply
  72. Ashley

    January 21, 2019 at 11:16 am

    Is it possible to write a formula stating if a cell is greater than 500 then multiply everything over 500 by .05?

    Reply
    • Catalin Bombea

      January 21, 2019 at 4:18 pm

      Sure, why not?
      In the next column, use this:
      =IF(A1>500,(A1-500)*0.05,0)

      Reply
  73. Sandra

    January 15, 2019 at 4:26 am

    Hello

    I want a formula that returns a value from a cell not the word true or false.

    Cell a =Ice so I want Cell C to equal the value typed in Cell D
    I have this but I get the word false instead of the number 12 that is typed in Cell D2

    =IF(A2=”Ice”, C2=”D2″,IF(A2=”Candy”,C2=”D4″,IF(A2= “Pop”,C2=”D5″))))

    Reply
    • Catalin Bombea

      January 15, 2019 at 2:38 pm

      Hi Sandra,
      The formula should be located in cell C2, you cannot choose the destination of the result in the formula:

      =IF(A2="Ice", D2,IF(A2="Candy",D4,IF(A2= "Pop",D5,"Other Case")))

      Reply
  74. Chad

    January 7, 2019 at 11:44 pm

    Please Help! I am trying to write a formula for calculating overtime in payroll. Basically if any value in column A is 40 or under it remains. If it is over 40, then in column B it calculates the difference from 40 and leaves 40 in column A.

    Thanks a Million!!!

    Reply
    • Catalin Bombea

      January 8, 2019 at 2:46 am

      Hi Chad,
      Sounds like a simple problem, try this in cell B1, copy it down as needed:
      =IF(A1<=40,0,A1-40)

      Reply
      • Chad

        January 8, 2019 at 3:19 am

        Thanks for the quick reply. The formula works but leaves the original number that is over 40 in column A. Can you let me know how to reduce the number to 40 and have the difference in column B. For instance employee X has 56 hours in column A, it should change column A to 40 and B to 16. Thanks for all your help!!

        Reply
        • Philip Treacy

          January 8, 2019 at 8:51 am

          Hi Chad,

          You can’t do that. A function will only return its result into the cell its called from. So a function in B1 will put a result in B1, it cant change the value of A1.

          You could take the values in A, have the worked time (up to 40 hours) in B and then have the overtime in C?

          Regards

          Phil

          Reply
  75. Rundull

    November 16, 2018 at 11:15 pm

    Please help me resolve this issue

    If A1=A or B then B1=1 else B1=2

    Thanks!

    Reply
    • Philip Treacy

      November 17, 2018 at 8:44 am

      Hi Rundull,

      Put this in B1

      =IF(OR(A1="A",A1="B"),1,2)

      Regards

      Phil

      Reply
  76. yogaraj

    October 10, 2018 at 3:56 am

    Dear Experts,

    Needed Help!

    I have listed 100 color in Cell A and 100 color in Cell B for double color cloth.(Ex:- Red,Blue)
    when i select cell A color and Cell B color it will be visible cell C.(Red/Blue)
    if i did not select in Cell B,the cell A color only visible in cell C.the cell B value don’t visible cell C.(Ex:-Red).
    How to manage this.? i have another one query if does not select cell B it will be auto hide without Macros?

    Thanks,

    Reply
    • Mynda Treacy

      October 10, 2018 at 9:29 am

      Hi Yogaraj,

      Please post your question on our Excel forum with a sample Excel file so we can see what you are trying to achieve. Please also clarify what you mean by ‘select’; do you mean click on a cell with your mouse, reference a cell in a formula or something else?

      Thanks,

      Mynda

      Reply
  77. Sandie

    October 2, 2018 at 2:59 am

    I am having a problem with the IF formula. Will you please help me with this problem.
    Create a formula using the IF function and structured references to determine the correct amount paid based on the following criteria:

    If the Repeat? value is “Yes”, calculate the amount paid by subtracting 5 from the Fee.
    Otherwise, the amount paid is the Fee value.

    How would I type this out using the IF Formula?

    Reply
    • Catalin Bombea

      October 2, 2018 at 2:10 pm

      Hi Sandie,
      Should look like this:
      =IF(A1=”Yes”,B1-5,B1) or: =B1-IF(A1=”Yes”,5,0) (they will always give the same result)
      If you want to apply the formula on a range that has more than 1 cell, it’s a different case, you should use:
      =SUM(IF(A1:A7=”Yes”,B1:B7-5,B1:B7)) (confirmed with CSE, it’s an array formula)

      Reply
  78. Syed Raiyan Karim

    September 23, 2018 at 7:29 pm

    It is very useful for self training

    Reply
    • Mynda Treacy

      September 24, 2018 at 2:41 pm

      Glad we could help, Syed.

      Reply
  79. Jimmy Ramirez

    August 23, 2018 at 1:12 pm

    Hi, I just need an explanation about the IF logic that I saw. It shows like this;

    =IF(ISBLANK($Z137),””,IF((INT($Z137)-$B137)<=IF($AC137="MIM",9,IF(OR($AC137="SGMA",$AC137="NGMA"),4,2)),"HIT","MISS"))

    Can you please explain it one argument at a time?

    Reply
    • Mynda Treacy

      August 23, 2018 at 2:28 pm

      Hi Jimmy,

      I’ll break your formula down into 3 parts. In English it reads:

      1. [IF(ISBLANK($Z137),””] IF Z137 is blank then return nothing and stop there.

      2. [IF((INT($Z137)-$B137)<=] Otherwise test if the integer of cell Z137 minus B137 is less than or equal to the result of the next IF. 3. [IF($AC137="MIM",9,IF(OR($AC137="SGMA",$AC137="NGMA"),4,2))] IF AC137 contains the text MIM, then return 9, otherwise IF AC137 contains the text SGMA OR NGMA then return 4, otherwise return 2. If part 2 compared to part 3 is true then return "HIT", otherwise return "MISS". Mynda

      Reply
  80. marco

    August 16, 2018 at 1:50 am

    =if(a1=22:00,b1,””) dos not working any help

    Reply
    • Mynda Treacy

      August 16, 2018 at 9:34 am

      Hi Marco,

      I suspect that 22:00 is either text or an actual time serial number.

      For text try:

      =if(a1="22:00",b1,"")

      For time stamps try:

      =if(a1=TIME(22,0,0),b1,"")

      Kind regards,

      Mynda

      Reply
  81. sarathy

    July 16, 2018 at 5:17 pm

    is there any formula to get the gross value when added salary and bonus.

    salary $k Bonus $k Gross $k
    138 nill 138+nil=138

    Reply
    • Catalin Bombea

      July 16, 2018 at 7:52 pm

      Hi Sarathy,
      What nil is? A percentage, a bonus amount? It does not look like an excel problem, more like basic math.
      If it’s a percentage (I assume you can make the difference yourself if it was an amount), try Gross/(1+Percent), where percent should have values like 10%, 30%, not 10, or 30.

      Reply
  82. Ali

    July 2, 2018 at 10:14 pm

    =IF(OR(K19>1,K19<J19,"Partial",IF(K19=J19,"Paid","Oustanding"))

    WHAT IS WRONG IN THIS FORMULA

    Reply
    • Mynda Treacy

      July 3, 2018 at 4:02 am

      Try:

      =IF(OR(K19>1,K19<J19),"Partial",IF(K19=J19,"Paid","Outstanding"))
      Reply
  83. 1234

    June 21, 2018 at 11:33 pm

    find two mistake
    =If(D5>=100,if(A4<=10,"100"),TRUE)

    Reply
    • Mynda Treacy

      June 22, 2018 at 9:44 am

      Your question isn’t clear, but if you want both logical tests to be true then use AND like this:

      =IF(AND(D5>=100,A4<=10),100,0)

      Mynda

      Reply
  84. Feleti Wolfgramm

    June 20, 2018 at 9:59 am

    “This is very useful tips and how you present your the formulas are easy to follow. Keep up the good work.”

    Feleti

    Reply
    • Mynda Treacy

      June 20, 2018 at 10:00 am

      Thanks, Feleti! Glad you found it helpful 🙂

      Reply
  85. Olusegun Osewa

    June 15, 2018 at 12:13 am

    Hi,

    I am really stuck with this IF Statement: =IF(F5=(I5+J5),”TRUE”,”FALSE”). It is not giving the right results. Please help.

    Reply
    • Philip Treacy

      June 15, 2018 at 8:16 am

      Hi,

      You don’t say what error you are receiving, when I type this out it works ok for me

      =IF(F5=(I5+J5),"TRUE","FALSE")

      I did notice that in your comment you had TRUE and FALSE encased in slanted/curly quotes ” but they should be in straight quotes

      "

      if you want to use strings (text).

      But neither TRUE nor FALSE actually need to be in quotes, you could just write it like this

      =IF(F5=(I5+J5),TRUE,FALSE)

      Regards

      Phil

      Reply
  86. Amy

    March 31, 2018 at 1:48 am

    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!

    Reply
    • Mynda Treacy

      March 31, 2018 at 8:00 am

      Hi Amy,

      Try:

      =IF(AD1<40,AD1,40)

      Mynda

      Reply
  87. SABIN GEORGE

    March 13, 2018 at 5:06 pm

    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?

    Reply
    • Mynda Treacy

      March 13, 2018 at 8:51 pm

      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

      Reply
  88. JJ

    November 30, 2017 at 8:48 pm

    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.

    Reply
    • Catalin Bombea

      December 1, 2017 at 4:12 pm

      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

      Reply
  89. Kirk Patrick

    November 28, 2017 at 8:20 am

    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.

    Reply
    • Mynda Treacy

      November 28, 2017 at 11:35 am

      Hi Kirk,

      Please post your question and sample Excel file on our Excel Forum where we can help you further.

      Cheers,

      Mynda

      Reply
  90. Diane

    November 19, 2017 at 2:39 pm

    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

    Reply
    • Catalin Bombea

      November 19, 2017 at 3:57 pm

      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)

      Reply
      • Diane

        November 20, 2017 at 12:49 am

        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

        Reply
        • Catalin Bombea

          November 20, 2017 at 1:10 am

          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.

          Reply
      • Diane

        November 20, 2017 at 12:56 am

        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

        Reply
        • Catalin Bombea

          November 20, 2017 at 1:12 am

          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

          Reply
  91. Karla

    November 16, 2017 at 2:12 am

    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.

    Reply
    • Mynda Treacy

      November 16, 2017 at 11:31 am

      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

      Reply
      • Karla

        November 17, 2017 at 2:35 am

        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?

        Reply
        • Mynda Treacy

          November 17, 2017 at 10:08 am

          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

          Reply
  92. nichelle

    November 14, 2017 at 12:38 am

    Can you please explain this equation for me?

    =IF(F6=””,””,WORKDAY(F6,ROUND(Gates!$B$2,0)))

    Reply
    • Mynda Treacy

      November 14, 2017 at 10:01 am

      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

      Reply
  93. Elinor

    November 4, 2017 at 7:58 pm

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

    Reply
    • Mynda Treacy

      November 5, 2017 at 9:04 pm

      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

      Reply
  94. Thomas

    October 26, 2017 at 4:03 pm

    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.

    Reply
    • Mynda Treacy

      October 26, 2017 at 9:11 pm

      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

      Reply
  95. MOHAMMAD AHSAN HABIB

    October 12, 2017 at 5:00 pm

    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

    Reply
    • Catalin Bombea

      October 13, 2017 at 3:49 am

      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))
      Reply
  96. Leslie

    September 17, 2017 at 3:53 am

    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.

    Reply
    • Catalin Bombea

      September 19, 2017 at 3:41 am

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

      Reply
      • Leslie

        September 19, 2017 at 8:39 pm

        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.

        Reply
        • Catalin Bombea

          September 20, 2017 at 3:09 am

          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

          Reply
  97. Kevin Lim

    September 14, 2017 at 2:11 pm

    =IF(H7=””,””,DATE(RIGHT(H7,4),MID(H7,4,2),LEFT(H7,2)))

    What does above formula do?

    Reply
    • Mynda Treacy

      September 14, 2017 at 2:57 pm

      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

      Reply
  98. Kaiser

    August 24, 2017 at 2:16 pm

    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.

    Reply
    • Catalin Bombea

      August 24, 2017 at 4:03 pm

      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?

      Reply
  99. Kate

    August 8, 2017 at 9:58 am

    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″])

    Reply
    • Mynda Treacy

      August 8, 2017 at 10:35 am

      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

      Reply
  100. Ashley Dickieson

    August 6, 2017 at 2:22 pm

    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 ?

    Reply
    • Mynda Treacy

      August 6, 2017 at 8:30 pm

      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

      Reply
  101. schnaider

    July 21, 2017 at 6:48 am

    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.

    Reply
    • Catalin Bombea

      July 21, 2017 at 1:31 pm

      Hi Schnaider,
      Try this one:
      =INDEX({0,0.4,0.45,0.5},MATCH(A1,{0,300,400,500},1))
      Catalin

      Reply
  102. Ray Passave

    July 15, 2017 at 2:46 am

    Very useful and, my goodness!, very well explained.

    Reply
    • Mynda Treacy

      July 15, 2017 at 7:41 am

      Thanks, Ray. Glad I could help.

      Mynda

      Reply
  103. Uche Uche

    May 30, 2017 at 6:28 pm

    I found it very useful because it is very clear to understand.
    Thanks

    Reply
    • Mynda Treacy

      May 30, 2017 at 8:27 pm

      Glad we could help, Uche 🙂

      Reply
  104. faezeh

    May 17, 2017 at 9:22 pm

    =IF(AND(“f2:m2”>Q2,”F2:M2″<R2),1,0)

    Reply
    • Mynda Treacy

      May 18, 2017 at 12:12 pm

      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

      Reply
  105. Jess

    March 22, 2017 at 10:31 am

    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.

    Reply
    • Catalin Bombea

      March 22, 2017 at 3:41 pm

      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

      Reply
  106. Fahmida Ferdous Maria

    February 9, 2017 at 8:04 pm

    Very helpful tutorial. Easy to understand , I am pleased.

    Reply
  107. Henry Hauck

    October 20, 2016 at 11:29 am

    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”

    Reply
    • Philip Treacy

      October 20, 2016 at 1:13 pm

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

      =IF(A1<3,"s",IF(AND(A1>3,A1<4.2),"m",IF(AND(A1>4.8, A1<6),"L","")))
      

      Cheers

      Phil

      Reply
      • Henry Hauck

        October 25, 2016 at 11:38 am

        Works fine thanks. As we sell timber by lengths I need to know short ,medium or long

        Reply
        • Philip Treacy

          October 25, 2016 at 7:44 pm

          You’re welcome.

          Reply
  108. Donna kilroy

    September 14, 2016 at 8:15 pm

    I am costing a project so if a person whose name is in c4 is working that day then e4 is £20.00

    Reply
    • Catalin Bombea

      September 15, 2016 at 12:44 am

      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?

      Reply
  109. Cheryl

    August 26, 2016 at 3:46 am

    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!

    Reply
    • Mynda Treacy

      August 26, 2016 at 8:39 am

      That’s great to hear, Cheryl. No stopping you now 🙂

      Reply
  110. Leia Morgan

    July 21, 2016 at 1:52 pm

    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

    Reply
    • Mynda Treacy

      July 22, 2016 at 5:30 pm

      Hi Leia,

      You need a nested IF statement like this in cell G4, then copy down column:

      =IF(E4="CNG",F4*1.7,IF(E4="CAM",F4,IF(E4="C$1",F4*1.5)))

      Mynda

      Reply
  111. Gwen

    June 18, 2016 at 5:51 am

    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.

    Reply
    • Catalin Bombea

      June 18, 2016 at 2:01 pm

      Hi Gwen,
      This one should work. Note that you don’t need double quotes around numbers:

      =IF(H4>=4,10000,0)

      Catalin

      Reply
  112. June Vendetti

    April 26, 2016 at 7:23 am

    These tips and tricks are great! I’m enjoying my review of Excel, and am learning more in the process.

    Reply
    • Mynda Treacy

      April 26, 2016 at 8:31 am

      That’s great to hear, June. Glad we can help.

      Mynda

      Reply
  113. LJ

    March 6, 2016 at 11:47 am

    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.

    Reply
    • Catalin Bombea

      March 6, 2016 at 2:11 pm

      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

      Reply
  114. suresh balaji

    February 23, 2016 at 5:06 pm

    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

    Reply
    • Catalin Bombea

      February 23, 2016 at 5:31 pm

      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

      Reply
  115. Anne Fernando

    September 19, 2015 at 10:04 pm

    Many thanks for explaining the ‘IF” Formula clearly

    Reply
    • Catalin Bombea

      September 19, 2015 at 11:33 pm

      Thank you for your feedback Anne, glad to see you’re happy 🙂
      Cheers,
      Catalin

      Reply
  116. Ramkumar

    September 18, 2015 at 6:14 pm

    100 Tips & Tricks book is very useful to me.
    Thanks a lot…

    Reply
    • Mynda Treacy

      September 18, 2015 at 7:25 pm

      Glad you liked it, Ramkumar 🙂

      Reply
  117. abdelfattah

    September 12, 2015 at 7:01 pm

    if i wanna to make formula to write a date of today if another cell bigger than 0

    Reply
    • Mynda Treacy

      September 14, 2015 at 9:24 am

      How about something like:

      =IF(B2>0,TODAY(),"")

      Where B2 contains the value in the ‘other cell’.

      Mynda

      Reply
  118. Mark D

    July 31, 2015 at 6:59 am

    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.

    Reply
    • Mynda Treacy

      July 31, 2015 at 12:46 pm

      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

      Reply
  119. Faridz Ridzuan

    July 29, 2015 at 12:52 pm

    Hi Mynda , thank you very much .

    Reply
    • Mynda Treacy

      July 29, 2015 at 1:03 pm

      You’re welcome, Faridz 🙂

      Reply
  120. Sujit Pokhrel

    July 2, 2015 at 2:10 am

    I need help with a function.
    e.g if (E6=16, “AA” or E6=40,”BB”or E6=50,”CC”)

    Can anyone help me?

    Reply
    • Catalin Bombea

      July 2, 2015 at 3:32 am

      Hi Sujit,
      Maybe this is what you want?

      =IF(E6=16,"AA",IF(E6=40,"BB",IF(E6=50,"CC","")))

      Catalin

      Reply
  121. Mohamed Saliha

    April 28, 2015 at 6:43 pm

    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

    Reply
    • Mynda Treacy

      April 28, 2015 at 6:57 pm

      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

      Reply
  122. Grim

    March 10, 2015 at 1:56 am

    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?

    Reply
    • Catalin Bombea

      March 10, 2015 at 3:26 am

      Try this:
      =IF(C14<=5.99,C14,6) Catalin

      Reply
      • Tone

        January 18, 2020 at 5:41 am

        I’ve learnt more in these comments than anywhere else. Thanks.

        I did this…

        =IF(C14>5.99,6,C14)

        Oh, I get it – same thing, different thing 🙂

        Reply
  123. raymark

    November 5, 2014 at 4:30 pm

    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"),""))))))

    Reply
    • Catalin Bombea

      November 6, 2014 at 2:56 am

      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

      Reply
  124. 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
  125. 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
  126. 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
  127. 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
  128. 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
  129. 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
  130. 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
  131. 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
  132. 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
  133. 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
  134. 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
  135. 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
  136. 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
  137. 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
  138. 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
  139. 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
  140. 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
  141. 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
  142. Chamnan Rodpai

    March 5, 2013 at 11:54 pm

    Very good explanation,thankx for your kind.

    Reply
  143. 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
  144. 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
  145. 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
  146. 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
  147. 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
  148. 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
  149. 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
  150. 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
  151. 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
  152. 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
  153. 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
  154. 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
  155. 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
  156. 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
  157. 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
  158. joseph

    November 12, 2012 at 5:39 pm

    kudos, excellent job

    Reply
    • Mynda Treacy

      November 12, 2012 at 10:33 pm

      Thanks, Joseph 🙂

      Reply
  159. 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
  160. Sanat

    October 16, 2012 at 7:25 am

    This is too good dear

    Reply
  161. 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
  162. 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
  163. 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
  164. Sachin

    September 14, 2012 at 2:54 pm

    Excellent

    Reply
    • Mynda Treacy

      September 14, 2012 at 3:27 pm

      Cheers, Sachin 🙂

      Reply
  165. 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
  166. 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
  167. 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

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

          • Shan

            December 14, 2012 at 5:21 pm

            thats good……

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

          • 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

          • Darren

            March 3, 2013 at 12:53 am

            Thank you CarloE

          • 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

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

          • Carlo Estopia

            March 3, 2013 at 11:20 am

            Darren,

            I’m glad you pulled it.

            Cheers.

            Carl

  168. 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
  169. 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
  170. 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
  171. 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
  172. 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
  173. 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
  174. 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
  175. 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
  176. 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
  177. 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
  178. 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
  179. Romeo Levanza Jr.

    February 17, 2011 at 10:42 pm

    I really appreciate your tutorial video.

    God Bless,

    Reply
  180. Debbi

    November 8, 2010 at 3:52 pm

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

    Debbi

    Reply
  181. 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
  182. 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
  183. 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

Trackbacks

  1. Excel 2007 – AND and OR Functions Explained says:
    October 2, 2010 at 9:31 pm

    […] AND and OR formulas on their own aren’t very exciting, but mix them up with the IF Statement and you’ve got yourself a formula that’s much more […]

    Reply
  2. Excel 2007 – AND and OR Functions Explained says:
    October 2, 2010 at 9:31 pm

    […] AND and OR formulas on their own aren’t very exciting, but mix them up with the IF Statement and you’ve got yourself a formula that’s much more […]

    Reply
  3. Excel 2007 – COUNTIF and COUNTIFS formulas explained says:
    September 22, 2010 at 1:25 pm

    […] like the IF Statment and SUMIF formula, the COUNTIF and COUNTIFS are based on logic.  This means you can employ […]

    Reply
  4. Excel 2007 SUMIF and SUMIFS Formulas Explained says:
    September 13, 2010 at 2:33 pm

    […] like the IF formula, the SUMIF and SUMIFS are based on logic.  This means you can employ different tests other than […]

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x