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

  1. here is what I'm trying to solve:

    Cell could have the following data in it: apple, orange, grape, celery

    Looking for an IF statement that would drop into another cell either fruit or veggie.

    IF apple, orange, grape = fruit, if celery = veggie

    hope that makes sense. Thanks for your help.

    1. Happy to report that I figured it out!!! Thanks!

  2. hello I need help here
    If column A2 is less or equal to 70 and the sum for cells D2:AA2 is 50 I want it to say "more effort Please" for else to leave it blank. I have tried this but it is not working
    IF((AND(A2<=70, SUM(D2:Aa2=50)), More effort please", " "))
    Thanks

      1. Thank you so much it worked very well

  3. I want to color a cell based on whether it is a date or not. Eg:if A1 has a date, E1 should be green otherwise, no color.
    The closest I've got is using 'ISNUMBER' function. But this will color E1 even if I randomly put a number in A1.Is there anyway I can specify it to date?

    1. Hello!
      I think that the cell in which the date is written must have a date format. Therefore, I recommend using the CELL function and checking what format the cell has.

      =CELL("format",F3)

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

      1. I got it.. thank you?

  4. Hi,

    I'm trying to setup an if statement needing both the and & or functions.

    The OR statement is =IF(OR(Recruits="Yes",Refresh="Yes"),1,0)
    The AND statement will be if Eng = Yes, and IF(OR(Recruits="Yes",Refresh="Yes")

    I can get both to work individually, but not together, is this possible?

    I have created a matrix to see all possible outcomes, to test (Below)

    Eng Recruits Refresh
    Yes Yes Yes
    Yes Yes
    Yes
    Yes
    Yes
    Yes

    1. Hi!
      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(A2="Yes",OR(B2="Yes",C2="Yes")),1,0)

  5. Related text need to display in 1st table from the 2nd table, If we write a remarks in the 2nd table following and related text in the 1st table -- can any one write the if formula for this

  6. If age is between 18 and 56 and Sex is either "M" or "F", remark is "Qualified"

    2. what is the formula? If age is between 18 and 56 and Sex is either "M" or "F", remark is "Qualified"

  7. What is the formula"if function" if you are getting the valedictorian, salutatorian in a class with the basis of ranking?

  8. Hi, I notice many IF scenarios above, but none that fit my scenario where I am also using a hyperlink. Any chance you can assist with the answer? Below is an example of what I am doing. The only thing I am trying to add is when D12 & D13 = other number combinations, I want that to reference a different hyperlink (image). Is it possible to add multiple IF's and hyperlink's in a single formula?

    =IF(AND(D12=2,D13=1),HYPERLINK("https://test1.jpg","View Layout"),"")

    I know this formula is wrong, but it depicts what I am trying to do with as many conditions as I need:

    =IF(AND(D12=2,D13=1),HYPERLINK("https://test1.jpg","View Layout"),IF(AND(D12=5,D13=2),HYPERLINK("https://test2.jpg","View Layout"),"")...so on and so on

    Thanks,
    Kyle

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

      =HYPERLINK(IF(AND(D12=2,D13=1),"https://test1.jpg", IF(AND(D12=5,D13=2),"https://test2.jpg","")), IF(AND(D12=5,D13=2),"view2", IF(AND(D12=2,D13=1),"view1","")))

      You can learn more about HYPERLINK function in Excel in this article on our blog.

  9. I have used the formula =IF(P2="V13-1X-BLU-1",N2+1457), so when the cell contains the text V13-1X-BLU-1 it adds 1457 days to the date (N2 contains date). Is it possible expand the formula to recognise more text (V9-2X-BLU-1) and make different additions (+804) ?

  10. I hope someone could help me to condition, where I can have a column where it would either say “Complete” and “Incomplete” to reflect overall completion of requirements? The value is either Yes or No. If all answered 'YES' it will show "complete" and if any "No" answered "incomplete

  11. Hello! I'm trying to do a multiple conditional formula and I'm having trouble getting it to work...
    I'm trying to make something like this:
    If A is TRUE and B is TRUE, the formula should be something like AVERAGE(A;B)
    If A is TRUE and B is FALSE, then it should be =A
    If A is FALSE and B is TRUE, then it should be =B
    If A is FALSE and B is FALSE, =0
    I wanted to do it with multiple conditions, like A, B, C...
    How can I make it work? I tried everything I knew.
    Thanks!

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question. Use a formula like this:

      =IF(AND(A1=TRUE,B1=TRUE),AVERAGE(A1:B1), IF(AND(A1=TRUE,B1=FALSE),A1, IF(AND(B1=TRUE,A1=FALSE),B1, IF(AND(A1=FALSE,B1=FALSE),0,""))))

  12. I NEED TO CREATE A FUNCTION:

    =IF (IC25>IA25, "URGENT", "OK")

    BUT I ALSO WANT IT TO LOOK AT COLUMN - ID IS GREATER THAN 1 THAT IT CREATES "ON PO"

    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(ID25>1,"ON PO",IF(IC25>IA25,"URGENT","OK"))

  13. Please provide formula :
    IF B4=290,B4=2449,B4=250 C2="",C3+"", if True " Please provide this information" if False the data in E1.

  14. Found really useful thank you!

  15. I need a formula for below conditions (urgent required):

    01. if (a1 or b1 or c1 = "P", 1,0
    02. (i) IF(A1:C1)="P", "THREE",0 (three cells value are "P")

    (ii) IF (A1 AND B1="P" AND C1="") OR (A1 AND C1="P" AND B1="") OR (B1 AND C1="P" AND A1=""), "TWO",0
    (any two cells are "P" from three cell)

    (iii) IF (a1="p" and b1 or c1="") OR a1 and b1 ="" and c1="p") OR (a1 and c1="" and b1="p"), "ONE",0
    (only any one cell is "P" from three cells)

    1. Hi!
      I hope you have studied the recommendations in the tutorial above. It contains answers to all your questions.

  16. =TEXTJOIN("/",TRUE,IF(K2>=45,45,IF(K2=40,"35/5",IF(K2=30,"25/5",IF(K2=20,"10/10",IF(K2=15,"10/5",IF(K2=0,"",K2)))))

    Could someone explain what is wrong with formula or if I'm using the wrong mechanism.

    This is the error I am receiving "Wrong number of arguments to IF. Expected between 2 and 3 arguments, but got 7 arguments."

    Any help is appreciated, thanks.

    1. Hello!
      Please try the following formula:

      =TEXTJOIN("/",TRUE,IF(K2>=45,45,IF(K2=40,"35/5",IF(K2=30,"25/5",IF(K2=20,"10/10",IF(K2=15,"10/5",IF(K2=0,"",K2)))))))

      Hope this is what you need.

  17. i am using formula which is below mentioned but the result is for "yes" ok but for "#n/a" result is same #n/a but i need to show the result is "NO" which is base on formula.

    example: =IF(I1="#N/A","NO","YES")

    regards,
    ABAS

  18. Hello! I need a formula to display a scholarship.

    These are the conditions:

    if final_grade < 8 print “No receive”;

    • if 8 ≤ final_grade < 9 print 300 ;

    • if 9 ≤ final_grade < 9,5 print 400 ;

    • if final_grade ≥ 9,5 print 600 .

    of course, I have a column where final grades are placed.

    I wish I was clear with my commnet... I can't wait for an answer!

    Thank you, All the best!

    2. my difficulty is to use more conditions in "IF" syntax... I don't know how to do that...

      1. C6=GRADE
        =IF(C6<8,"NO RECEIVE", IF(C6<9, 300,IF(C6<9.5, 400, 600)))

      2. I tried to use more '' IF '' in Formula Bar ( like in Progamming for example - elseif ... ... ...) but it doesn't work and I don't think it's possible something like that.

  19. If cell C2 is greater than 50000, then give 20% discount, if less than 50000 give nil discount. Please assist me with the correct IF Function

  20. Thank you for this page. I used to work in an office and now use Excel to calculate my marks as a teacher but it's been a while since I've used the IF formula. So grateful to have found your summary!

  21. Hello, Can you please help me with the below formula , I want to set an Active, Non Active status relating an end date of a contract based on today date.
    Thank you,

  22. Hello,
    Column A has names of people ( repititions are also present )
    Column B has multiple codes names with which they are called
    example -
    column A(names) column B( code name) Column (pocketmoney) --------- sheet 1
    James Jamie ?
    James Jam ?
    nick Nicky ?
    john jonny ?
    derek D ?
    thomas Tom ?
    derek D dog ?
    thomas TIM ?

    a different sheet is same excel has data as below -- sheet 2

    Column A(fathers name) column B(mothers name) Column C ( pocket money ) Column D(code nam)
    ABC AAA 1$ Jamie
    XYZ BBB 2$ Jam
    XXX CCC 3 Nicky
    YYY DDD 4 jonny
    ZZZ EEE 5 D
    ZXZ FFF 6 TOM
    ZZY GGG 7 D Doog
    AZZ HHH 8 TIM

    My condition is as below
    If same names have multiple code names in sheet 1, then allocate the pocket money for children( column A in sheet ) based on their codes names in Column D of sheet 2

    example -
    James has 2 code names ( jamie and jam) , so the one with code name jam should get 2 $ as pocket money and jamie should get one

    i need a formula to write in column C of sheet 1 to retrieve the pocket money of the children having same name but different nick name

  23. I am creating a spreadsheet for a darts score card. I want to display "winner" in a cell when a player's score reaches zero and Loser in another cell at the same time for the opposing player. I also want to display Game on in both cells whilst the game is in progress, i.e. when both players score is greater than zero,
    I have managed to display winner when either player reaches zero, and Game on when they have a score greater than zero, but I am struggling to achieve displaying loser in the opposing cell which will still display Game on.

    1. Hi,
      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.

  24. I required a formula

    Name factory Name invoice type Qty
    A Rawal Plant GST 478
    B AJK Plant without GST 325
    C Rawal Plant without GST 843
    D AJK Plant GST 275
    E AJK Plant GST 564
    F Rawal Plant without GST 528
    G AJK Plant without GST 362
    H AJK Plant GST 358

    Required data separately Qty
    AJK Plant GST ?
    AJK Plant without GST ?
    Rawal Plant GST ?
    Rawal Plant without GST ?

  25. I have to measure the height of the balls in a a ball mill to determine how many balls to feed example

    Say I measure 100 cm from the height in the ball mill , there is another table that I have that gives ranges that 95 cm to 100 cm you add 1 kg of balls into the mill ,

    My question is how do I add my table that gives the ranges so that when I do the measurements and put them into my excel sheet it automatically looks at the ranges and automatically tells me add 1kg or 2kg etc... not sure if you get me but if you do I just need a formula to use

  26. I know how to reference cells on other sheets, but I'd trying to point to a cell on another sheet IF the date at the beginning matches the date I input.

    For example: I have dates running down the A column on Sheet1, and total sales running down the B column also on Sheet1.

    On Sheet2, I'd like to enter a date into A1 and, on A2, I'd like it to tell me the total for that day, as already listed on Sheet1.

    ---

    Sheet1:
    Dates:
    A1 - 01/04/21
    A2 - 02/04/21
    A3 - 03/04/21
    etc.

    Total sales:
    B1 - 7
    B2 - 4
    B3 - 8
    etc.

    ---

    Sheet 1:
    B:B - I can input the total sales for the day in A:A

    Sheet 2:
    A1 - I can input any date.
    A2 - show me the total sales for that date.

    ---

    I've tried using =IF on Sheet2 and manually nesting formulas for each cell in each row I want to reference on Sheet1, instance by instance.
    Eventually, I'd end up nesting over 100 formulas in a single cell on Sheet2.

    Is there a way to do this more generally?

    1. Hello!
      If I understood the problem correctly, the SUMIF function can be used in cell A2. You can learn more about SUMIF in Excel in this article on our blog.
      I hope my advice will help you solve your task.

      1. Hi Alexander,

        That's exactly what I needed. Perfect! Thanks for that :)

  27. Hi

    I hope you can help.

    I am looking to automate a cash flow forecast using figures from a separate 'Budget' tab, so that payments are listed on the specific day that they are due.

    I need to cater for various different suppliers, some of which are paid at the end of the month and others at various days during the month.

    I have a separate column which displays the payment due date, which will be listed as '31' for end of month (to help identify those that need adjusting for shorter months), and the specific day of month for all others.

    In short, I need the budget figure 'Budget K16' to only show on the specific day confirmed on each line ('B24') for that supplier, and if not due, to show as zero/empty.

    Budget K16 = Payment amount from other tab
    B24 = Day of month that payment is due
    D3 = Date

    I have come up with the following formula to ensure that the payments are displayed on the day that they are due to be paid, but the IF statement is not working.

    =IF(AND(OR(B24="31"), AND(D3=EOMONTH),'Budget '!$K$16,0),OR(DAY(D3)=$B$24,'Budget '!$K$16,0)

    Are there any obvious errors with this? Any help would be much appreciated.

    Kind regards

    Dave

    1. Hello!
      It is very difficult to understand a formula that contains unique references to your workbook worksheets. Correct me if I’m wrong, but I believe the formula below will help:

      =IF(OR(AND(B24=31, D3=EOMONTH(MONTH(D3),0)),DAY(D3)=$B$24),$K$16,0)

      Use external links to other sheets in your workbook. I cannot do this.

  28. I have to use single cell as a condition for the whole column.. How can i use it?

    For example in the below formula i have to use cell Formula!A2 for all the cells in the column W in the workings spreadsheet.

    =IF(Workings!W2=Formula!A2,CONCATENATE("V2",".",A3),CONCATENATE("I2",".",A3))

  29. Hello,

    Needing some help with a formula that will produce my values that might have a letter at the end of it. IE.

    =IFERROR(IF(OR(VALUE(B90)7000),"",VALUE(B90)),"") this works perfectly fine when I am doing only numbers like 1000 or 4000, but when I have a 3429G, this formula will obviously leave my cell blank. I need to get my cells that might have inventory letters and numbers together to post in that cell. Is there a formula to help identify if there is text within numbers?

    1. Hi,
      If there are no letters in a cell, it can be converted to a number. If this is not possible, then there are letters in the cell.
      For example:

      =IF(ISNUMBER(--(B1)),"Number","Text")

      I hope my advice will help you solve your task.

    2. Sorry it messed the formula up for some reason. Here is my current formula:

      =IFERROR(IF(OR(VALUE(B86)7000),"",VALUE(B86)),"")

  30. I'm looking to do a mail merge from an excel sheet to a word document/contract. I need a formula that will basically say any time you see "One (1)" in the document then "First (1st)" will appear throughout where needed. If "Two (2)" is written, then "Second (2nd)" would appear when needed. I would like to have this up to "Ten (10)" if possible.

    Any ideas for a formula?

  31. However I need to add EQ07 in to the above formula where as the condition is different

    IF(A5="EQ07",IF(AF<=70,"70")

    I need to have only one formula that reads value of Column A and accordingly reviews the condition and provides result in column B

    1. Hi,
      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(A5="EQ07",IF(AF<=70,70,""),"")

      Please have a look at this article — Nested IF in Excel – formula with multiple conditions

  32. Good Day! Need assist!
    I have a formula: =IF(I2>64,IF(J2<=$O$20,TRUE),IF(I2$O$20,TRUE),IF(I2>64,IF(J2>$O$20,FALSE),IF(I2<64,IF(J2<=$O$20,FALSE)))))
    O20 is a particular criteria near the table that I am changing.
    Currently I am receiving everything correctly, where True is that my system results (column J) predicted the results from column I and False appears when the system provided a wrong result. However, there is an exception that I need to add IF(I2<64,IF(J2<=$O$20,FALSE) I want to see in this case in the cell "EXCEP"
    Please, comment if I do not described the question correctly?

    1. Hello!
      Try the following formula:

      =IF(I2>64,IF(J2<=$O$20,TRUE,
      IF(I2<$O$20,TRUE,
      IF(I2>64,IF(J2>$O$20,FALSE,
      IF(I2<64,IF(J2<=$O$20,FALSE)))))),IF(J2<=$O$20,FALSE))

      I'm not sure if this will work. There is already a condition IF(I2<64 in your formula.
      I can’t check the formula, because I don’t know the conditions and I don’t have the source data.

  33. HELLO i need help please.

    i need a formula that says
    if in a cell there is the word- red then return a 3 if it says blue then return a 2 and if yellow then return a 1

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

      =IF(ISNUMBER(SEARCH("red",B1,1)),3, IF(ISNUMBER(SEARCH("blue",B1,1)),2, IF(ISNUMBER(SEARCH("yellow",B1,1)),1,"")))

      You can learn more about SEARCH function in Excel in this article on our blog.

  34. im looking for a formula for 2 pages to look up if the account number is the same it would add up the totals in the totals column for that account number and give me one total Can you help please.

  35. Please share if its says YES than add no value anf if it says NO than add value formula in excel
    AS EXAMPLE.
    A1 A2 A3
    YES 500 FORMULA PLEASE
    NO 100

  36. Hello to all,
    I really need your help about this formula. I've tried everything I know about excel...

    When I use this formula to check the field A30 (could be a date OR text Always On):
    =IF(OR(A30="Always On";A30>=TODAY());"ACTIVE";"NOT ACTIVE")
    If I put a date in A30, the result is correct, ACTIVE if higher or NOT ACTIVE if lower.
    But with any text in A30, it gives me always ACTIVE, instead of only recognizing Always On.

    I have tested the same formula but choosing different fields (A30 and B30):
    =IF(OR(A30="Always On";B30>=TODAY());"ACTIVE";"NOT ACTIVE")
    And in this case, it recognize either the date AND the correct or wrong text.

    Do you know why it is happening when it is the same field?

    Thank you for your help!

    1. Hello!
      Add a condition to the formula: if the date is checked, then A30 must be a number.

      =IF(OR(A30="Always On",AND(ISNUMBER(A30),A30>=TODAY())), "ACTIVE","NOT ACTIVE")

      I hope it’ll be helpful.

  37. Hi,

    I'm newbie to excel, I hope you could help me, basically I'm trying to get the formula if two conditions are met
    example: If A1="apple" and A4 contains %(eg.12345%Mango), then it will sum the C1/B4 and do nothing if not.

    Appreciate your response.

    1. Hello!
      What does the “sum the C1/B4” phrase mean?
      This formula shows the fulfillment of your conditions.

      =IF(AND(A1="apple",ISNUMBER(SEARCH("%",A4,1))),"Yes","No")

      You can write other values or formulas instead of Yes and No.

  38. Hello! I'm pretty new to excel but I'm trying to get a status using the equation below. There are three different options that can happen "completed", "not started", and "waived". What I'm trying to figure out is how to get "completed" and "waived" to come out across a row as "True" but if there were to be a "not started" for example in cell E2 then for it to show the status as "False".

    =IF(OR(AND(D2="completed",D2="waived"),AND(E2="completed",E2="waived"),AND(F2="completed",F2="waived"),AND(G2="completed",G2="waived"),AND(H2="completed",H2="waived"),AND(I2="completed",I2="waived")),"True","True")

    Thank you!

  39. Hi How would i set up formatting? using an If statement for example
    if A1:A3 = yes color whole row green (A1:BE1)

    thank you in advance!

  40. Hello!

    Trying to figure out how to do if statements to add up to a number between 1 and 4 if they hit certain if statements and then have that number multiple by 200

    If( B1>=A1,0,1) + ( If B2>=A2,0,1) + (B3>=A3,0,1) + (B4>=B4,0,1) .....Then depending on how many 1's that adds up to that sum would be multiplied by 200

    1. Hello!
      If I understand you correctly, then you can use any of these formulas:

      =(SUM(IF(B1>=A1,0,1),IF(B2>=A2,0,1),IF(B3>=A3,0,1),IF(B4>=B4,0,1)))*200

      =(SUM(--(B1<A1),--(B2<A2),--(B3<A3),--(B4<B4)))*200

      =((B1<A1)+(B2<A2)+(B3<A3)+(B4<B4))*200

      Hope this is what you need.

  41. Hi ,

    Could you please help me to find formula

    A | B | C | D | E | F | G | H | I | J | K | L | M |
    WARD|Ward|Description|Average|Ward.|Wardwise|Total|RES |RES|COM |COM|INDUSTRY|INDSTRY|
    1 |1 |RES |0.90% |1 |0.80% |3 |0.90%|2 |0.60%|1 | |
    2 |1 |COM |0.60% |2 |0.60% |1 | | | | |0.60% | 1
    3 |4 |RES |1.00% |3 |1.50% |1 | | |1.50%|1 | |
    4 |5 |RES |1.10% |4 |1.00% |1 |1.00%|1 | | | |
    5 |9 |RES |0.30% |5 |1.00% |2 |1.00%|2 | | | |
    6 |2 |INDUSTRY |0.60% |6 | | | | | | | |
    7 |1 |RES |0.90% |7 | | | | | | | |
    8 |9 |COM |0.30% |8 | | | | | | | |
    9 |5 |RES |0.90% |9 |0.30% |2 |0.30%|1 |0.30%|1 | |
    10 |3 |COM |1.50% |10 | | | | | | | |

    column A,B,C,D (data) (i wanted formula formula to sort column A-D in ascending )
    Column F - ward wise average (used formula =IFERROR(AVERAGEIF($B$3:$B$12,(A2),$D$3:$D$12),"") )
    Column H, J, L - used formula - IFERROR(AVERAGEIFS($D$3:$D$12,$B$3:$B$12,A2,$C$3:$C$12,$I$1),"")

    i have used data - sort in coulmn G,I,K,M but i want formula to sort wardwise-RES, wardwise-COM, etc.)

    in Column N - wanted highest average repeated in column D , and total highest average counts repeated in Column O

    Please help me in find the solution ........ plz need help

  42. cell A1 can have value "ok", "nok", or "n/a".
    same cell B1 can have value "ok", "nok", or "n/a".

    How can I in cell C1 return:

    ok if both A1andB1 are ok.
    nok if either is nok.
    ok if A1orB1 is ok and the other is n/a.
    n/a if both are n/a.

    Below formula returns all except n/a.

    =IF(OR(AND(DD3527="ok", DR3527="ok"),AND(DD3527="n/a",DR3527="n/a")), "ok", "nok")

    1. Hello!
      Please try the following formula:

      =IF(OR(A1="nok",B1="nok"),"nok",IF(OR(A1="ok",B1="ok"),"ok",IF(AND(A1="na",B1="na"),"na","")))

      Hope this is what you need.

      1. You are AWESOME!! Thank you!!

  43. Hi - can someone help me write this in a Boolean "IF" statement.

    IF the years of experience is "0" or less than 5 years, the raise is 2%
    IF the years of experience is 5 years and less than 10 years, the raise is 3%
    IF the years of experience is 10 years and less than 14 years, the raise is 4%
    IF the years of experience is 13 years and less than 18 years, the raise is 5%
    IF the years of experience is greater than 17 the raise is "0"

    Tanya

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question.
      Your conditions 4 and 5 contradict each other.

  44. I just want to say that i greatly appreciated and was just amazed how you are still consistently and humbly solving questions of people without getting sick of it, even after more than 2 months of the publication date. Kudos to you man!

  45. Hello! I'm trying to get excel to do an IF function but am having trouble with my formula. I want it to be if Field 1, Field 2, or Field 3 are selected then "Soccer". If Field 4, Field 5, or Field 6 are selected then "Baseball". I'm trying to get excel to automatically bring up with fields are soccer or baseball fields. Thanks!

    1. Hi,
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question.
      Your conditions contradict each other. If the values will be written in all 6 cells, what value do you want to get? “Soccer” or “Baseball”?

  46. Hi, what can I do if I want to print a specific data-field(cell already typed in excel) depending upon a value entered by the user?

    For instance, IF(B1="Petrol", (//I wish to print cell D1 adjacent to it))

    1. Hi,
      You can use the formula

      IF(B1=”Petrol”, D1,"")

      I hope you remember that an Excel formula can only change the value of the cell in which it is written.

  47. Hello there!!

    I am trying to analyze data from a google doc survey. The question I am interested in analyzing was a multiple checkbox response with multiple answers. I am wanting to take the data from that cell (that shows multiple text-based answers) and create a formula that pulls out a specific option/text response and then inputs a 0 or 1 if the option/response is present in the initial cell that has the multiple text answers. How would I create this formula?

    Example of what I want:
    Participant # | Response | Option 1 present | Option 2 present | Option 3 present - etc.
    Participant A | Opt 1, Opt 4 | 1 | 0 | 0 |
    Participant B | Opt 1, Opt 2, Opt 3 | 1 | 1 | 1 |
    Participant C | Opt 3, Opt 4, Opt5 | 0 | 0 | 0 |
    Participant D | Opt 2, Opt 3 | 0 | 1 | 1 |

    1. Hi,
      I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula below will work for you:

      =IF(ISNUMBER(SEARCH(1,$B$1,1)),"Opt 1 "&C1,"")

  48. I want formulae based on Num of Trainings
    Num of Trainings
    3
    7
    15
    3
    0
    -7
    Days1

    Pls check where am doing mistake - '=IF(f20,f230,f260 Days"))) and help me in correction

  49. Is there any formula for designing timetable for teachers in a school/ college or university?

  50. Hi, I need a formula for my work,
    I work on students results, in remarks I want to show if rank=1 ,"first position" and if percentage < 60, "fail"
    Want to show the both in the same column.
    I know to use rank formula for positioning, and if formula, but I want to merge them both.

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