Comments on: Excel IF statement with multiple conditions

For powerful data analysis, you may often need to build an Excel IF statement with multiple conditions or use IF together with other functions. This tutorial will show you the most effective ways to do this. Continue reading →

Comments page 44. Total comments: 4557

  1. I want if b2=1,20% (1+1)*10
    if b2=2,50% (2+1+2)*10
    if b2=3,90% (3+1+3+2)*10 continue to n number how to possible?

    1. Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

  2. N4=IF(G4=1,"H4+I4-L4",IF(G4=2,"H4+I4",IF(G4=3,"H4")))

    Incase of G4=1 Output come, H4+I4-L4 instead of numerical value
    Incase of G4=2 Output come, H4+I4 instead of numerical value
    ...........
    Is there any problem with my if formula? why i dont get numerical value on N Column?

  3. Good Morning! im trying to write a formula that looks at a frequency eg. 6 months, 1 year, 2 year, 5 years and if its X add that number to date inserted in a seperate column. so A would be 1 year, B would be "test date" and C "Due Date" currently have the below, but isn't working.
    =IF(OR(E17="1 Year",EDATE(L17,12)),IF(E17="2 Years",EDATE(L17,24)),IF(E17="5 Years",EDATE(L17,60)))
    Thanks in advance.

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(E17="1 Year",EDATE(L17,12),IF(E17="2 Years", EDATE(L17,24),IF(E17="5 Years",EDATE(L17,60))))

      I hope this will help

  4. Hello, greetings for the day, my problem is i want to write a formula a1 has 350000 and a2 has 13000 and in the cell a3 i want to write like this if a1 < 500000 and also a2 greater than 12500 then a3 should be a2-12500. how can i do this? and if a1 400000 and a2 11000 and a3 should be a2 cause it is less than 12500. to the point if a2 is greater 12500 then it(a3) should be after deducting 12500 and if it(a2) is less than 12500 it can be that amount but primary condition is a1 must not have more than 500000. answer to this question is highly appreciated. Thank you in advance.

  5. I need to apply formula for Cell A1, to find its 70% or 10000 whichever is less

  6. Hi Alex! Hope all is well and that you could assist me with my Index formula as it's currently not working. I'm trying to use the index formula where it matches the keyword & returns the category. Unfortunately it's not working and hoping that you could assist.

    Data
    Post Date Check Description Debit Credit
    07/24/2020 SHINE MEDICAL 21022.00
    07/24/2020 TA INSTRUMENTS-W DIR-DEPT 7200.00
    07/24/2020 ORBITAL SCIENCES EFT 25594.00
    07/24/2020 THE BOEING CO BCAGPAYSVC 16480.00
    Formula
    Balance
    {=INDEX(K2:K15,MATCH(TRUE,ISNUMBER(SEARCH(J2:J15,C2)),0))}

    Keyword
    SHINE MEDICAL
    TA INSTRUMENTS-W DIR-DEPT
    ORBITAL SCIENCES EFT
    THE BOEING CO BCAGPAYSVC
    UNEX DISB
    CHECK NUMBER

    Categories
    WIRE
    WIRE
    WIRE
    WIRE
    WIRE
    CHECKS PAID

  7. hello, i would need a bit of a help with formula.
    in sheet1 in column A i have names, they are starting from row 3. in row 2 starting from column B i have numbers from 1 to 31.
    numbers represent dates. so i am filling that table with "1" when someone is there and i leave cell empty when that person was not working that day.

    now hard part.
    sheet 2
    there i need some formula here i will be able to pick number/date and that formula to show me who was working on that day. then to print them, and to pick another date/number.

    thanks

    2. for first 2 dates i have done something like this
      =IF(D2=1,IF(Sheet1!D4=1,Sheet1!C4,""),IF(D2=2,IF(Sheet1!E4=1,Sheet1!C4,""),""))
      do i have to do same for all 31 dates or is there something easier?

  8. Hello! I cannot figure out the formula to "FLAG" a row for if each of the following conditions is not met:
    - If e2= "TRUE", then c2="TRUE" and d2="TRUE"
    - If d2= "TRUE", then c2="TRUE"
    - If c2= "FALSE", then d2="FALSE" and "e2="FALSE"
    - If c2= "TRUE"and either f2 OR g2="TRUE", then d2="TRUE"

    Please let me know if you can help! Thank you

    1. Hello!
      Please describe your problem in more detail. It’ll help me understand it better and find a solution for you. Thank you.

  9. Hi, I have a table of data & want a formula to find all the orders which have both a basin and a PEP :
    IN052816 FRT FRT
    IN052816 BASIN VBAML55MSMNO
    IN052816 PKG PEP
    IN052817 FRT FRT
    IN052817 BASIN VB-BAR-48-NO
    IN052817 PKG PEP
    IN052817 ACC NONSTOCK
    I have tried pulling all the basins to a separate tab & using the following formula: =IF((AND(K:K=PEP!K:K,PEP!M:M="PEP")), "Pass", "Fail") (K is IN (1st) column, M is PEP (3rd) column) but all it comes up with is FAIL
    Please help!

    1. Hello Sally!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail?
      Is your data recorded in one cell or in several?
      There is not a single value in the sample data to find BOTH basin AND PEP. Each of the lines contains OR the first, OR the second.

      1. Hi Alexander, thank you for getting back to me.
        The original data is in one tab A-AF 1-29808, with each item on its own line & column (sorry the post above doesn't look like it).
        I appreciate the Basin & the PEP are on separate lines, I thought I could use the invoice number to determine the number of times they appear together as one invoice number would appear multiple times relating to each item on the invoice (K). I couldn't think of a way from this to find how many times a PEP (M) was on the same invoice as a Basin, so I copied all the Basins (L) onto one tab, and the PEP's onto another tab and tried the formula above on the basin tab, looking to the PEP tab to find a match.
        Column K = Invoice Number
        Column L = Category - Basin
        Column M = Item - PEP
        Does that help you understand what I am trying to do? and what data I am working with?
        Thanks
        Sally

        1. Hello Sally!
          If I understand your task correctly, the following formula should work for you:

          =IFERROR(INDEX(K1:K20, SMALL(IF("BASINPEP"=L1:L20&$M$1:$M$20,ROW(K1:K20)-0), ROW(INDIRECT("1:"&ROWS(K1:K20))))),"")

          This is an array formula and it needs to be entered via Ctrl + Shift + Enter, not just Enter.
          If your table has header rows, replace 0 in the formula with the number of those rows.

  10. I am trying to create a formula for my spreadsheet for multiple ITEM in a single cell
    If there is a "29" in a cell, then formula will D1+E1,
    If there is a "31" in a cell, then formula will D1
    If there is a "32" in a cell, then formula will D1+E1-F1
    ...........

    Here 29,31,32 ... etc area type fabricated rebar size in civil construction.
    so i use following formla
    =IF(H1=29,"D1+E1",IF(H1=31,"D1",IF(H1=32,"D1+E1-F1")))
    For 29 answer shows "D1+E1" instead of (D1=5+E1=7)=12

    Please help!

    1. Hi,
      The reason it is showing D1+E1 is because you put it quotation marks.
      just put D1+E1.

    2. Hello!
      You named the condition "If there is a“ 29 ”in a cell, then formula will D1 + E1". This condition is written in your formula.
      Why do you expect "(D1 = 5 + E1 = 7) = 12" to be executed (I don't really understand what this expression means)?

      1. Hello, Can you help me solve this?

        I need a formula in cell C5 that does the following:
        IF A1+B1 4 but 9 but 15, return $75
        Thanks!

        1. Hello!
          Sorry, it's not quite clear what you are trying to achieve. Could you please describe it in more detail? What result do you want to get? Give an example of the source data and the expected result.

  11. Hi! I need to convert the following from Google Sheets to work in Excel
    =ifs(N9=1,I9/10,N9=2,I9/100,N9=3,I9/1000,N9>3,I9/10000)

  12. Hi,
    Hope you are doing well.
    I wanted to know what is the formula used to derive how many sequences a person has carried out each day. Below is my example..Everyday the person will enter his/her start no. and end of the day the closing no. and i needs to check how many sequences each person has done.

    Thread Tester Seq Started Seq Completed Total Seq.Executed
    MOT-FST-83 MEETA 1 5 5
    MOT-ITM-146 AK-2 0 0 0
    MOT-ITM-147 LD-4 5 5 1
    MOT-ITM-148 LD-3 1 1 1
    MOT-MSA-17 LD-1 33 36 4
    MOT-MSA-18 AK-1 35 36 2
    MOT-MSA-20 AS-2 4 6 3
    MOT-MSA-21 MB-3 6 6 1

    1. Hello Linda!
      To calculate the amount for each person, use the SUMIF function. Use the person's name as a criterion for counting. Read the detailed instructions in this article.
      I hope this will help, otherwise please do not hesitate to contact me anytime.

  13. Hi,
    My Name is Pradeep ,I need help for example state and brand wise subscriber count data in a sheet .so my requirement is .
    1st Condition ---
    If I select only state one name then subscriber total count show only selected state sum of count .
    2nd Condition ---
    If state and town then subscriber total count show only selected state and town sum of count.
    3rd Condition ---
    If state and town any name both are no select then subscriber total sum of count show.
    Date for your reference ---
    State Brand Total
    Ayodhya IDEA 188
    Ayodhya Voda 4189
    Gorakhpur IDEA 541
    Gorakhpur Voda 3994
    Kanpur IDEA 2072
    Kanpur Voda 8248
    Lucknow IDEA 1739
    Lucknow Voda 21211
    Prayagraj IDEA 723
    Prayagraj Voda 5028
    Varanasi IDEA 898
    Varanasi Voda 5496
    I am waiting your reply ..

    Thanks
    Pradeep

  14. Hello,

    I'm trying to determing which shipping containers were unloaded within the expected allocated time based on the number of pieces in the container. I have the total time it took to unload the containers in question and the amount of pcs on the containers. What formula can I use to determine if the container was unloaded within the threshold set based of the container pieces?

    Hoping someone can help me!

    < or = 399 pieces = 60 - 120 min per container
    400 - 899 pieces = 120 - 180 min per container
    900 - 1100 pieces = 180 - 240 min per container

    1. Hello Alex!
      Please try the following formula:

      =IF(A1<=399,IF(B1*60*24<=120,"Yes","No"), IF(A1<=899,IF(B1*60*24<=180,"Yes","No"), IF(A1<=1100,IF(B1*60*24<=240,"Yes","No"),"")))

      Cell B1 contains the execution time of the operation in the time format "hh:mm:ss"
      Hope this is what you need.

  15. Hello
    I'm looking for a way to create a formula or macro that will check the value or entry in a column then have it enter the results based on the formula/macro in 2 different columns.
    Example
    Column A Column B Column C
    12345 212456 230456
    12366 217456 (null or blank)

    For the above the criteria it would be IF column A = 12345 then column B = 21256 and column C = 230456
    The data in column A is large with over 300 possible numbers.

    1. Hello James!
      The information presented to you is not enough to give you advice.
      You didn't say anything about the verification criteria. But an Excel formula can only enter a value in one cell.

  16. Hello Alexander!
    I wonder if you can help me?
    I have a nested IF formula which works fine;
    =IFERROR(IF(Homepage!$L$4="Asda",'CPA - Hotspots Asda'!B12,IF(Homepage!$L$4="Tesco",'CPA - Hotspots Tesco'!B12,IF(Homepage!$L$4="Boots",'CPA - Hotspots Boots'!B12,IF(Homepage!$L$4="Field",'CPA - Hotspots Field'!B12,IF(Homepage!$L$4="Ireland",'CPA - Hotspots Ireland'!B12,IF(Homepage!$L$4="Morrisons",'CPA - Hotspots Morrisons'!B12,IF(Homepage!$L$4="Sainsburys",'CPA - Hotspots Sainsburys'!B12,IF(Homepage!$L$4="Sainsburys Trial",'CPA - Hotspots Sainsburys Trial'!B12,IF(Homepage!$L$4="Savers",'CPA - Hotspots Savers'!B12,IF(Homepage!$L$4="Superdrug",'CPA - Hotspots Superdrug'!B12,IF(Homepage!$L$4="Wilko",'CPA - Hotspots Wilko'!B12))))))))))),"")

    I now however need to add another element to this whereby even if L4 on the homepage states "Asda", if B28 states "No", I need it to direct to a different sheet, but if it states "Yes" it continues to pull from B12 on the 'CPA - Hotspots - Asda' sheet, and then I also need this to continue for each individual If statement. Is this possible at all? Thanks

    1. Hello Leena!
      It is very difficult to understand a formula that contains unique references to your workbook worksheets. For the same reason, I cannot check her work.
      If I understand your task correctly, you can change the formula

      =IF(AND(Homepage!$L$4=”Asda”, B28="Yes"), [your formula], IF(AND(Homepage!$L$4=”Asda”, B28="No"),[direct to a different sheet],""))

      I hope it’ll be helpful.

  17. Hello,
    I have a medical insurance cost comparison. Using data from multiple insurance quotations, with a number of factors to consider (Age, Gender, Salary and insurance Category), what formula would I have to use to return the cost in a specific cell (costing data found on a secondary worksheet, within the same workbook)? For example, Employee A is 30 years old, Single Male earning $4,070 per month and is entitled to Category B Insurance the cost from Insurer 1 = $6,696 per annum

  18. can anyone please help me with this.
    assume i have different data in A1 like 12ABCD999 OR 45JKLM888 OR 78MNOP111 and many more. now i want if value of first 2 number of A1 starts with 12 then my output should be "OK" and if value of first 2 number of A1 starts with 45 then my output should be "GREAT" and if value of first 2 number of A1 starts with 78 then output should be "FANTASTIC".
    Thank you.

    1. Hello!
      The formula below will do the trick for you:

      =IF(--LEFT(A1,2)=12,"OK",IF(--LEFT(A1,2)=45,"Great",IF(--LEFT(A1,2)=78,"FANTASTIC","")))

      I hope this will help

  19. Hi,
    Can anyone help me with this formula? I've been trying to create a Nested IF OR but just can't get it to work. I have a table with "Team" as a column heading in column A. I've added column D to the table for the office group. If column A is ftl, gai, or orl then column D should be fl. If column A is glf or nol then column D should be gcn. If column A is atl then column D should be geo. This is my formula but the result is always #VALUE!. Please help!
    =IF(OR([@Team]="ftl",[@Team]="gai",[@Team]="orl"),"fl"),IF(OR([@Team]="glf",[@Team]="nol","gcn"),IF(OR([@Team]="atl","geo"))

    1. Hello Sue!
      If I understand your task correctly, the following formula should work for you:

      =IF(OR(A1="ftl",A1="gai",A1="orl"),"fl",IF(OR(A1="glf",A1="nol"),"gcn",IF(A1="atl","geo","")))

      or IF function can be replaced with INDEX + MATCH functions

      =IFERROR(INDEX({"fl","fl","fl","gcn","gcn","geo"},MATCH(A1,{"ftl","gai","orl","glf","nol","atl"},0)),"")

      I hope it’ll be helpful.

      1. You're a Genius! I used the Index Match and it works perfectly! I never would have figured it out on my own. Thank you so much!!

  20. HI there I have inherited a daily cash reconciliation file which was updated manually on a daily basis. I'm trying to automate or create a formula and was wondering whats the best way to approcah this. So I have a list of transactions which I need to separate by Deposits, Wires Rcvd, Incoming Transfers, (Checks/wires)Paid, & Outgoing Transfers. Thank you!

      1. Post Date Check Description Debit Credit Status Balance Keyword Categories
        07/24/2020 SHINE MEDICAL CORP PAY VALCOR ENGINEERING 21022.00 Posted =INDEX(K2:K15,MATCH(TRUE,ISNUMBER(SEARCH(J2:J15,C2)),0)) SHINE MEDICAL CORP PAY VALCOR ENGINEERING WIRE
        07/24/2020 TA INSTRUMENTS-W DIR-DEPT VALCOR SCIENTIFIC 7200.00 Posted =INDEX(K2:K15,MATCH(TRUE,ISNUMBER(SEARCH(J2:J15,C2)),0)) TA INSTRUMENTS-W DIR-DEPT WIRE
        07/24/2020 ORBITAL SCIENCES EFT ID: 400742 0005VALCOR ENGINEERI 705ISA*00* *00* 25594.00 Posted =INDEX(K2:K15,MATCH(TRUE,ISNUMBER(SEARCH(J2:J15,C2)),0)) ORBITAL SCIENCES EFT WIRE
        07/24/2020 THE BOEING CO BCAGPAYSVC ID: 0006433532 0011VALCOR ENGINEERI 705ISA*00* *00* 16480.00 Posted =INDEX(K2:K15,MATCH(TRUE,ISNUMBER(SEARCH(J2:J15,C2)),0)) THE BOEING CO BCAGPAYSVC WIRE
        07/24/2020 UNEX DISB PAYABLES ID: VALCOR VALCOR ENGINEERING COR 705 10878.00 Posted =INDEX(K2:K15,MATCH(TRUE,ISNUMBER(SEARCH(J2:J15,C2)),0)) UNEX DISB WIRE
        07/23/2020 108572 CHECK NUMBER 108572 1245472250 972.10 Posted =INDEX(K2:K15,MATCH(TRUE,ISNUMBER(SEARCH(J2:J15,C2)),0)) CHECK NUMBER CHECKS PAID
        07/23/2020 108341 CHECK NUMBER 108341 1245598400 10553.34 Posted =INDEX(K2:K15,MATCH(TRUE,ISNUMBER(SEARCH(J2:J15,C2)),0)) TRANSFER TO CK# 41337204 OUTGOING TRANSFER
        07/23/2020 TRANSFER TO CK# 41337204 826257.04 Posted =INDEX(K2:K15,MATCH(TRUE,ISNUMBER(SEARCH(J2:J15,C2)),0)) PRINCIPAL LIFE WIRE
        07/23/2020 PRINCIPAL LIFE P PLIC-PERIS ID: 7-1454400004529 NO NAME ON FILE 76421.81 Posted =INDEX(K2:K15,MATCH(TRUE,ISNUMBER(SEARCH(J2:J15,C2)),0)) Medivators Inc. EDI PYMNTS WIRE
        07/23/2020 PRINCIPAL LIFE P PLIC-PERIS ID: 7-1454400004530 NO NAME ON FILE 9064.99 Posted =INDEX(K2:K15,MATCH(TRUE,ISNUMBER(SEARCH(J2:J15,C2)),0)) MERCHANT BANKCD DEPOSIT DEPOSITS
        07/23/2020 PRINCIPAL LIFE P PLIC-PERIS ID: 7-1454400004528 NO NAME ON FILE 1750.00 Posted =INDEX(K2:K15,MATCH(TRUE,ISNUMBER(SEARCH(J2:J15,C2)),0)) Accessory Tech WIRE
        07/23/2020 Medivators Inc. EDI PYMNTS ID: 01-102020000123 0012VALCOR ENGINEERI 705ISA*00* *00* 96770.50 Posted =INDEX(K2:K15,MATCH(TRUE,ISNUMBER(SEARCH(J2:J15,C2)),0)) PARKER EDI/ACH WIRE
        07/23/2020 MERCHANT BANKCD DEPOSIT ID: 447201760884 VALCOR ENGINEERING COR 6255.50 Posted =INDEX(K2:K15,MATCH(TRUE,ISNUMBER(SEARCH(J2:J15,C2)),0)) NORTHROP GRUMMAN EFT PMT WIRE

        Hi Alex, I'm trying to create a Index formula for where it returns the category of the expense but unfortunately it's not working properly could you please assist?
        Thanks,
        Anees

        1. Hello!
          I'm sorry, it is not very clear what result you want to get. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.

          We'll look into your task and try to help.

  21. This formula result: #VALUE!.
    =IF(OR(AND(A4="APPLE",B4="ORANGE"),"DRINK",IF(AND(A4="BREAD",B4="BUTTER"),"EAT")))
    Could you please fix it? The expected result is: DRINK, BUTTER, OR FALSE (either one).
    Thank you in advance.

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(AND(A4="APPLE",B4="ORANGE"),"DRINK", IF(AND(A4="BREAD",B4="BUTTER"),"EAT"))

      I hope it’ll be helpful.

      1. Thanks a lot, Alexander.
        It works perfectly!
        Have a nice day.
        Cheers,

  22. Hi,
    Can anyone please help me with my formula.
    I need "Yes" or "No" answer if the number in cell matches a number from any cell in another selected field. These two i have tried so far, but no luck.
    And sorry, my excel is in German.
    =WENN(A13=Tabelle1!A2:A29;"ja";"nein")
    =WENN(B16="SVERWEIS('INT-B-106 Kennzahlen nach Artik'!A13;Tabelle1!A:A;1;Tabelle1!A:A)";"ja";"nein")

    Thanks for help and have a great day!

    1. Hello!
      I propose to use the following formula (in English):

      =IF(SUM(--(IF(A13=Tabelle1!A2:A29,1,0)))>0,"yes","no")

      I hope this will help

  23. I need help in finalizing this formula. =IF(AND(L10>=1,L10<=10),250,SUM(L10-10)*11+250), IF(L10<1,0,""). The formula works great up to the last IF statement. I am needing it to return 0 if L10 is less than one or in other words equal to zero.

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IF(AND(L10>=1,L10<=10),250,IF(L10<1,0,SUM(L10-10)*11+250))

      I hope this will help

  24. Good Morning,
    Please assist, I am struggling with the formula below.
    =IF(Z5>Y5,Y5*30%,IF(Z50,Z5*0%)))
    X Y Z AA
    Tax @25% Tax @25%
    200.00 2,100.00 3,800.00 630.00
    200.00 2,100.00 800.00 240.00
    200.00 2,100.00 3,800.00 630.00
    0.00 2,100.00 -5,400.00 -1,620.00
    200.00 2,100.00 3,800.00 630.00
    0.00 2,100.00 -5,400.00 -1,620.00
    I want Cell AA to be zero if the value in cell Z is less than 1

    1. Hello!
      The numbers in your table do not match the conditions in your formula. Therefore, your calculations are not very clear to me. However, it may be useful to you.

      =IF(Z5>Y5,Y5*30%,IF(Z5<1,Z5*0%))
      =IF(AND(Z5>0,Z5>Y5),Y5*30%,0)

  25. Hi,
    Following is an example of a data sheet with the 4 columns(A, B, C, D).The values in first three columns correspond(match) to each other but in Column D values are randomly located in different rows even though the column is sorted. I need to find out the correct value that corresponds to the value of Start and stop in each row. For example, there is a value in "Position" column that falls between the range of start(3904) and stop(4943) in first row but do not know how to find it?
    Genesymbol Start Stop Position
    Mt-nd2 3904 4943 471
    Mt-atp8 7758 7962 635
    Ppil4 1897338 1929764 645
    Mib1 1970914 2094921 665
    Arrdc1 2129879 2136937 685
    Hspa1a 2699712 2701816 2924
    Glmn 3204390 3247696 2959
    Ncoa2 5616483 5694599 3130
    Slc7a1 8032809 8108973 3140
    Vcpip1 9230984 9257935 3145
    Uimc1 10065947 10130908 3166
    Wasf3 10298555 10391271 3196
    Secisbp2 13555349 13586555 3211
    Fam98a 21051327 21065987 3418
    Samd4a 23665202 23878540 3560
    Gfod1 23823632 23923793 3575
    Npy1r 24779477 24788741 3581
    Hars2 29629184 29638722 3808
    Pcdhb5 30398113 30400696 3860
    Ddx50 32239748 32269146 4055
    Zfp827 32401661 32528966 4252
    Epc2 33641616 33740462 4375
    Tmem132b 35970821 36398247 4600
    AABR07001068.1 37059412 37162590 4708
    Rmnd5b 37073345 37084638 4920
    Fbxo8 37131345 37177034 6331
    Dpysl2 43477629 43542940 7474
    AABR07072400.1 43694183 43694612 7568
    Ugdh 44479614 44502846 7606
    Lias 44507218 44524253 7663
    Tenm3 46731403 46926246 7699
    Senp7 46880928 47027668 7711
    Amph 48304322 48562906 7898
    Pnrc1 48501472 48504512 7925
    Scn2b 49418965 49427690 8025
    Ctc1 55596148 55616890 8040
    Ndst1 55955389 55992886 8115
    Phf23 56605140 56609234 8255
    Tlk1 57013025 57119002 8271
    LOC103690017 57318795 57389902 8308
    Pigo 58461759 58469400 8351
    Mcart1 60944563 60955944 8377
    RGD1305464 61913589 61919875 8394
    Cdh6 63101739 63166510 8441
    Dip2c 63635086 63808465 8483
    Luc7l2 66290389 66354712 8534
    Cyp20a1 67130259 67179693 9260
    Cxxc1 70192493 70197868 9298
    Rasal2 74771522 75059327 9310
    Zswim9 75335894 75354821 9544
    Ppp2r2c 78679214 78902064 9574
    Xrcc5 79659251 79748079 9593
    Irgq 81395841 81399673 9646
    Plcd4 81816872 81844365 9658
    Bcs1l 81868265 81872198 9684
    Limk2 83573928 83641893 9763
    Ick 85413537 85472695 9832
    Pip4k2b 85658324 85684139 9937
    Zmiz2 86652365 86661442 10077
    LOC100360491 86751157 86751793 10125
    AABR07019085.1 87404841 87450320 10298
    Stat5b 88686207 88754830 10334
    Itpkb 98615287 98708150 10790
    Neu4 101284902 101290559 10820
    Dusp10 104284660 104321456 10992
    Prpsap1 105430520 105452229 11834
    Armcx5 106253355 106255059 12021
    Nek9 109124330 109162268 12621
    Mrpl12 109658032 109662536 12762
    Zfp638 115601661 115690273 12786
    Ccdc51 117812099 117830869 12802
    Nup210 122644135 122741111 12858
    Xrcc6 123259761 123280613 12876
    Rtl6 125465117 125465849 12921
    Lurap1 134991997 135001721 12935
    Arid2 137680530 137795656 12948
    Zfp133 138597638 138616132 12975
    Dzank1 138624555 138683319 13006
    Noct 140286661 140306870 13020
    RGD1561327 144445795 144447448 13039
    Ret 150202058 150244373 13099
    AABR07054400.1 152402520 152465229 13336
    Epb41l1 153843206 153893848 13551
    Ing4 157554794 157563353 13580
    Vwf 158088505 158219524 13637
    Oser1 159784128 159802953 13699
    Snx21 161252723 161264819 13826
    Lrp6 168194927 168323752 13916
    Ints13 180855800 180887051 14003
    Tmtc1 182625386 182844292 14149
    Snapin 189878168 189880744 14193
    Wdr3 202447745 202470400 14202
    Rap1a 208193954 208215188 14235
    LOC100911730 214009784 214013766 14706
    LOC100911881 214039481 214074664 14775
    Tspan4 214454090 214473742 14910
    Chid1 214476387 214511530 14970
    LOC108348052 220307394 220314036 14980
    LOC102553088 230476320 230479595 15009
    Alpk1 231996088 232117135 15073
    Jak2 247398598 247458510 15088
    AABR07007121.1 282063929 282064742 15150
    Any help will be appreciated.Thanks!

    1. Hello!
      You want to find the values in the "Position" slider, which are between the Start and Stop values. Between 3904 and 4943 there are values 4055.4252, 4375, 4600.4708 and many others. Explain which one you want to find?

  26. Hello,
    I have the following statement that works.
    =IF(OR(G6 = "high", H6 = "high", I6 = "high", J6 = "high"), 0.3*F6, F6)
    I now want to have the statement to look for a "medium" value in each of those cells and if it is a "medium" it would multiply F6 by 0.6 otherwise it would return F6 unchanged.
    If "high" it would multiply F6 by 0.3
    If there is a "high" in the string then it would ignore any medium.
    Thanks!

    1. Hello John!
      If I understand your task correctly, the following formula should work for you:

      =IF(OR(G6 = "high", H6 = "high", I6 = "high", J6 = "high"), 0.3*F6, IF(OR(G6 = "medium", H6 = "medium", I6 = "medium", J6 = "medium"), 0.6*F6, F6))

      I hope this will help

      1. Perfect! thanks!

  27. Hi is someone is able to help
    I need help with an IF formula
    I need help with, if the value in cell A1 sheet1 is + or - 10 of another cell in another sheet say Sheet2 cell A1 if yes then equals Cell B1 in sheet2 .

    hope this make sense thank you in advances

    1. Hello Glenn!
      I hope you have studied the recommendations in the above tutorial. Explain what "value in cell A1 sheet1 is + or – 10 of another cell in another sheet" means?

      1. Hi Alexander,
        I have studied the above tutorial I do understand how to calculate if cell A1 is within + or - 10 of Cell B1, but it is when it comes to the result I don't want it to equal good or bad I want it to if it falls within that + or - 10 I want it to equal another cell C1 but if it doesn't fall within it is a fail.

        1. Hello Glenn!
          If a value is written in cell A1, then the formula can no longer be written to it. This has already been discussed on the forum many times. So your question about the formula in cell A1 does not make sense. Only VBA Macro Can Solve the Problem

          1. Hi Alexander,
            Apologies I should have explained my situation better and included my formula.
            the formula I am running is =IF(ABS(A1-B1)<=C1,"ok","Fail") but instead of if the result is ok it equals a number in cell E1

            1. Hello!
              If I understand your task correctly, the following formula should work for you:
              =IF(ABS(A1-B1)<=C1,E1,"Fail")

  28. Can I find the same for time ???
    I means if my difference in time is greater than 10 mins and my task done within 10mins
    If yes can you explain the formula

    1. Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

  29. I tried several IF formulas but I am not getting the desired output. Please can you help me provide the appropriate formula to get the output.
    There are the conditions: (if the number is between 1 and 20, the value should be 3)
    1-20 figs - 3 days
    21–100 figs – 5 days
    101–200 figs – 8 days
    201–400 figs – 12 days
    401–600 figs – 14 days
    Please help.

  30. Hello,
    I am trying to understand the correct formula for my project. My goal is to develop an IF statement for the following. If the number in column C is within a range it gets a score of 6-8.

    Score Legend
    1-50,000,000 Scores a 6
    50,000,001 – 100,000,000 Scores a 7
    100,000,001 – 500,000,000 Scores an 8

    Column C Score Column D Score
    400,000,000
    85,000,000
    48,000,000

    I keep getting errors with different formulas. Any help would be greatly appreciated.
    Thank you!!
    Tanner

    1. Hello!
      I hope you carefully read the article above. If I understand your task correctly, the following formula should work for you:

      =IF(AND(A1>1,A1<50000000),6,IF(AND(A1>50000001,A1<100000000),7, IF(AND(A1>100000001,A1<500000000),8,0 )))

  31. Hi there, I'd like some more insight into making this formula work for me. I'm doing a training matrix where I need to know if by today's date someone training has expired or about to expire..

    I have so far:
    =today()

    1. Hello Celina!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

    2. =today() and =today()+30 for expiry in 30 days (between)
      =today()+30 (greater than)
      Would this work?

  32. If income is
    Up to Rs. 7,500 Nil (for male)
    From Rs. 7,500 to Rs. 10,000 Rs. 175 (for male)
    Rs. 10,000 onwards Rs. 200 (for male)
    Up to Rs. 10,000 Nil (for female)

    1. If income is
      Up to Rs. 7,500 then tax will be Nil (for male)
      From Rs. 7,500 to Rs. 10,000, tax will be Rs. 175 (for male)
      Rs. 10,000 onwards, tax will be Rs. 200 (for male)
      Up to Rs. 10,000 tax will be Nil (for female)
      Above Rs.10,000 tax will be Rs.200 (for female)

      1. Hello,
        Below formula should work for you... if the values Sex and Salary are assumed to be in the cells D3 and E3 respectiely...
        =IF(AND(D3="Male",E37501,E310000),200,IF(AND(D3="Female",E310000),200,"")))))

  33. Hi,
    I hope you are well! I just need a quick tip with my formula:
    I need to create a forumla which says IF column F has the word TOR then collumn E must have 100. If it has 100 it displays "correct" if it is less/more than then it displays "incorrect".
    I.E: F=TOR E=100 COLUMN H: CORRECT
    I.E: F=TOR E=90 COLUMN H: INCORRECT

    This is just one part of the formula, how would I then add an additional/multiple rules which also displayed same result.
    For example, if I had the words TOR, TOR1, TOR2, TOR3
    TOR = 100
    TOR1 = 200
    TOR3 = 300
    How can I get column F which contains the word TOR/TOR1/TOR2 etc to match the correct value assigned to it in column E

    I.E: F=TOR E=100 COLUMN H: CORRECT / F=TOR1 E=200 COLUMN H: CORRECT
    I.E: F=TOR E=99 COLUMN H: INCORRECT / F=TOR1 E=199 COLUMN H: INCORRECT
    I hope this explains it well enough. Really appreciate the support!

    1. This is what I have so far but it is constantly displaying "Wrong" in the cell:

      Wrong
      =IF(AND(E9="80.00 UK",F9="TOR",OR(E9="100.00 UK",F9="TOR1",OR(E9="150.00 UK",F9="TOR2"))),"Correct","Wrong")

      1. Hello David!
        If I understand your task correctly, the following formula should work for you:

        =IF(OR(AND(F6="TOR",E6=100), AND(F6="TOR1",E6=200), AND(F6="TOR3",E6=300)),"Correct","Wrong")

        Hope this is what you need.

        1. You are an absolute genius! Exactly what I needed!!

          I just need to keep adding the AND section for additional criteria, thank you so much you have saved hours of mine and my colleagues time! Thank you so much again!

  34. Hello,
    Can you help me with a formula which return the value as "Qtr 1, Qtr 2, Qtr 3, Qtr 4' based on the month entered in a cell.
    eg. if Column B as value as Feb, Apr, Jul, Dec in cell B1, B2, B3, B4, in column C the result should be as Qtr 1, Qtr 2, Qtr 3, Qtr 4.

    Thanks

    1. Hello!
      If I got you right, the formula below will help you with your task:

      ="Qtr "&ROUNDUP(MONTH(DATEVALUE("1/"&B1&"/2020"))/3,0)

      I hope this will help

  35. This is what I see:

    W Z AG (Results) '=IF(W55000, W5-5000))
    Taxable Amt Non Taxable Amt Over $5000.00 Exempt Amt
    0 $377.50 0
    $2,848.45 $573.90 0
    0 $1,600.35 $(5,000.00)
    0 $12,478.32 $(5,000.00)
    0 $13,864.08 $(5,000.00)
    $9,314.72 $- $4,314.72
    $13,320.09 $- $8,320.09

    When I put your formula in Column it gives the same results:

    Column AG
    Over $5000.00 Exempt Amt
    $(5,000.00)
    $-
    $(5,000.00)
    $(5,000.00)
    $(5,000.00)
    $4,314.72
    $8,320.09

    so not sure why it is ignoring that if column W is zero then Column AG ought to be zero.

    Hope this helps with the explanation.
    Thank you for helping,
    Kathi

    1. Hello Kathi!
      The formula I sent to you was created based on the description you provided in your first request. However, as far as I can see from your second comment, your task is now different from the original one. Please note that if you’d provided me with the precise and detailed description of your task from the very beginning, I’d have created a different formula that would have worked for your case.

  36. Attempting to create a formula to show once case record. If it is a zero, then fail, if it a 1, then pass. Data below shows duplicate case records, need to show that if one of the cells to the left of case record is a zero, then the whole case record is a fail, and if they are all ones, then it is a pass. However, don't need the case record to show as a duplicate, only one case record. Thoughts?
    Right the First Time CaseRecordID
    0 28018598
    0 28018598
    1 28018598
    1 28018598
    1 27986560
    1 27986560
    1 27986560
    1 27986560
    1 28008880
    0 28008880
    1 28008880
    1 28008880
    1 28008920
    1 28008920
    1 28008920
    1 28008920
    1 28038434
    1 28038434
    1 28038116
    1 28038116
    1 28038116
    1 28038116
    1 28038434
    1 28038434

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =IF(SUMPRODUCT(--($C$1:$C$28=C17), --(IF($B$1:$B$28=0,-9999999999,1))) > 0,"pass","fail")

      After that you can copy this formula down along the column.

  37. Looking to show a result (pass/improve/Fail against a range of numbers in the same formula, how can I do this? Appreciate the help. please see below range;
    0 to 70 = fail
    71 to 89 = improve
    90 to 100 pass

    1. Hello!
      Please try the following formula:

      =IF(A1<70,"fail",IF(A1<90,"improve","pass"))

      I hope this will help

  38. I have a data set and I need to use a nested IF formula.
    Basically, if the word faculty(begins in cell I2) appears, my output should be Dr.
    and if student (begins in cell I2)appears with a sex of M (begins in cell H2), my output should be Mr.
    and if student (begins in cell I2)appears with a sex of F (Begins in cell H2), my output should be Ms.
    To complete the output, First Name and Last Names must also be included EX: Dr. First Last - Mr. First Last - Ms. First Last (First name starts in cell A2 and Last Name B2

    1. Hello Christina!
      If I got you right, the formula below will help you with your task:

      =IF(I2="faculty",CONCATENATE("Dr. ",A2," ",B2),IF(I2="student",IF(H2="M",CONCATENATE("Mr. ",A2," ",B2),IF(H2="F",CONCATENATE("Ms. ",A2," ",B2),""))))

      I hope my advice will help you solve your task.

      1. Yes, thank you.

  39. For distance calculation Compare two cell and third must automatic enter .
    If A & B - 2
    If A& C = 3
    If A&D = 4
    If B&C = 5 et., to compare 20 possibilities

    1. Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

  40. Hello, I am trying to figure out a formula by which if a cell contains yes it will add a specific amount to 4 totals in different cells.

    If yes add 1 million to running Total in 2 different cells, 750k to another and 500k to the last cell.

    What kind of formula would I be looking for?

    Thank you for any help!

  41. Hi there
    I need assistance with the following formula please:
    =IF(AND(K11=0),(OR(ISNUMBER(SEARCH("8000000",C11)),ISNUMBER(SEARCH("9000000",C11)),ISNUMBER(SEARCH("9100000",C11)),ISNUMBER(SEARCH("9500000",C11)))),"NO","YES")

    I need 2 criteria to be met.
    If colomn K's value is = 0
    AND the text in column C contains 8000000 or 9000000 or 9100000 or 9500000
    Result should be NO
    Otherwise YES
    Thanks for your help

    1. Hello Elouise!
      If I understand your task correctly, the following formula should work for you:

      =IF(AND(K11=0,OR(ISNUMBER(SEARCH("8000000",C11)), ISNUMBER(SEARCH("9000000",C11,1)),ISNUMBER(SEARCH("9100000",C11,1)), ISNUMBER(SEARCH("9500000",C11,1)))),"NO","YES")

      I hope this will help

  42. I am trying to use excel to add or subtract a value if it falls into a certain ranges otherwise just place the number in the correct box.
    the ranges are:
    145.1-145.5, 146.6-146.999999,147.6-148.0 subtract .6
    146.0-146.4, 147.0-147.4 add.6
    All other just move the entered number the cell.
    I can get it to do it to one set of ranges, but it fails when I try and use multiple ranges.
    Any help would be greatly appreciated.
    Thank you

    1. Hello!
      The formula below will do the trick for you:

      =IF(OR(AND(A1>145.1,A1<145.5),AND(A1>146.6,A1<146.9999),AND(A1>147.6,A1<148)),A1-0.6,IF(OR(AND(A1>146,A1<146.4),AND(A1>147,A1<147.4)),A1+0.6,A1))

      Hope this is what you need.

    2. This is the formula that I am currently trying to use, but numbers outside the ranges are subtracted.
      =IF(OR(B2>=145.09999,B2=146.599999,B2=147.5999999,B2<=148.00001),B2-0.6,B2)

      Example. If I enter 144.100 I still get the answer of 143.500

  43. How do i do an IF formula similar to below that actually works

    =IF(A5>1=(B5*120,IF(A5<1=B5*40)))

    Please help!

  44. I was confused in formula can you please help me out
    1840 P
    1841 A
    1842 P
    1843 P
    1844 P
    1845
    I need to mark "P"(present) and "A"(absent) all numbers but i have only list of numbers which are "P" (present)
    I need to mark both P and A
    will be very thankful if you reply :)

    1. Hello Jamin!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

  45. 1.) IF B1=C1 AND D1=TRUE => calculate A1*0.1
    2.) IF B1=C1 AND D1=FALSE => calculate A1*0.2
    3.) IF B1C1 => 0

  46. Anubody can help to solve this?:
    I have following conditions
    Cell A1 = value $1000
    Cell B1 = text A
    Cell C1 = text B
    Cell D1 = cell condition TRUE or FALSE (always on condition is set)

    Now I want to do following in cell E1

    1.) IF B1=C1 AND D1=TRUE => calculate A1*0.1
    2.) IF B1=C1 AND D1=FALSE => calculate A1*0.2
    3.) IF B1C1 => 0

  47. Hi, please I can't make the mega formula for the followings, please help me out:-

    Section Chapters
    I 1 to 5
    II 6 to 14
    III 15
    IV 16 to 24
    V 25 to 27
    VI 28 to 38
    VII 39 to 40
    VIII 41 to 43
    IX 44 to 46
    X 47 to 49
    XI 50 to 63
    XII 64 to 67
    XIII 68 to 70
    XIV 71
    XV 72 to 83
    XVI 84 to 85
    XVII 86 to 89
    XVIII 90 to 92
    XIX 93
    XX 94 to 96
    XXI 97

    For above infomation, how can a cell returns 'Section' in roman letters if fall in the right chapter number?

  48. Hi, I'm trying to figure out how to create a certain formula and am hoping someone can help. Here's what I want to do:

    Ex. If cells F7, F8, and F9 are NOT blank, return a value of "YES" to cell G7.

    Basically, the criteria in F7, F8, and F9 has to be checked off before G7 can be marked off as complete. I can do a "normal" If/then function referencing 1 cell, but am having trouble figuring out how to set a "True" value when referencing multiple cells.

    Thank you!!

    1. Hello Joanne!
      I hope you have studied the recommendations in the above tutorial.
      If I understand your task correctly, the following formula should work for you:

      =IF(AND(F7<>"",F8<>"",F9<>""),"Complete","")

  49. Hi question is there is Incentive which I have to pay to my employees according to their collection please refer below and I would like to calculate their incentive " % " based on their collection. please send me formula for the same.
    Amount Percentage
    25000-30000 10%
    30001-40000 20%
    40001-50000 30%

    Name of the employees Amount collected Incentive
    John 25000
    Paul 32000
    Peter 32500
    Isaac 48010
    Lemuel 32180

  50. 9873424761 6700670610
    9873424761
    9873424761 9873424761
    6700670610
    If first and second column is different then print both
    if first column is blank and second column is number then print number
    if first and second column are same then print any one
    if first column number and second is bland then print first column

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