Comments on: How to use IF function in Excel: examples for text, numbers, dates, blanks

IF is one of the most popular and useful functions in Excel. Generally, you use an IF statement to test a condition and to return one value if the condition is met, and another value if the condition is not met. Continue reading →

Comments page 40. Total comments: 4822

  1. I need a formula to calculate a value from a group of numbers in a column:
    Column A - has dollar figures
    Column B - has dates WHEN the amount in column A is paid.
    I want to calculate the unpaid amounts from column A when column B doesn't have a date entered or a blank cell.

  2. hello iam sandip mere ko aisa formate make karna he exm:-90 he to 75 ke above 10 ana chahiye 81 ke above 15 86 ke above 20 91 ke above 25 ager 75 ke below rahega to 0 ana chiye mene ye formate banaya he mager ye kam nahi kar rha he,
    =IF(C4>=76,"10",IF(C4>=81,"15",IF(C4>=86,"20",IF(C4>=91,"25",IF(C4<=75,"0")))))
    please help me my whatsapp no.8669800963

  3. I am trying to write a formula to distinguish between if a serial # starts with a Letter or a Number then multiply by two different % rates . Any help would be great.

  4. I am using an IFS function to write a color ("Red", "Yellow", or "Green") in cell F4. It is dependent upon the results (a number between 1 & 5) in another cell (AA4). If I type a number into the cell AA4, the formula yields the appropriate color. However, when I use a formula (Average) to generate the number, it does not work. I get an error message (#N/A in Excel and #Name? in Sheets). How do I get the IFS function to recognize the results in the cell instead of the formula?

    1. Problem solved. It was a rounding issue. Even though I could only see a 1, 2, 3, 4, or 5, the underlying numbers were 1.??, 2.??, etc., so the IF statements were not true. I used the Round function in the Average formula and solved the problem.

  5. How to enter some number in another cell according to ented number in first cell. ie. If 1 is enterd in column 1st then atomatically 300 (rate per kg) is enterd in money column. Please answer quickly. Thankyou for readin and answering me.

  6. Hi All
    Can you please help me with a formula? I need to create a monthly report.
    Reporting period is from 26th previous month to 25th of the current month for example for May 2019 reporting commence from 26th April to 25th May.
    Can you help? Many thanks in advance.
    Jonathan

  7. Hi There,
    Please help me to come up with the formula i needed.
    In a column, if there is a value encoded the result must be 8 or 0 only.
    How is it?

    Thanks a lot.

  8. Hello,
    I'm trying to condition a cell (A) color based on 4 other cells (B,C,D,E).
    The first one is a case number and the 4 cells are attempts to reach a customer via phone.
    So, if a first call is made and the customer is not reached, B cell will contain text, A cell will change to yellow.
    When a second call attempt will be made, C cell will contain text and A cell will need to change to orange.
    After the third call attempt is made, D cell will contain text and A cell will need to change to red.
    If the customer is reached at any given point (1st,2nd,3rd attempt), E cell will change to value (Y) and A cell will need to change to green.
    Can you help with this?
    Thank you

  9. Hello Team
    How to find the TP value with IF condition by using TYPE.
    TYPE ENTRY PRICE TP
    Sell 1.34365 1.34165
    Buy 0.69792 0.69992
    Please help me with this.

    Thanks

  10. I am trying to write an equation that will do the following:
    If the value for B6*0.25 is less than 1,000, I want to display 1,000, and then if the value of B6*0.25 is greater than 1,000, I want it to print that value. Below is my failed attempt. Thank you for your help.
    =IF(B6*0.251000,B6*0.25))

    1. =IF(K133*0.25<1000,"1000",0)

  11. Hello I'm trying to write an IF statement that results in showing two conditions when met. For example, I input -5 in the reference cell I want the IF statement to punch out 5 below or if the number is positive to punch out 5 above in the same cell.

    1. Use this;
      =IF($R14<0,($R14*-1)&" Below",($R14*1)&" Above")

  12. Hi I am trying to use the if function but I am stumped.
    I need me S/Sheet to be blank is 0, if between 1 and 28 show yes and higher than 28 No.
    My current formula is =IF((AND(F2>=1, F2<=28)), "Yes", "No") but the everything with a 0 gets a no when I need it blank
    HELP PLEASE xx

    1. Use this;
      =IF($F11=0,"",IF($F11<=28,"Yes","NO"))

  13. In excel there was 8 sheets, on that 1 sheet is the master sheet
    If we enter any email id in column A in any of the 8 sheets it has to auto reflect into Master sheet on another below.
    Ex. in sheet 2 I have entered abc@gmail.com
    in sheet 3 I have entered bcd@gmail.com
    in sheet 7 I have entered jkq@gmail.com
    result should be entered mail auto reflect into master sheet one another below

    Please help in this regard

  14. I am trying to do travel rates based on where an employee visits. I would like to create a formula that includes 50 states and top cities within those states.
    So if John travels to Washington State he choose WA from a drop down box. From there in the next cell cities will become available such as Seattle, Tacoma and Spokane. Based on which city John chooses travel rates will appear in the next cells such as acceptable hotel rates, breakfast, lunch and dinner. If John chooses AZ then Flagstaff, Phoenix, Sedona and Tucson appear...makes sense? Possible?

    Hope anyone can help

    1. Yes it's possible with conditional drop down list.
      Need data work on it.

  15. COLUMN A COLUMN B COLUMN C RESULT

    OTE3‐L1‐IM‐300992‐01 12-Apr COMPLETE
    OTE3‐L1‐IM‐300992‐01 13-Apr COMPLETE
    OTE3‐L1‐IM‐300992‐02 13-Apr INCOMPLETE
    OTE3‐L1‐IM‐300992‐02 INCOMPLETE
    OTE3‐L1‐IM‐300992‐03 13-Apr INCOMPLETE
    OTE3‐L1‐IM‐300992‐03 INCOMPLETE
    OTE3‐L1‐IM‐300992‐03 INCOMPLETE
    OTE3‐L1‐IM‐300992‐03 INCOMPLETE
    I need a result of complete or incomplete when look at the

  16. StockName CurrentQty InvestmentPrice CurrentMarketPrice HoldingValue MarketValue
    BGRENERGY 14 483.00 58.25 6762.00 815.50
    BRIGADE 29 390.00 229.25 11310.00 6648.25
    BSE 18 806.00 610.45 14508.00 11132.10
    COALINDIA 100 232.75 250.50 23275.00 25050.00
    DIXON 8 1766.00 2353.95 14128.00 18831.60
    DLF 5 932.52 174.30 4662.60 871.50
    EDL 69 371.70 6.80 25647.30 469.20
    ENDURANCE 30 472.00 1156.20 14160.00 34686.00
    ERIS 24 603.00 632.75 14472.00 15186.00
    GICRE 32 433.50 244.60 13872.00 7827.20
    Please sum col E & F How to a cetain text numbers

  17. In column A (there are 4 sentence choices: I want an Apple, I don't like Bananas, I love coconuts, I need water)

    A1 I want an Apple
    A2 I don't like Bananas
    A3 I love coconuts
    A4 I need water

    In column B, I want to use a function to check column A and search for partial text, then past a value in cell. For example, in B1, checks for 4 possible conditions in A1, check for "ppl" and copy the value "Yes" to cell in B1 OR check for "Ban" and copy the value "No" to cell B1 OR check for "coco" and copy the value to B1 OR check for "wat" and copy the value "end" to B1.

    What function should I use?

    1. If (AND condition works here..

  18. In column A (there are 4 sentence choices: I want an Apple, I don't like Bananas, I love coconuts, I need water)

    A1 I want an Apple
    A2 I don't like Bananas
    A3 I love coconuts
    A4 I need water

    In column B, I want to use a function to check column A and search for partial text, then past a value in cell. For example, in B1, check A1 for "ppl" and copy the value "Yes" to cell in B1

    What function should I use?

  19. How can I formulate a cell to cover a range of one column and when the number in that column reaches zero or below zero, the cell will populate with a column next to the original range with that 2nd column's date.
    Example column A shows balance, and when loan balance reaches zero (or below) populate the date in the column next to show when loan is paid off.

  20. I need an example, which shows if cell contains the specified the value it should return as it is, if not it should run the following formula.

  21. HOw to write the following using IF ?

    0.70-0.75 1
    0.75-0.80 0.9
    0.80-0.85 0.8
    0.85-0.90 0.7
    0.90-0.95 0.6
    0.95-1.00 0.5
    1.00-1.05 0.4
    1.05-1.10 0.3
    1.10-1.15 0.2
    1.15-1.20 0.1
    1.20-1.25 0

  22. Hello;
    I have the formula which i stated below, doesn't work. Could you help me on this matter.
    The idea is: i will insert the data in C30 and if it's blank it will show "Please insert rank in cell C30" but if it's not blank it will show the value that searched through the table which is between "b33 and b41". So far it worked (without using AND formula).
    Lastly i want the insert values to be between 0 and 10. So i put the and formula but it doesnt work.
    Sincerely.
    =AND(IF(ISBLANK($C$30);"Please Insert Rank in cell C30";(INDEX($B$33:$B$41;$C$30)));(c30>0;c30<10)

  23. Hi, im looking for the right formula to identify the value statement.
    example:
    Category Description Amount BDO Eastwest
    Cash In BDO 500 (formula?)- on how to appear the amount

  24. if i want to input a value in cell A1, then each time the output value in cell B1 must be added to that value. in this scenario which formula i should use ?

  25. Hi, I'm trying to create a formula that will provide a "True" statement if the cell is blank between 1/1/19 and 5/30/19. Then a formula to identify blank cells after 6/30/19. I'm using these formulas for conditional formatting.

  26. I want to see automatic date in cell if I enter some dispatch quantity
    Example--
    Date Dispatch Quantity
    Today Date 150 and any other number >0

  27. is there any formula to find out the total figer of a person whose name in a sheet 3 to 4 time.

  28. AMT Clientname client code
    42560 SAP 1234
    31760 DELL 4321
    2150 INTEL 3412

  29. i want to user if and in single formula
    exp:A=>1&B=SAP=1234
    A=>1&B=DELL=4321
    A=>1&B=INTEL=3412

  30. I want to know how to work this formula out: in cell M1, I add F1+ I1+J1+K1+L1, but if the total in cell F1 an I1 is 0 then the total should be 0

  31. Can you please help with a formula to calculate a Balance column for a Cost and/or Refund for an expense sheet. I cannot figure out how to set up my balance column to calculate when a cost or refund was entered and give the new balance (either an addition for the refund or subtraction for the cost). The header for each column for the sheet looks like this:
    Date Initials Client Name Description Cost Refund Balance

  32. Hi,
    I am Aaditya please help me how can set this formula
    Data
    If A, B and C dealer has Purchased goods in different slab
    Slab Slab Point Purchase
    Silver 8000 7200
    Purple 5300 4900
    Blue 3000 5300

  33. HI i am having on question
    i am having a excel file that contains website names
    in my sheet eg
    abc.com
    abc.com/test
    abd.com
    abc.com/hello
    I need to highlight those cells which Matches first 15 letter's
    Answer would be Highly appricated

    thanks

  34. I have a list that is a drop down
    hello
    goodbye
    so sad
    I have a summary column that I just want a numerical value to appear in. So if someone chooses goodbye my summary column would show 2. If someone chose hello is would show 1 etc. What is the formula I should have in the summary column for it to determine this? Lets say the drop down values are in column B2.

  35. Hi, how would you write
    if a range of cells contains L, then add 9.5

  36. 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".

  37. any idea how to make formula if I need something like this
    I have few different numbers (example 35413565 , 14451844) nd I need if first number is 3 then text in other column is (ex.YES) or if is 2 then text NO.
    Could someone help ?

  38. =IF(J10="","",(IF(J10>=75%,"HIGH"),(IF="",""(IF(J10,˂49"LOW,"MODERATE"))) my formula dont work

  39. Hi All ,

    I hope everyone is well !

    I currently have the following in a cell =((H21-G21)*24-1)

    this simple formula allows me to count the number of hours between two times ( when working out working hours for my staff)

    I am looking to have this cell note that if a time is not put into the cells ( h21/g21 ) this information is current data validation from another sheet - and instead the word "holiday" is added the cell will generate an automatic number ? is this possible ? any help would be great !

    Out normal working hours for a "holiday is 8 hours" so if the cell says holiday i would like it to default this to "8"

    thanks again

  40. trying to write a formula that will look at 3 inputs and assign a point value.

    i need a formula that will assign 3 points to the highest score, 2 points to the second highest and 1 point for the lowest.

    thanks

  41. I am pulling my hair out on a formula. I have a workbook with 5 tabs ( Gen212, Poe,360 BWS, sample, Production). What would the formula be to have a flow number from tab 5 (production) in column H row 7 automatically populate into tab 3 (360 BWS) in column C row 18?

  42. Can someone help with formulas for the following 2 scenarios:
    1. Groups submitted from the 25th of the previous month
    -For month of February: statement is true if the date is 1/25/19 thru 2/28/19
    2. Groups submitted from the 25th of the current month
    -For month of February: statement is true if the date is 2/25/19 thru 2/28/19
    Thank you!

  43. I have two columns. Column G is my due date and column J says whether I have approved or denied the application. If the due date is in the next 7 days, from today, and the corresponding cell in column J is blank (meaning I have not approved or denied the application) I want the cell in column G to highlight, or the row, to let me know the due date is coming up and I have not taken action on the application yet.

  44. Hello-

    I am in desperate help of trying to figure out an if statement that can help me calculate # of days past due.

    I have a column for start date, end date and actual date of completion. I have a column with a formula for today’s.

    Originally, I used the networkdays formula to calculate the days from due date to today’s date, but when I put in the actual completion date, the past due days keeps updating and I don’t want that.

    Have no idea if that made any sense. But hopefully someone can help me out !!

  45. Hi,
    May i Know how to use TODAY function when my table has different dates,
    Date-1, Date-2,Date-3,
    How to get the same data on one column based on IF Function,
    thanx

  46. Hi,

    I want to write a function that will find alternate minimum value for a range where logical value TRUE equal 1 and the logical value FALSE equals 0, and the text and blanks equals 0.
    Thanks,

  47. G 12 is an unknown which is a value where i keyed in, meanwhile E12 is a value which i obtained through a returned value from another keyed in a value, both G 12 & E 12 multiply each other and should not give you more than 220, if it does, it supposed to print out 220 or else the value is supposed to be the the equation provided,need some help as i am getting errors when executing it

  48. Problem getting IFS formula to work.
    Problem I want the formula to read the cell and if it contains a certain text,then it is to input certain text, and if it is another text,then input another text,if the cell contains a certain text,then input a certain text.

    Example:
    Cell: =IFS(C1="CAR","A.V",C1="NQR",C1="SCAR")
    Formula only regonizes CAR and inputs A.V.,but will not work for other two conditions.

  49. Hi Svetlana,
    How would I write a formula for time zone conversion sample:
    Cell D2 Local Time, E2 is the GMT Conversion and L2 is the Converted TIME Zone
    is it like; =IF (E2=+2), D2+600
    My logic is: The time in L2 is the equivalent of D2+600 if E2 has value of +2
    Hope you could reply.

  50. What my goal is to have a set of cell content only (B4:K6)to empty at 6am daily. Is it possible and if so how do I accomplish this task?

    Thank you.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)