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 37. Total comments: 4557

  1. Hi can u tell me how to use if and or function to get depreciation value with sln, dB and Syd method for the same data in same cell.

  2. if-or(L59>L60,L59>L61,L59>L62),''yes'',''no'' which button to click on the keyboard in order to get to yes or no. i am learning on line.please help

  3. Hi,
    I am trying to create a formula with 3 different conditions.
    If J3<5 and K3<5 it should calculate K3-J3+1
    If J34 it should calculate 4-J3+1
    If neither is true the result is 0

    I have tried different variations o the following but nothing works:
    @IF(OR(AND(J3<5,K3<5),k3-j3+1,AND(J34),=4-j3+1,0))

    Any advice would be greatly appreciated. Thank you

    1. ERROR in 2nd "If" condition I posted.
      Should read: If J34, it should calculate 4-J3+1
      ERROR in formula I posted.
      Should read: @IF(OR(AND(J3<5,K3<5),=k3-j3+1,AND(J34),=4-j3+1,"0"))

      I copied and pasted so I am not sure what happened. So sorry for any confusion!

      1. OK so strange - after hitting Send the text changes in my post!
        Where it says J34 it should say IF J3 is less than 5 and K3>4.
        Hope the 3rd time is a charm! Thanks again.

        1. NEVER MIND - I figured out by studying at least 1,000 questions already answered here over the past few days.

          Correct formula: =IF(AND(J3<=4,K3<=4),K3-J3+1,IF(AND(J34),4-J3+1,0))

          Thanks to everyone who answers strangers very complex problems on this site. AMAZING!

          1. And once again my formula changed upon posting.

            If anyone needs the correct formula - respond to this and I will try to write it again with some other method

  4. HI
    I NEED A FORMULAR THAT WILL HELP CHECK IF A STUDENT HAVE PASS ALL REQUIRED COURSE.
    THANK YOU

  5. Cell G16 shows total.
    I want G17 to display "Level One" if cell G16 falls between B4 on another sheet (number listed in cell B4 is 10-13). Need this nested with C4 (14-16), d4 (17-19), e4 (20-22), f4 (23-27), g4 (28-38)

  6. i have to design a excel sheet for calculating printing cost, which involves multiple conditions
    categories :paper size, quantity, paper quality (100 GSM), Lamination,etc., i have to calculate paper cost, plate cost, labour cost according to the inputs.

    under paper size category there are diff sizes like
    1.a4
    2.a3
    3.crown
    4.double crown
    5.dimmy
    etc

    under paper quality categery it also varies according to gsm like 100gsm,170gsm,300gsm etc

    by looking up and combining paper size and gsm i have to calculate paper cost and remaining calculations labour printing cost, plate charges are also have to be calculated using combined inputs.

    please suggest me formulas with example to use / provide me a model excel.

    Thanksalot,

  7. does any know
    I want to write an argument statement that if a number in a certain column >-999 or<999 then send me an email.
    does anyone know how I can write this?

  8. Hello,

    I'm working on a project with 3 criteria and my formula seems to super long.

    =IF(AND(K2=12,L2>2),6,IF(AND(K2=6,L2>2,L28,M211,M20,M22,L20,M25,L210,M28,M26,L20),2,IF(AND(K2=3,L2=8,M2>25),2,IF(AND(K2=6,L2=8,M2>25),4,IF(AND(K2=6,L2=8,M2>25),4,0)))))))))))))

    1. Column K is fixed with 3 numbers = 3, 6 and 12
      Column L could be between from 1-12
      Column M could be between from 1-31

      This is my formula so far. It's Super long. Maybe you can help me.

      =IF(AND(K2=12,L2>2),6,IF(AND(K2=6,L2>2,L28,M211,M20,M22,L20,M25,L210,M28,M26,L20),2,IF(AND(B2="KSA",K2=3,L2>2,L26,L28,M28,M20,M212,M22,L25,L28,M28,M20,M22,L22,L22,L22,L22,L22,L22,L225),2,IF(AND(B2="UAE",K2=6,L2=8,M2>25),4,IF(AND(B2="KSA",K2=3,L2=8,M2>25),2,IF(AND(B2="KSA",K2=6,L2=8,M2>25),4,IF(AND(B2="UAE",K2=6,L2=8,M2>25),4,IF(AND(B2="Jordan",K2=6,L2=8,M2>11,M225),4,IF(AND(B2="Jordan",K2=4,L2=8,M2>11),2,0))))))))))))))))))))))))))))))))))))

  9. Hi all looking for help with and IF AND and Or function

    im trying to get it to either times by .25 if both parameters are met or if only one then times by .5

    And if none of the parameters are met then leave as is.

    =IF(AND(J45="Tigress",M45>100000),O45*0.25,O45)*(OR(IF(J45="Tigress",O45*0.5,O45)))

    Any help would be much appreciated

    1. =IF(AND(J45="Tigress",M45>100000),O45*0.25,IF(OR(J45="Tigress",M45>100000),O45*0.50,0))

      This should work

      Explanation on the below

      If J45=Tigress and M45 less than 100000, value in cell O45 will multiply to 0.25
      If J45=Tigress or M45 is less than 100000, value in cell O45 will multiply to 0.50
      If neither of the equation, the answer will be 0 - or you can change it to "Not found" instead of 0

  10. Hello,

    I am trying to use the formula below, but it is not correctly categorizing my data and I cannot figure out why.
    =IF((AND(A2>=TIME(7,0,0),A2<=TIME(18,0,0))),"Diurnal","Nocturnal")

    While some categorizations are correct, others are not. For example one species was caught at 15:36 Army time, but is categorized as nocturnal when it should be diurnal. Any thoughts?

    Also, as a fun bonus question: is there any way I could add a third option for species that come out at dawn out at dawn or dusk?

  11. Aloha,
    Can someone please check this formula? =IF(C3:D3=E3:K3), "BALANCE")

  12. Not entirely sure how the non-sensical formula in my last post managed to get in there. Note to self: Friday afternoon is no time for troubleshooting and analysis. It was clearly Beer O'clock and I was not paying attention to my inner voice. :)
    Feeling bright and bushy tailed on this Saturday morning, I was able to solve the puzzle.
    =IF(OR(AND(I12>F2,I12F2,J12F2,K12F2,L12<G2)),"Y","")

  13. I am having a heck of a time trying to get an IF(OR(AND formula to work correctly. My rows contain maintenance requirements with varying frequencies or recurrence. I then have 15 date columns each holding the next projected date for each maintenance requirement. The statement below captures a search through 3 of the 15 columns to see if the projected maintenance falls prior to a planned maintenance time. I keep getting an #VALUE! error. Any help would be appreciated. I can't help but think it is something simple. I have had attempts where I can get it to return a "Y" for the first occurrence but nothing after that in subsequent years. Thx.
    =IF(OR(AND(I12>$F$2,I12$F$2,J12$F$2,K12<$G$2)),"Y","")

  14. Hi All,
    Need a help in sorting out a problem. I'm trying to use the combination of IFS, AND, OR here.
    (All values are dates)

    Condition 1:
    Today's date (C1) = 04-12-20
    A1 = 04-12-19, 04-12-18
    &
    A1 = 04-02-21, 04-12-21

    I want to merge these conditions into a single cell E2

    If (C1-A1365,"Past 13+ Months") and if(A1-C190,"Fcst 4+ Months")

    Problem is that, it is reading two conditions as same, if the value is 0, which I'm trying to avoid.

    I would really appreciate some help on this by anyone.

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

      =IF((C1-A1) > 365,"Past 13+ Months",IF((A1-C1) > 90,"Fcst 4+ Months",""))

      I hope it’ll be helpful.

  15. I am preparing examination results, that consist of thirteen subjects each subject has two scores, then i calculated the average for each subject by considering two score i need a help on how to find the result points by considering seven subjects that a student got higher marks.

  16. In Cell I5 Create a drop down list with MON,TUE,WED, THU, FRI, SAT, SUN
    In Cell I5 Select TUE from the drop down list
    In Cell I6 Create a drop down list with BKLYN, QNS, NYC, BX, SI
    In Cell I6 Select NYC from the drop down list
    In Cell J5 Create an IFS formula that will result "# of Drivers working" on the DAY selected in Cell I5 and
    the BORO selected in Cell I6
    I know, how many drivers were working on the selected day and borough as I am following the chart but I have a problem with the formula. I don't know, how the formula will be since it requires me to have a formula in one cell with two different condition. Need help in formula.

    MON in Bklyn, Nyc, Qns, Bx, Si is only 1 driver working
    TUE all the same monday results
    Wed all the same monday results
    Thur all the same monday results
    Fri all the same monday results
    Sat all the same monday results
    Sun all the same monday results
    Only 1 driver was working when the condition met with any options if its Days or Borough they all have a same answer which is 1.

    Thankyou.

  17. Hello
    Rate Quantity Result
    2 2 4
    2 Text Text
    Text 2 2
    Text1 Text2 Text2

    I need the result like this help me with the formula please

    1. 2*2 = 4
      2*Text = Text
      Text*2 = 2
      Text1*Text2 = Text2

      All possibilities should be in one formula

  18. Hello!

    I'm having a bit of a challenge on this issue. I have a list of 2 columns, A and B.
    Column A has duplicates that I need to remove, but I need to "Textjoin" column B first.

    > Forgive extra Parenthesis, I need them to be able to read it better.

    Currently using "=IFS((A2=A3), (TEXTJOIN(", ",TRUE,B2,B3)))

    However, the problem is that it leads to duplicate text joins, and it has a limit to how many values it's joining. I have it set as the first function of the column to start things out.

    Formula #2 I came up with:
    =IF((A2=A3), (TEXTJOIN(", ",TRUE,B2,B3)), (IFS((A2=A1), (TEXTJOIN(",",TRUE,E1, B2)))))

    However, even this doesn't work as I want it to because it's merging cells multiple times. Not all of the groupings are by two. Some are 5 cells, some are 3, etc.

    Formula #3 I came up with:
    For this one to work I had to put in a value for the first cell in Column C. (I just set it =A2).
    "=IFS((A3=A2), (TEXTJOIN(", ",TRUE,C2,B3)), (A3=A4), (TEXTJOIN(", ",TRUE, B3, B4)))"

    Formula #3 produced the best results, but still adding cells incorrectly.

    The reason for this; there are roughly 12K rows and I need to reduce it as much as possible. However, I don't have a month to go cell by cell confirming it joined correctly.

    Hopefully you can pass on some assistance!

    1. Hello!
      Unfortunately, without seeing your data it is difficult to give you any advice. In which cells do you write these formulas? Please provide me with an example of the source data and the expected result.
      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.

  19. English Punjabi Hindi Math Science SST Comp Sci BELOW40% 90%&ABOVE
    16 24 40 8 8 12 20 0 108 45.0 ENG,MAT,SST
    24 28 40 10 18 12 26 0 132 55.0 ENG,PUN,MAT,SCI,SST HIN
    12 6 6 8 10 12 14 0 54 22.5
    16 22 10 4 10 4 22 0 66 27.5
    18 20 30 14 16 18 16 0 116 48.3
    14 22 30 14 16 18 16 0 114 47.5
    WHICH FORMALUA APPLYED IN EXCLE TO GET ANSWER. PLEASE HELP ME

  20. Hi! A non-techy person here and I need help for the formula for this please. (If B2 and C2 is blank then 0, if B2 and C2 has a value then D1+B2-C2) Thanks in advance!

    1. Hi,
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question. Please use the following formula

      =IF(AND(B2="",C2=""),0,D1+B2-C2)
      or
      =IF(ISBLANK(B2:C2),0,D1+B2-C2)

      I hope it’ll be helpful.

  21. Please i need a formula for the following:
    If "Abbasia" then add 30 minutes to the time (The time is in another cell) and if Maadi Please write the time as it is (The time is in another cell)

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question about IF function.
      To add 30 minutes to the time, use the formula

      A1+TIME(0,30,0)

      I hope I answered your question. If something is still unclear, please feel free to ask.

  22. I want to apply a formula to print purchase order status is active or if print purchase order status is cancel then print cancel. please guide me what is the formula i need to apply to get result

    1. Hi,
      An Excel formula can change the value of a cell. But she cannot start printing the document or cancel printing. To do this, you need to use a VBA macro.

  23. hi all

    i have some data like column a column b column c column d column e
    a1 b1 c1 d1 e1 f1 g1 h1 i1 j1
    1 2 3 4 5 6 7 8 9 10

    if b1="a", then c1 should 2 d1 3 e1 3 like series should continue
    if a1="a", b1="a", then c1 shoud start with 1, d1 2, e1 3 4 5 6 like

    if f1 "a", g1 should start with 6,7,8,9,10............

    A letter it may repeat and continues , but series should be continue.

    ple provide the formula for above logic.

  24. I'm getting a FALSE status on the one logic.

    =IF(AND(V8=1,Q8>7/31/2020,Q8<9/1/2020),R8,"")

    Q8 is the date 9/17/2020
    R8 is a dollar amount

    Logic 3 keeps returning FALSE, but I can't figure out why.

    I'll be copying this down a column where some of the dates fit the parameters.

    1. Hello!
      Here is the article that may be helpful to you: Using IF function with dates
      Please try the following formula:

      =IF(AND(V8=1,Q8 > DATE(2020,7,31), Q8 < DATE(2020,9,1)),R8,"")

      or

      =IF(AND(V8=1,Q8 > DATEVALUE("7/31/2020"), Q8 < DATEVALUE("9/1/2020")),R8,"")

      I hope I answered your question.

      1. They both worked! Thank you so much!!!

        From looking through your site, I had tried the DATEVALUE, but missed using the " marks.

        Thank you again!

  25. I have issues producing a function to fit in my amount cell.
    Ok.
    I have income from 3 sources Beans, Rice and Garri and I can only spend within the income received from each of these items. So I have an income section where I record income received from these items and their totals are in cell (A5, C5, E5 respectively). So For example, if I received 10,000frs as income from Beans, I cannot spend above the 10,000frs.
    I now have an Expenditure section where I have a date column, a searchable list column where I can select the source of income (Beans, Rice, or Garri) i want to spend from at the time being and then a column for amount.
    I want an IF function that when the amount of Expenditure of a particular item is more than the income received for that item, it should give an error message.
    For example. If I received 10,000frs for Beans, and I put the first Expense in the amount of Expenditure section, 5,000frs, choosing Beans as source of income and now if I want to put the second Expenditure on a different date say 6,000frs still choosing Beans as source of income. It should return an error message because is more than 10,000frs income received from that item (Beans)

    Please can someone help me out

    1. Hi,
      Your request goes beyond the advice we provide on this blog. This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.

      1. Ok. Thanks for the reply Sir. Please How can I get to you may be privately for help in this.

  26. Hello Ma'am

    how can i summary all the fail and pass in the same sheet? I want to separate all the fail and pass after using if function please help. I don't want to manual copy and paste the name the selected fail or pass thank you and GOD bless.

  27. Okay, I have a question. I am trying to pull data from one cell that has a lot of data. IF the data were consistent, I believe it would be easy. But it is not.

    So, I have cell A2 for example the data in the cell is something similar to this:
    Persons Cell Phone Number: 123-456-7890 (not everyone enters it that format and I can't 'force' it to be that way)

    Persons Personal E-Mail Address: (again, not everyone types it out the same)
    -Variations could be Persons Personal E-Mail Address: with or without a space before the start of the email
    Persons E-Mail Address: with or without a space before the start of the email

    All I want to do is easily pull out the persons email address and cell phone number. Here is an actual example of some of the data that could be in ONE cell (the data is not always in this order):

    PERSONS E-MAIL ADDRESS: PERSONS.EMAIL@OUTLOOK.COM
    ________ (SM INITIALS)
    PERSONS CELL PHONE #: (XXX) XXX-XXX
    ________ (SM INITIALS)
    WHERE ASSIGNED TO (SSSSSS) : MR JIMMY SMITS
    OFFICE/CELL PHONE NUMBER: (XXX) XXX-XXXX / (XXX) XXX-XXXX
    E-MAIL ADDRESS: ANOTHER PERSONS EMAIL@OUTLOOK.COM

    IF I AM UNABLE TO CONTACT MY OFFICE, I WILL CONTACT THE HELP DESK AT 1-800-XXX-XXXX.

    1. Hello!
      To extract e-mail from the text, use the formula:

      =MID(A2,SEARCH("PERSONS E-MAIL ADDRESS:",A2,1)+23, (SEARCH("_",A2,SEARCH("PERSONS E-MAIL ADDRESS:",A2,1)+23+1)) - (SEARCH("PERSONS E-MAIL ADDRESS:",A2,1)+23))

      To extract a phone number from text, use the formula:

      =MID(A2,SEARCH("PERSONS CELL PHONE #:",A2,1)+21, (SEARCH("_",A2,SEARCH("PERSONS CELL PHONE #:",A2,1)+21+1)) - (SEARCH("PERSONS CELL PHONE #:",A2,1)+21))

      I hope this will help, otherwise please do not hesitate to contact me anytime.

      1. Thanks, the problem is that only works if the data is all exactly the same. If the way they enter the title of the persons email address is PERSONS E-MAIL ADDRESS then it might work if all of the other data was in the same location, character wise, in each cell. Sometimes, however, they enter the title of the persons email address as PERSONS PERSONAL E-MAIL ADDRESS: sometimes they enter it as PERSONS E-MAIL ADDRESS: and then to top it off, if they put spaces after the : it throws it off even more.

        1. Hello!
          You can remove extra spaces using any of the methods indicated in this article. But if they enter data in the wrong place, the program cannot fix it.
          However, by changing these formulas, you can extract any part of your text.
          If there is anything else I can help you with, please let me know.

  28. Hi,
    I need to help do this correction and needed to add more like this???

    =IF(C4,(G4*H4*2+G4*J4*2)*L4),IF(C5,((((I5+H5)/2)*G5)*2+(G5*J5+0.141*K5))*L5)

  29. Hi, need help to create a formula that can add 7 numbers (my own given numbers) to generate 5 number combinations between 1 and 36.

  30. hello Team, I'm a bit stuck I need your help. So what I'm trying to do is to put 'if' formula in this way:
    I have 10 persons and 8 cars, when I put a person to work i want to be assign to a car, but when that person is off I want another person to be on that car. The problem that I have is that if I put the first person off the second person will take the car but the will double up.
    My formula is in this way: =if(B=1, A, B=0, C). I also tried =if(B=1, A, B=0, C, C=1, D)
    I think I would need a 'but' somehow but I don't get the hang of it.
    Any ideas would be very helpfull

    thank you

  31. Hello Team, I want to know the formula if 35Km, 45Km, 65KM is their and I want to fixed $7 for 5Km per ride then what was the formula

  32. I have to add 'if A2>=200,B2=200,B2<=0)),"999",A2/B2)

    But, Can I make this formula in IFERROR form ? because in 'value in false' shows #DIV/0!

    Thank you very much in Advance.
    Sean

    1. Hello!
      The purpose of your formula is not entirely clear to me. However, your conditions B2=200 and B2<=0 cannot be met at the same time. If B2=0 you will get # DIV/0 error!

  33. I have multiple sheets with similar information; students fill in one of the sheets but I want the last sheet to look at all the other sheets (at a specific cell) to see which one(s) are filled in and enter that number in the formula cell. The number will be the same regardless of the sheet information entered in. So regardless of which sheet the information was entered on, it should return a specific number.

    1. This is one of the formulas I tried; but I received an error (#value!) - =IF((OR(AP!E5:S5=1, OA!G5:S5=1)), "1", "0"). I am looking at a range of cells in each sheet for a response.

  34. Trying to write a formula for calculation of: 25% of the quantity or (quantity x rate = ) Rs.5000 whichever is more

  35. Hey Ablebits.
    Thx for this amazing page. i've been using you so much. I'm unsure on how I post a question in a new topic. so feel free to move me in the right Direction.

    I have this Formula
    =if(and(D16=C4,D16=C5,D16=C6,D16=C7,D16=C8,D16=C9,D16=C10),B10,"Forkert beløb i D16"))))))))

    How do I fast make changes to this, so that all the times it says "D16" I will change it to "K30"
    Cheers

    1. Hello!
      Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you the following formula:

      =IF(AND(K30=C4,K30=C5,K30=C6,K30=C7,K30=C8,K30=C9,K30=C10),B10,"Forkert beløb i K30")

  36. Hi
    is it possible to use VLOOUP function in multiple IF statements as below? already try cannot work.

    need your advice if have another way to solve it.

    thanks in advance.

    =IF(EXACT(G5,"AGENT"),VLOOKUP(E5,MASTERLIST!C:G,2,FALSE), IF(EXACT(G5,"MINI AGENT"), VLOOKUP(E5,MASTERLIST!C:G,3,FALSE)), IF(EXACT(G5,"DROPSHIP"), VLOOKUP(E5,MASTERLIST!C:G,4,FALSE)))

    1. Hello!
      It is very difficult to understand a formula that contains unique references to your workbook worksheets. Hence, I cannot check its work, sorry.
      However, the formula contains an error. Try to change it.

      =IF(EXACT(G5,"AGENT"),VLOOKUP(E5,MASTERLIST!C:G,2,FALSE),
      IF(EXACT(G5,"MINI AGENT"),VLOOKUP(E5,MASTERLIST!C:G,3,FALSE),IF(EXACT(G5,"DROPSHIP"),VLOOKUP(E5,MASTERLIST!C:G,4,FALSE))))

      I hope it’ll be helpful.

      1. Thank sir, formula work perfect!!

  37. I have a condition kindly provide me the formula

    upto 10 KM a company charges $20
    above it per km a have additional charges is $2 per KM

    if a person travel following km what was the formula

    A B C D E F
    1 18
    2 10
    3 24

    Then what is the formula I put in B1

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

      =MAX(A1-10,0)*2+20

      I hope it’ll be helpful.

  38. Sorry meant that I wish the span of normal to be between 53.

    My initial thought was fx= IF(A1>=71, "hypertension, IF(A1<=53, "hypotension, IF(A1=53, "normal"))))

    1. Hello!
      Your terms change with every comment. Your conditions A1 <= 53 and A1 = 53 cannot be met at the same time. Perhaps this formula will suit you

      =IF(A1>=71,"hypertension",IF(A1<=53,"hypotension","nornal"))

      Pay attention to the following paragraph of the article above — Multiple IF statements in Excel

  39. Hello!
    Perhaps you can help me. I am hoping to write a IF + AND statement with multiple results.
    A171 is "2" and A1 >53 AND <71 is "0". Thanks!

    1. Hello!
      Please reread the article above, it covers your case completely.
      What does the condition mean - A171 is “2”?

      1. I wish to write fx where A1>71= hypytension, A153=71, "hypertension, IF(A1<=53, "hypotension, IF(A1=53, "normal"))))

        However this does not work for me. Thanks

        1. I wish to write fx where A1>71= hypytension, A153=71, "hypertension, IF(A1<=53, "hypotension, IF(A1=53, "normal"))))

          However this does not work for me. Thanks

  40. Condition:1
    Marks >=20 & Marks =20 & Marks =41 & Marks =41 & Marks <=60
    % Marks 110 & Above
    Eligible for Rs.3000/- reward

  41. Hi

    Can any one apply the formula for given 2 conditions please.

    Condition-1
    Marks 20 to 40
    Marks 41 to 60
    Marks 61 to 80
    Marks 81 to 100

    Eligible Marks 30
    Score (Marks) 30
    % of Marks 100
    Award for 1500

    Eligible Marks 30
    Score (Marks) 35
    % of Marks 117
    Award for 2500

    Condition-2
    Marks 20 to 40
    Marks 41 to 60
    Marks 61 to 80
    Marks 81 to 100

    Eligible Marks 45
    Score (Marks) 45
    % of Marks 100
    Award for 2000

    Eligible Marks 45
    Score (Marks) 55
    % of Marks 122
    Award for 3000

    1. Hello!
      Sorry, I do not fully understand the task. ould you please describe it in more detail? What result do you want to get? Thank you!

  42. Hi can anyone spot the error in the following formula? Thanks.

    =IF(C$7<=55,"37%", IF(55<C$7<=60,"26%", IF(60<C$765,"12.5%"))))

    It gives either 37% (for 55) as outcome.

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

      =IF(C$7<=55,"37%",IF(AND(55

  43. Hello, I am attempting to forecast employee availability based upon a singular calendar date.

    Through conditional formatting I’ve created cells to be filled if they align with the start and end dates based upon a singular employee being able to forecast 10 different start and end dates columns.

    I was able to get all 10 to feed into a singular cell based on a IF(OR(AND statement, however I will only get a 1 if true and 0 if false. For the entirety of the formula...

    My end-state is that I would like to create a data validation that doesn’t allow any date to overlap any of the other dates.

    My thought was if a cell yielded 1, great! If the cell went greater than 1, then the user would get an error message stating the date overlaps with another.

    Unless you know a better solution? I am all ears, Grand Master Yoda :)

    Please Help
    Warm Regards, Michael

    1. Hello!
      Unfortunately, without seeing your data it is impossible to give you advice.

      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.

  44. Hi Friends

    Could you please help me to get the excel formula?

    c11 = 50% of b11 or 100000, which one is lower

    Thanks
    Manik

  45. Good morning Svetlana,
    I really need some help on this,
    How do i write a formula to convert every 0.30 to 1.
    Assuming cell a1 contains 100, how do i count very 30 as 1.

    I hope this makes sense and I really appreciate the assistance!!
    Thank you in advance for the help!!
    Kind regards,

    Samuel

  46. Hello
    i have a list of names, scores, sex and statuses and here's my assignment:

    if the "status" of the customer is "Faculty" then give it a value with "Dr. " plus a space plus First Name plus a space plus Last Name.
    if the "status" of the customer is "student" and the "sex" is "M" then give it a value with "Mr. " plus space plus first name space plus last name
    if the "status" of the customer is "student" and the "sex" is "F" then give it a value with "Ms. " plus a space plus first name plus space plus last name

    please help me out with that one i am completely clueless on how to do it..

  47. Hi would you be able to help me with the below. I have 2 formulas that work separately however want to merge them into one formula.

    =IF(OR(AND(SUM(W3:X3)>=100,Y3>=0), AND(SUM(W3:X3)<=100, Y3<=0)), "Yes", "No")

    =IF(W3="","N/A", IF( X3= "", "N/A", IF(Y3="", "N/A")))

    I would like to add into the first formula that if W3 or X3 or Y3 are blank, then it should return "N/A"

  48. If you write 5 column like this

    A.Name
    B. Source (broker,ref and direct)
    C .product value
    D. commission %
    E. Commission amount( need formuls this column)

    1 ) if any broker(column B) sale 1 product
    Get commission (product value* 2%)=

    2 ) any existing buyer through his refrence sale 1 product to any customer
    Get commission 3 types
    * 1 st deal = 10k
    * 2 nd deal = 20k
    * 3 rd deal = 30k

    And

    3) if direct sale any product
    Get 0 commision

    How to codition formula in one cell

    1. Hello!
      Your task is not completely clear to me. Explain condition 2 using your table data as an example. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

  49. Hi, i want determine the latest date for this:
    If: a1=complete, c1=complete, e1=complete,g1=complete, i1=complete, if all of these are in "complete status" i want to get the latest date: b1=2/1/2020,d1=3/2/2020,f1=4/2/2020,h1=5/1/2020,j1=6/1/2020,j1=7/1/2020

  50. Please can someone help me with an excel formula? I have a cell E6 which has a drop down (Not Applicable, Fully Implemented, Mostly Implemented, Partly Implemented and Not Implemented). I want cell E7 to output 100%, 100%, 75%, 50% and 0% when E6 is Not Applicable, Fully Implemented, Mostly Implemented, Partly Implemented and Not Implemented respectively.

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