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 13. Total comments: 2999

  1. Hi

    Can anyone help me on this? I am creating a formula for time and date. If the time exceed at 5:00 PM. The date and time will appear the next day and the time will be 9:00 AM.

    Example

    8/1/2017 5:00 PM result 8/1/2017 5:00
    8/1/2017 5:02 PM result 8/2/2017 9:02

    What is formula for this?

    1. Hi, your explanation and example don't quiet marry up, so I will give you 2 solutions.
      Info:
      9AM is 09:00 ( 9/24)
      5PM is 17:00 (17/24)
      From 5PM to 9AM is +1 day -8 hours or +16 hours (16/24) (2/3)
      #1 After 5PM show Tomorrow 9AM
      IF( ( A1-INT(A1)) > (17/24), INT(A1) +1 +(9/24), A1)
      #2 After 17:xx show Tomorrow 09:xx
      IF( ( A1-INT(A1)) > (17/24), A1 +(2/3), A1)
      # This can get really complex if you want ( 09:00-17:00 M-F, NO Change, Otherwise set time to next work day)

  2. Could you please help?
    I need a warning when transposing numbers in different columns using IF function. Trying to check bank balance in (A1) with other itemised columns, only one value per row.have tried =IF((B1,C1,D1,E1,)=A1,"OK","NG") Have received #value results at times. Cheers Garry

    1. Hello, Garry. Thank you for contacting us.

      If I understand your task correctly, the formula below should work for you:

      =IF(OR(B1=A1, OR(C1=A1, OR(D1=A1, OR(E1=A1)))), "OK", "NG")

      If the task is different, please describe it in more detail. I'll do my best to help you.

  3. How to create a IF formula for Cell that contains percentage between 95% - 100% will be stated as "ACTUAL", less then 95% will be stated as "DOUBLE", between 100% - 115% will be stated as "FIFTY PERCENT", above 115% will be stated as "NIL"

    1. Hi Virendra,

      Copy the following following formula in B2 (the answer cell):

      =(IF(A2>114.9%,"NIL",IF(AND(A2>=100%,A2=95%,A2<=100%),"ACTUAL",IF(A2<95%,"DOUBLE")))))

      Format cell A2 as percentage.

      The premise:
      94.9% and below = DOUBLE
      95% to 99.9% = ACTUAL
      100% to 99.9% = FIFTY PERCENT
      115% and above = NIL

      Format cell A2 as percentage.

      The only problem with this formula is that if nothing is mentioned in cell A2 the answer always displays "DOUBLE".

      Hope Svetlana Cheusheva or someone can improve upon the formula.

      Regards,
      John Sanil

      1. Dear Virendra
        Sorry the premise (100% to 99.9%)
        should read as: 100% to 114.9%.

        Rest all remain the same.

        John

  4. Need help with correct formula for:

    Parcels less than 2kg in weight are charged at 0.365cents per gram
    Parcels over or equal to 2kg in weight are charged at 0.425cents per gram

    Thank you.

    1. Hello, Marie. Thank you for your question.

      Let's suppose that the parcels' weight is in A1.
      You put the following formula into B1 in order to get the charge for the parcel:

      =IF(A1<2, "0.365 cents per gram", IF(A1>=2, "0.425 cents per gram"))

      I hope this helps. Please let me know if you have any other questions or difficulties.

  5. P001 OW RF 4/23/2016 0:00
    P001 OW RF 4/23/2016 0:00
    P001 OW RF 4/23/2016 0:00
    P001 OW RF 4/20/2016 0:00
    P001 OW RF 4/19/2016 0:00
    P001 OW RF 4/19/2016 0:00
    P001 OW RF 4/19/2016 0:00
    P001 OW RF 4/19/2016 0:00

    I have this data in sheet1 whereas i have, the below data in sheet 2

    4/19/2016 0:00 4/20/2016 0:00 4/23/2016 0:00 4/24/2016 0:00
    P001

    i want a formula which can highlight corresponding cell in sheet 2 if the date against P001 occurs in sheet1.

    1. can anybody help pls.

  6. How to display, if( c2= a(alphabet) or b or c or d or e or f or g, the value is true , or value is false). Please help

    1. Hello,
      You can try the following formula:

      =IF(OR(C4="A", C4="B", C4="C", C4="D", C4="E", C4="F", C4="G"),TRUE, FALSE)

      In case you need the whole alphabet, use this one:

      =IF(OR(C2="A", C2="B", C2="C", C2="D",C2="E", C2="F", C2="G", C2="H", C2="I", C2="K", C2="L", C2="M", C2="N", C2="O", C2="P", C2="Q", C2="R", C2="S", C2="T", C2="V", C2="X", C2="Y", C2="Z"),TRUE, FALSE)

      I hope this helps. Please let me know if you have any other questions or difficulties.

  7. Hi!
    Great website. I'm learning a good bit. Thanks.

    I'm trying to configure a simple budget. I have two columns: one is a category (e.g., "Groceries"). The second is the amount purchased.

    I want to Sum all "Groceries" formula. How would I do this?

    It would start like this...

    =IF(C2="Groceries",

    Thanks!
    Daniel

    1. Hello, Daniel. Thank you for your question.

      You can use the SUMIF function to configure your budget. Let's suppose that you have a table with types of your costs in column A and the amount purchased in column B. Please put the word "Groceries" in cell D2 and following formula in E2:

      =SUMIF(A2:B8, D2, B2:B8)

      You can learn more about this function in our blog article. I hope you'll find this information helpful.

      I hope this helps. Please let me know if you have any other questions. I'll be happy to help!

    2. you can use formula =sumif

  8. I am trying to build a price list based on tiers. If C2 is less than 20, multiple it by two, if 20 to 99 multiple by 1.54%, if 99 to 599 multiple by 1.43, if 599 or more multiple by 1.34. Also C2 needs to have a tax rate of .08517 added to it but before commission markup.
    Ex: $20 x .08517 = 1.71 $20 x 2 = $40 plus $1.71 tax = $41.71.
    I keep getting errors.

    1. Hello, June,

      as for the first part of your task, you can use this nested IF:
      =IF(C2<20,C2*(1+2%),IF(AND(C2>=20,C2<99),C2*(1+1.54%),IF(AND(C2>=99,C2<599),C2*(1+1.43%),IF(C2>=599,C2*(1+1.34%),""))))

      As for the second part, could you please describe it in more details? Thanks

  9. if(B5>20,B5<50,"20-50") How to work, please find like this

    1. Hello

      Try the following formula:
      =IF((AND(B5 > 20, B5 < 50)), "20-50", " " )

      You can also read more about IF formulas with multiple conditions here

      Feel free to contact us if you have any other questions.

      1. Hi,
        Please correct this,
        =if(A<B,B-A,IF(B=0,0,""))

  10. Have different tables that have been joined into one spreadsheet
    table 1 has account open date, account ID#, & last updated
    table 2 has account ID, code, name, status, & update code

    Column A1 has an ID#
    Column B1 has a Code#
    Column C1 has a Name
    Column D1 has a Status
    Column E1 has an Update Code
    Column F1 has the Last Updated Date

    I have duplicate account IDs that are missing vital information.
    I would like to attach that information to row that contains the account open and account ID

    How can I get B2 & C2 into B1 & C1 if A1 = A2?

    1. Table 1
      ID Up Date
      18318ZE3-B96F-448F-8501-70625246C49E 20170404
      Table 2
      ID Code Name Stat UC
      18318ZE3-B96F-448F-8501-70625246C49E 1122 Jones A 1

      In Excel

      ID Code Name Stat UC Date
      18318ZE3-B96F-448F-8501-70625246C49E 20170404
      18318ZE3-B96F-448F-8501-70625246C49E 1122 Jones A 1
      18931ZE3-D97F-449A-8571-78452211D12G 20170301
      18898ZE3-K96F-446F-8581-70625246C49G 1123 Smith A 1
      18998ZE3-H96F-448L-8502-71625246C49A 20170404
      18998ZE3-H96F-448L-8502-71625246C49A 1124 Brown A 1

  11. Please help me to use this funtion

    Example:
    culum a colum B
    abc 10
    dfe 7

    I want to show that: If (abc & 10) > 6 "fhggh", If (dfe & 7) > 4 "ssjfj"

    Thanks

    1. Hello, Phun,

      if I understand your task correctly, this formula should do:
      =IF(AND(A1="abc",B1>6),"fhggh",IF(AND(A1="dfe",B1>4),ssjfj,""))
      If you mean something other than that, please explain your task in more details.

  12. Hi

    I need to asign a value to prices which falls in a specific range

    0-50000 = Level 1
    50001 - 300000 = Level 2
    300001 - 500000 = Level 3

    This is what i did, but that only gives a value for the first level.

    =IF(E2:E10<=50000,"Level 1", "Other Value")

    1. Hi, Johan,

      please try this formula:
      =IF((COUNTIF(A1:C1,"<=50000"))>0,"Level 1",(IF((COUNTIF(A1:C1,">=50001")-COUNTIF(A1:C1,">300000"))>0,"Level 2",(IF((COUNTIF(A1:C1,">=300001")-COUNTIF(A1:C1,">500000"))>0,"Level 3","")))))

      Please note that the first criterion will be checked at first, and if there's at least one cell in the range that falls under this criterion, the rest won't apply.

      If you need something other than that, please specify.
      Hope it helps.

  13. Hi,

    I am trying to create a spreadsheet to calculate high school grades using the weighted average and weighted percentage method.

    My problem is, I want to show that if a student is absent for 1 or 2 assignments or tests, they will not be penalized in their average assignment grade.

    How then can I write a IF function to show this calculation?

    Thanks for your usual assistance.

    1. Hi, Hamack,

      for us to be able to assist you with an approximate formula, please specify the names of the columns you have, what values are stored there, and how you indicate that the student was absent.

      Thanks!

  14. Hi, I'm creating a formula that once your if you change status to Complete in column B, then column C will provide the current date

    1. Hi, Nyleve,

      if the status is in B2 and you need the result to return into C2, here's a formula:
      =IF(B2="Complete",TODAY(),"")

      Then copy the formula down column C.
      Please note that if B2 doesn't contain "Complete", C2 will remain empty.

  15. I have two workbooks that I am trying to link together. The first workbook is Participants' Weight. The cell I am looking at is G2. If G2 is blank, I want to only add cells C3, E3, G3, and I3 on the second workbook (Nutrition and Fitness). If G2 has a value, I want it to add that value PLUS C3, E3,G3, and I3. Can you please help with my formula? This is what I have..

    =IF(ISBLANK('Participants'' Weight'!G2),=SUM('Nutrition & Fitness'!C3+'Nutrition & Fitness'!E3+'Nutrition & Fitness'!G3+'Nutrition & Fitness'!I3),=SUM('Nutrition & Fitness'!C3+'Nutrition & Fitness'!E3+'Nutrition & Fitness'!G3+'Nutrition & Fitness'!I3+'Participants'' Weight'!G2))

    1. Nevermind, I figured it out!

  16. Hi, I'm trying to create a schedule for our workers using the schedule for our kids. I'm trying to figure out how to put in a formula that says " If Cell A3 on Sheet 1 equals "JohnDoe" then cell A2 on Sheet 1 will be copied to cell A2 on Sheet 3". Is there a way I can do this?

    1. =IF(Sheet1!A3="enter text here", Sheet1!A2, " ")

      Enter this formula into A2 on sheet 3. Where it says "Enter text here" you can either enter the text you want to use or take away the quotations and enter a cell like B2...

      Drag the formula all the wya down

  17. Hi

    I'm trying to get an IF formula to work but I am having trouble. I'm trying to do an IF formula for both cells. I want the formula to return the number 2 if cell D7 is not blank (ie has data in it), return the number 6 if cell E7 has data in it or remain blank if neither of them have data in. Can anyone help please?

    1. =IF(D7>0, "2", IF(E7>0,"6"," "))

      I am not sure if this is what you are asking

  18. Hi,
    I try to do like, =IF(A1="no","no need to fill in the blank", "") and If the A1 = "yes" I need to fill in the blank. How to do that? because if I do like above IF statement, the IF statement will gone when A1 = "yes" after I filled the blank.

    1. =IF(A1="yes", "Fill in the blank", IF(A1="No","Do not fill in the Blank"," "))

  19. I need a formula that can place the date from sheet 1 on sheet 2 when a number value is greater than or equal to a set number. The date would be in the same row in sheet 1 as the number. Example sheet 1 column A row 1 (5/5/17 entered) column B row 1(405 entered) set value is 400. 5/5/17 would then be shown in cell choosen on sheet 2.

    1. Example:

      =IF(405>=400, Sheet2!A1, "" )

    2. Example:

      =IF(405>=400, Sheet1!A1, "" )

  20. I need help for this thankx.if cell b1 greather than 0 then add A3 and B2 if not return A3 VALUE in b3 .

    1. use the or formula within cell B3...
      =IF(0>B1, B2+B3, A3)

  21. I am trying to use an if/than stmt and change the color of the cell based on that stmt. example if the cell is greater than or equal to than $1000 i want the cell to turn green

    1. Use the conditional formatting tool. Any number great than or equal to 1000 turn green.

  22. Hello,

    need to create a formula that would, if not manually entered number in cell, the formula would read data from other cell.

    example:
    in cell A1 if i type 10 it says 10, but if i skip it then it would read from cell D1 where i get the number from some equation.

    Thank you

    1. Hello, Alen,

      I believe, the formula below will help you:
      =IF(ISNUMBER(A1),A1,D1)

      It will return any numeric value from A1, but if there's a text in A1 or it's empty, the formula reads D2 then.

      1. It did,
        thank you very much !

  23. i need to create a formula that, when i click "yes/no" in one cell, I get the answer in another cell. the formula is complex but not sure how to navigate this.
    HELP!

    1. Hello, Greg,

      if your first cell (let's name it A1) has only two options - Yes and No, your formula should look like this:
      =IF(A1="Yes",""Answer_for_Yes","Answer_for_No")

      If A1 may remain empty or contain other value for which you don't want anything to return, then:
      =IF(A1="Yes",""Answer_for_Yes",IF(A1="No","Answer_for_No",""))

      You can learn more about nested IF function on our blog page.

  24. Hi,

    Need help for this formula.

    Duration: Must be more 60 Seconds.
    Status: POOR, GOOD, EXCELLENT.

    Condition:
    IF duration 60 seconds, but the Status = "POOR", then need to check.

    My current formula:
    =IF(A2<60,"POOR",IF(B2="GOOD","OK",IF(B2="EXCELLENT","OK"

    I'm stuck with that.

    Thanks.

    1. Hi,

      if I understand your task correctly, this formula should do:
      =IF(AND(A2<60,B2="POOR"),"NOT OK",IF(AND(A2<60,B2="GOOD"),"OK",IF(AND(A2<60,B2="EXCELLENT"),"OK","")))

      If it's not what you're looking for, please describe in details what values you already have and in which cells, and specify your condition for when there's poor/good/excellent in B2.

  25. Hi team,
    Why if functions excepts text greater than any number value? if I write values in A1 -100, A2-200, A3-300 and write function in B1 =if(A1>=250,"Fine",if(A1>=100,"Not so bad","")) when instead of 100 i write in cell A1 text NO VALUE - if function returns me "Fine"

    Thanks

    1. up,, any info about why text values are greater than number values in logical functions?

  26. I am trying to write that if there is a 1 in Column B2 then write "refer to page 34". I have been using =IF(B2=1, "refer to page 34"). This works great when there is a 1 in the reference column but when the column is blank I keep getting a False statement. Help please

    1. Hello, Dave,

      The point is that IF function uses 3 arguments. You specified what to return when the value is 1, but you didn't for when the value is not 1. If you need the cell to remain empty in that case, please try this formula:
      =IF(B2=1,"refer to page 34","")

      Hope this helps!

  27. The following vlookup function

    =(VLOOKUP($E12,'SPORTS'!$A$9:$G$162,6,FALSE))

    returns the value as "#N/A" because the data is not available in the "SPORTS" tab. How can I change the value to "0" from "#N/A"?

    Thanks

    1. =IFERROR(VLOOKUP($E12,'SPORTS'!$A$9:$G$162,6,FALSE), "0")

  28. Hello Team,

    "If value of Cell d4 between -5 to +5 then display "Correct" Else "Error".

    Kindly help me with a formula for the above condition.

    Thanks in advance.

    1. If you have Excel 2013 or higher, use the conditional formatting tool.

  29. I want to achieve three outcomes from three arguments. I am attempting to compile a register containing current insurance policies. The policy dates are either current (OK), out of date or not provided at all.
    1. TODAY()
    2. Date registered (A4)
    2. OK (A5)
    3. Out of date (A6)
    4. Not submitted (A7)

    My attempt: =IF(TODAY()A4,"A6",IF(ISBLANK(A4),"A7")))

    This doesn't work and I suspect I don't understand functions well enough to achieve my desired outcome. Can you assist.

    1. Oops typo. Should be =IF(TODAY()>A4,"A6",IF(TODAY()<A4,"A5",IF(ISBLANK(A4),"A7")))

  30. Please help on this

    =if(A3=E1&B1=C1, D3*G3,0)

    SIMILAR ABOVE I WANT TO CREAT.

    1. =if(A3=E1&B1,C1, D3*G3)

  31. Hello Team,

    Can any one help with, Actually i want to find the things from 3 column,
    Eg i have 50 Servers list those i need to find from 1000 D column list which i can find by using =vlookup function but every servers having its status with retired, Decom, Active in H column, So i need to find if Server in D column status in H column with Retaied or Dcom then find as true.

    1. Hello Rajesh thanks for looking into it, i have drop an email with my concern. Hope that clear to you.

  32. How to write the IF function in case of a dubble letter column.
    For Example
    =IF(AA18="";"Close";"Open")
    I get a ?NAME error.

    1. =IF(AA18="","Close","Open")

  33. Hi guys, Please assist with IF formula. 1. I wanna compare two columns and isolate all the numbers that are not duplicates into a separate column.
    2. I also wanna compare the isolated numbers to another column that has data.
    Example

    Column A *Column B Now the odd one out must go to Column C
    1234 1338 1338
    1245 1245
    1336 1336

    There after I would like to again see if column C data is not in another column, say Sheet 2 Column F

    your assistance will be highly appreciated

    1. Hi, Andy,

      for starters, if the data begins from A1, you can enter the following formula into C1:
      =IF(A1<>B1,B1,"")

      and copy it down the column to isolate all non-duplicates.
      Then you can use one of these ways (to your liking, depending on what result you want to see) to compare column C with another one.

  34. How to create an if then statement that follows as such:

    If A5 = "Orange" AND B5 = "Banana" then populate in C5 "Fruitbasket" with yellow background

  35. I am trying to write a formula in E5 where if D5 is > $5,000.00 then subtract $5,000.00 from D5, if not then blank.

    Thanks,
    Larry

    1. Hi Larry,

      Here you go:
      =IF(D5>5000, D5-5000, "")

  36. Hi.

    I have been googeling this for some time now, and cant find out how to:

    check if the cell value is less than another cell value, and if it is, add a cell value to the first cell.

    A1 = 120, A2 = 200, A3 = 300. I need some function to check if A1 is less than A2, which it is, and then add A3 to A1, so A1 becomes 120 + 300 = 420, and is no longer less than A2.

    if anyone have an answer for this, i'll be very thankful.

    thank you in advance.

    - Morten

    1. Hi, Morten,

      I'm afraid you won't be able to put the formula in A1 and reference A1 at the same time. You will have to enter the formula to some other cell, say A4, to return the result:
      =IF(A1<A2,A1+A3,"")
      Also, note that if A1 is not less than A2 the cell with the formula will remain empty.

  37. I am trying to figure out how to use an if function when a cell contains a date in a format such as 1/1/2004. What I am trying to do is strip the month and day information and have an if statement that will return a result of "2004". For example if 5/5/1998 it should return a result of "1998". Can anybody help?

    1. Hi,Soccer Guy

      use this formula if your data in A1 "=YEAR(A1)"

  38. Hi,

    I have a list of activities if I select any one of those activities in one cell then the other cell should reflect as Production. Could you please help me with the formula

  39. Hi I need help figuring out formula to do.
    =if cell is 1-3=0 if cell is 4-7=5 if cell is 8-11=10 if cell is 12-15=15. using signs.

    ex.=IF(D2>=4=5,IF(D2>=8=10,IF(D2>=12,15)))
    where d2 is number of disks used
    and working from G2 where it is discount given in currency

    0-3 disks= 0$
    4-7 disks= 5$
    8-11 disks= 10$
    12+ disks= 15$

    1. Hi, Michael,

      If I understand your task correctly, you can try this formula in G2 (make sure to set the Currency format to the cell):
      =IF(AND(D2>=0,D2<=3),0,(IF(AND(D2>=4,D2<=7),5,(IF(AND(D2>=8,D2<=11),10,(IF(D2>=12,15,"")))))))

      Hope it helps!

  40. I have an excel sheet with three columns.
    I want to create a nested if formula that shows the following
    =If(RR>8,"P1",If(RR<=3,"P3","P2"))
    How do I re-write this formula in a situation I do not want a value returned when any RR cell is empty?

    Thanks

    1. Hello, Peejay,

      If you reference RR1 cell, then
      =IF(RR1="","",IF(RR1>8,"P1",IF(RR1<=3,"P3","P2")))
      then copy the formula down the column to return the values for other RR cells.

      Also, note that the formula above will return the text values P1, P3, and P3.
      If you want those to be cell references, then use the following formula:
      =IF(RR1="","",IF(RR1>8,P1,IF(RR1<=3,P3,P2)))

      Hope this helps.

  41. Hi,
    I need an excel formula for the following:
    IF A1 & B1 HAVE NUMBERS THEN ADD THOSE NUMBERS, IF B1 IS BLANK THEN RETURN A 0.
    I am working on a compensation spreadsheet containing market adjustments for some of the staff. I calculated the market adjustment and need to calculate the new base rate. If the base rate did not get an adjustment then I need the cell to show 0. If the base rate did get an adjustment I need it to add the current base rate with the calculated adjustment for the new base rate.
    Thank you in Advance

  42. Hi
    I am trying to do a simple less than < formula but can't seem to get it to work. I have two columns with dates in them say A & B. I want to be able to say:

    if A1 is than A1 then B1. If A1 is blank I want it to input what is in B1.

    Any help would be great!
    Thanks

    1. It would be wrong to check if A1 is less than A1. But, in case you misspelled and wanted to see if, for example, A1<A2, and return B1 in both cases, try the following:
      =IF(A1<A2, B1, IF(ISBLANK, A1), B1, "")
      If it's not helping, please, consider correcting the condition so we could assist you.

  43. Hi,
    I want to add cells up, and if they are greater than 0, add those cells up, multiply that time 200,000, then divide by other cells that contain numbers. For some reason, it will not give me the correct answer. Should I be using the IF function before the sum, or the sum function before the if?

    1. Hey, Rob,
      try IF function with SUM function inside.
      If the cells to sum up are A1 and B1, the formula will be
      =IF(SUM(A1,B1)>0, (SUM(A1, B1)*200000)/C1, "")

      If it doesn't answer your request or you meant something slightly different, please, specify your question.

  44. I need this:
    =IF(H18*I18=1,"B18","")
    but B18 doesn't contain a number, but text.
    I need the IF function to give me that text in cell B18 but it gives an error.
    Is there a solution?

  45. I am trying to construct a nested IF AND function that involves summing a range of cells. I tried using the following: =IF(SUM(I6:I15)>15,2,IF(AND(ISBLANK(H24)),0,1.5)) Where am I going wrong or what variation do I need to use? Thanks!

    1. Hi, Adam,
      AND function requires at least 2 parameters, but there’s only one in your formula. If you have only one condition – whether H24 is blank or not – you can omit AND. For example:
      =IF(SUM(I6:I15)>15,2,IF(ISBLANK(H24),0,1.5))

  46. i have 3 conditions
    1. if A1-cell value is a +ve value then result in B1 as "Excess"
    2. if A1-cell value is a -ve value then result in B1 as "Short"
    3. if A1-cell value is a 0 value then result in B1 as "nul"

    1. ve? =IF(A1>0,"Excess",IF(A1<0,"Short",IF(A1=0,"null")))

  47. IN A COLUMN I HAVE "1,2,3" AND I WANT TO COVERT THE NUMBERS TO "WORKERS,CLEANERS,DRIVER". BECAUSE THAT IS WHAT THEY STAND FOR AND ALL THE OTHER COLUMNS HAVE TWO OR THREE NUMBERS THAT REPRESENT THE WORDS GIVEN ABOVE. THANK YOU.

    1. Hi, Grace,
      if you want to change the data in one and the same cell and you don’t want to do it manually, you need to use a slightly different Excel function – Replace. It's very easy to use, and here is an article that will help you with the task:
      How to replace one value with another
      But if you have a huge table with tons of values to be replaced, you can try VLOOKUP function.

  48. Hello since I see new posts I will ask. I have tables which I am entering numbers into that total different each time I enter new numbers and total the sheet.

    These numbers for example let's say 11+11+11+11 = 44 are then totalled into a new cell and added with +3. Okay so far I have a number in the example of 47.

    I want to use your formula with the number 47 (remember this will change) that I just generated to recall a value with it.

    Example) On page 2 I have values for each number.
    11 100
    20 300
    35 600
    47 750
    etc

    I want to get the formula to upon result of my number (say 47) to then pull the value associated with that. I've tweaked it to say on a specific value of 47 to pull the 750 into the cell, but that is by manually entering the one value. I need it to do a range of values in one forumla to pull from the list.

    Is this possible? or am I in the wrong formula.

    Thank you

    1. Hello, Dean,

      you were looking for a formula in the wrong article, because you need not IF, but VLOOKUP function.
      For example:
      on 'Sheet7' in A1:A4 we have the numbers. In B1 we put:
      =SUM(A1:A4,3)
      to sum them up and add 3.
      Then in C1 we use the next VLOOKUP function:
      =VLOOKUP(Sheet8!A4,Sheet8!A1:B4,2)
      to return the value of the corresponding number from 'sheet8'.
      'Sheet8' contains a little table with the possible results in A1:A4 and their corresponding values in B1:B4.

      Hope you won't have same troubles in the future!

  49. I'm trying to figure out how to write a formula to compare mileage from two columns. I'm tracking service intervals on fleet vehicles. when a current mileage is within 1,000 miles i want it to turn yellow. if its within 100 miles i want it to turn red. can anyone help me please. thank you in advance

  50. Am I able to have text in one cell determine if a number in another cell is negative or positive?

    If cell A1 says "withdrawal" I would like the number in cell B1 to be negative. If cell A1 says "deposit" I would like the number in cell B1 to be positive.

    1. Assuming, that column B already contains positive numbers, the next formula will do:
      =IF(A1="withdrawal",-B1,B1)

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)