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 51. Total comments: 4461

  1. I'm trying to return different values, based on multiple ranges of cells.
    e.g.
    if any cell in the range a1:a4 contains a number value, then true=1,false=""
    and if any cell in the range a5:a9 contains a number value, then true=2,false=""
    and if any cell in the range a10:a20 contains a number value, then true=3,false=""
    etc.

    Have tried several formulas and keep getting error message.

    Are you able to help me with this?

  2. Hi! I am working in excel and need help with an IF formula for the following statements:

    If the cell value is =8 then 30 points are awarded
    If the cell value is >=10 then 60 points are awarded
    If the cell value is >16 then 30 points are awarded
    If the cell value is >17.5 then 0 points are awarded

    This IF statement is meant to calculate whether our wholesaler is stocking the appropriate # of days of inventory. If they stock 8-10 days of inventory they are awarded the maximum points. They are penalized for dipping below or above the targeted range per the schedule above.

    Any help you can provide would be greatly appreciated!

    1. Hi Kristen,

      Here you go:
      =IF(A2>17.5, 0, IF(A2>16, 30, IF(A2>=10, 60, IF(A2>=8, 30, ""))))

      Please note that I use >=8 in the last logical test, because =8 would return nothing for 9 days. Anyway, you can always adjust the conditions the way you want :)

      1. Good Day,
        Can you kindly help me with the following.
        I want to use a function for the following.
        There are codes which starts at 1000-000 till 7400-000, what I want to do is that if a certain code is typed in, there must automatically display 15 or 0 in another column in which I'm typing in the function, 15 if true 0 if false.
        Kind Regards,

      2. Thank you!!!

  3. =IF(F14=D14,C14,IF(F14=0.7,C15, IF(F14=0.72, C16, IF(F14=0.74,C17, IF(F14=0.76, C18, IF(F14=0.78, C19, IF(F14=0.8, C20, IF(F14=0.7, C21, IF(F14=0.84, C22, IF(F14=0.86, C23,IF(F14=0.88, C24, IF(F14=0.9 ,C25, 0)))))))))))).. I TYPE THIS FORMUALLA MANUAL. IS THERE ANY OPTIONS TO DO SHRT WITHOUT TYPING THE NEXT CONSECUTIVE CELLS.

  4. HI!
    I am working on a spreadsheet to keep track of real estate commissions and need a formula to help me. I thought it was the if function, but it does not seem to be working.

    I need it to do:

    If C3="yes" and D3>500000 then K3= H3*.5 or if C3="yes" and D3<500000 then K3= H3*4

    Any help would be great!

    Thanks,
    Mel

    1. Hi Mel,

      Try this one:

      =IF(AND(C3="yes", D3>500000), H3*0.5, IF(AND(C3="yes", D3<500000), H3*4, ""))

      1. Hi Mam.. i have a doubt in excel
        If column A equals to column c then substract column b and colum d...
        Pls tell me how can i do in excel

  5. Hi,
    I'm hoping someone can help me.
    I have a spreadsheet that I enter X into a cell when a student has completed a task. If they completed four tasks (four X's) the are finished. I'm trying to do a formula that will count the X's for a specific row and if it is 4 or more it will put a "C" in another cell or better yet it will put a "C" and also colour the cell. Is this possible??
    Thanks!!

  6. Hi, I have a question.
    I want a formula to the following:

    If column S2=N return the sum of Q2*G2 or if S2=Y return the sum of R2*G2

    1. Hi SandeeBee,

      Here you go:

      =IF(S2="N", Q2+G2, IF(S2="Y", R2+G2, ""))

      1. Thanks you Sandeebee for asking this question.
        And thank you very much Svetlana for your response.
        You both have really help me solved an issue i have been dealing with here.
        Thank you.

  7. Hi Erin,

    Try the following formula for English scores:
    =IF(AND(C2>D2,C2>39),"pass",IF(D2>39,"pass","fail"))

    And if it works as you expected, make a similar one for Math.

    1. Svetlana,
      That's great! Thank you! Is there a way, perhaps, to join both the english and the math? for example, if english is a passing grade and math is a passing grade, they pass the course. if english is a passing grade but math is a failing grade, they fail the course...? If both grades are passing, they pass, but if one or both grades are failing, they fail the course. Is there a way to put this into one formula, or should I calculate the english separately and the math separately and then create a formula from those?

      1. Erin,

        Of course, there is a way:

        =IF(AND(OR(C2>39, D2>39), OR(E2>37, F2>37)), "pass","fail")

        The formula works with the following logic:
        If either column C (english) or D (english retake) is above 39 AND either E (math) or F (math retake) is above 37, it returns "pass", "fail" otherwise.

        My first suggestion was excessively complicated in fact, and there is a more elegant solution for individual subjects, e.g. =IF(OR(C2>39, D2>39),"pass", "fail")

        1. Thank you so very much! This is MUCH appreciated!

  8. Thanks for the awesome website!
    I am trying to compile some data and was wondering if there was a way to do this?
    I have a list of test scores in column C (english) and E (math). In column D english retake) and E (math retake), I have a list of retake scores for those tests. Some of those scores will be blank because some students did not retake a test.
    I need to see who passes and who fails based on taking the highest score for each section. Basically, a student passes if english is above 39 and math is above 37. If one is less, they fail. I can figure a formula for this. However, I need to take into consideration the retake scores. If a retake score is higher than the original score, I need excel to take that number. If the retake score is lower or blank, that number can be ignored. Is this possible?
    Thanks so much!

  9. Thank you Svetlana, you are truly amazing!

  10. HI, I could really use some help making an if/then equation:

    -If Z2 equals 0, then "4"
    -If Z2 equals 1, and Y2 > 0, then "3"
    -If Z2 equals 1, and Y2 = 0, and U2 > 0, then "2"
    -If Z2 equals 1, and Y2 = 0, and U2 = 0, then "1".

    Thanks!

    1. Hi Courtney,

      You can try the following nested IFs:

      =IF(Z2=0, 4, IF(AND(Z2=1, Y2>0), 3, IF(AND(Z2=1, Y2=0, U2>0), 2, IF(AND(Z2=1, Y2=0, U2=0), 1, ""))))

  11. Hi Svetlana,

    I wondered if you could help me with the following problem!

    I have a spreadsheet which shows expenditure, restricted funds and designated funds.

    If the expenditure is greater than the restricted funds, and the restricted funds fall below zero, I want to use the designated funds instead.

    So if expenditure minus restricted funds is less than zero, use the number in the designated column and subtract the difference.

    I hope that makes sense! Sorry if I'm not explaining it very well! Any help is much appreciated.

    Thanks
    Ciara

    1. Hi Ciara,

      Yep, it makes a perfect sense except exactly what difference to subtract:

      >use the number in the designated column and subtract the difference.

      Also, please specify the column letters for expenditure, restricted funds and designated funds.

      1. Hi Svetlana thanks so much for your help

        Column letters
        Expenditure E
        Restricted F
        Designated G

        Thanks!

        1. Ciara,

          Also, please clarify about the difference:

          >use the number in the designated column and subtract the difference.

          The difference between which numbers shall we subtract?

  12. Hi,

    I need some help creating a formula please. I need to create a formula based on the amount of trucks that passes a certain point. The formula should be based on the following :

    1 - 25 trucks = 50 + that figure
    26 - 49 trucks = 100 - that figure
    50 - 99 trucks that figure
    100+ trucks = 100

    Can anyone please assist?

    1. Hi Juan,

      Supposing that "that figure" means the number of trucks and it is in cell A1, you can use the following formula:

      =IF(A1>=100, 100, IF(A1>49, A1, IF(A1>25, 100-A1, IF(AND(A1>0, A1<26), A1+50, ""))))

  13. i would like crate formula for if time crossed more than 16:00:00 if after 4:00 Pm of an activity using if function the text should say " Activity delayed", " Actvity on time"...

    Please create a form for the same

    MM Rajesh

    1. Hello Rajesh,

      You can do this with the following formula:

      =IF($A1>TIMEVALUE("4:00:00 PM"), "Activity delayed", "Actvity on time")

  14. Hi,

    I hope that this does not confuse anyone and I will attempt to explain what I am trying to do as best as I can.

    I currently have the following formula located in cell P6:

    =IF(O6-TODAY()>0,O6-TODAY(),0)

    This is essentially a countdown cell that stops when the date that appears in cell O6 equals Zero days remaining.

    My question relates to attempting to hide the initial Zero that appears in cell P6 without turning the font white and having to use conditional formatting. I am hopeful that there is a way to write the formula so that cell P6 is blank until a date is entered in O6, then, when the date is entered and the countdown reaches Zero, that Zero remains showing.

    Thanks,

    Dan

    1. Hi Dan,

      Your explanation is perfect :) Hopefully, this is what you are looking for:

      =IF(O6-TODAY()>0, O6-TODAY(), IF(O6<>"", 0, ""))

  15. I would like to create a formula that says if a1*.8>10000, return 10000 and if a1*.8<10000, return the value of a1*.8. Can you help??

    1. Hello Brandy,

      =IF(A1*.8>10000, 10000, A1*.8)

      Please pay attention that the formula will also return a1*.8 if a1*.8=10000

  16. I am trying to complete a payroll workbook. Sheet 1 is the summary page with columns reading "Hours Worked", "Overtime", "PTO", etc. When I enter in an employees time on their individual timesheet I would like the column for "Hours Worked" to cap at 80 and reflect the Overtime on it's own. I have the Overtime showing on it's but I can't find the formula to cap the hours worked at 80. Hope this makes sense.. :( Please help!

  17. want calculate the count of students have 60 and above, 65 and above and 70 and above in their Subject 1 and subject 2 both. Having trouble as i need to do this for 2000+ students. Please help

  18. sir i used to manage if condition i excel but according to my new task is to get (in bold ) if the answer of a sum got greater than the required number it should become bold
    ex: the answer we got in a sum is greater then or equal to 100 it should become bold if it is below 100 it should remain as same as it's older font
    any one of you know about this please give me a suggestion

    hoping that it can be resolved as soon as posible

    1. Hello Pavan,

      Select the cell(s) with the sum and create an conditional formatting rule with this formula:
      =$A2>=100 where A2 is the cell with the sum.

      In the "New Formatting rule" window, click the Format button, switch to the Font tab and select Bold under "Format Style".

      1. sorry mam the resolution you have given is not about the subject what i had asked and the subject is for example i'll show you bellow
        23 20 43
        0 46 46
        48 24 72
        15 36 51
        52 25 77
        2 45 47
        216 632 848
        as in the above consider rows indicates the sum of two numbers in the adjacent columns and here the third column indicates the sum or answer and if the answer is greater than the required value it should become bold in the same cell itself with its own value (answer required)

        any one of you know about this please give me a suggestion

        hoping that it can be resolved as soon as posible

        1. Hi Pavan,

          The solution I suggested is exactly for the following case:

          >if the answer is greater than the required value it should become bold in the same cell itself with its own value

          For more information about Excel conditional formatting, please read this tutorial:
          https://www.ablebits.com/office-addins-blog/excel-conditional-formatting-formulas/

          When creating a rule, you simply have to replace A in the formula I suggested with the letter of your SUM column.

          1. thank you mam
            thank you for your suggestion
            i'll be thank full to you

      2. Kindly provide solution to me also

  19. Hi,

    I have cell X6 with a simple calculation of =K6*W6. I am trying to have this same cell calculate =Q6*W6. K6 & Q6 will never have data in them at the same time, so I am just trying to have this cell show the correct result if one of K6 or Q6 has data.

    Any help would be much appreciated!

    Dan

    1. Hi Dan,

      I believe the following formula is what you are looking for:
      =IF(K6<>"", K6*W6, Q6*W6)

      1. Hi Svetlana,

        You're an amazing resource and I thank you for your time!

        This formula worked perfectly, could you tell me how I would hide the Initial Zero value that is located in Cell X6?

        Thanks,

        Dan

        1. Hi Dan,

          Try this one:
          =IF(AND(K6<>"", W6<>""), K6*W6, IF(AND(Q6<>"", W6<>""), Q6*W6, ""))

          It will multiply the specified cells only of both are not empty, otherwise it will return an empty string (blank cell).

          1. Hi Svetlana,

            Thank you!

            Hopefully my final question:

            I have cell AA6 with a formula of =X6-Y6-Z6-U6. Cells Y6 & Z6 do not have a formula associated with them, however, cell X6 has your previous formula =IF(AND(K6"",W6""),K6*W6,IF(AND(Q6"",W6""),Q6*W6,"")) and cell U6 has this formula =IF(AND(K6"",W6"",T6""),K6*W6*T6,IF(AND(Q6"",W6"",T6""),Q6*W6*T6,"")).

            Right now, with no value in any of the cells, cell AA6 shows an error of #VALUE!

            My end goal is to have cell AA6 show a blank cell until any of X6, Y6, Z6 or U6 has a value. X6 will consistently show a value (after it's own formula has been met), but sometimes none of the other cells will show a value and AA6 will simply equal the value in X6. In other instances, sometimes only U6 will have a value, making the end result X6-U6.

            Again, I hope that I have been clear with regards to my question and I appreciate any time that you are able to dedicate to this.

            Thanks,

            Dan

            1. Hi Svetlana,

              I have re-written the formula in cell AA6 to now be this: =IF(X6"",X6-Y6-Z6)-SUM(U6). This looks to work how I want it to, with the exception of hiding the Zero value.

              Thoughts?

              1. Hi Dan,

                If my understanding of your end goal is correct, the following formula should work a treat:

                =IF(X6<>"", X6-Y6-Z6-U6, "")

  20. My formula =CONCATENATE(D20-C20," (",+ROUND(((D20/C20)*100)-100,0),"%)")

    B C D E
    4203 4276 3916 -360 (-8%)

    returns the value -360 (-8%) which is correct. However, I would like the first part of the returned value, in this case -360 to show in brackets and not with a minus sign.

    I don’t know whether I need to nest an IF Function in there, but if so how please?

    Many thanks.

    Tracco

    1. Hi Tracco,

      There is no need for nested IF in this case. You can use the ABS function to return the absolute value of a number, and add another brackets in your CONCATENATE formula:

      =CONCATENATE("(", ABS(D20-C20), ")"," (",+ROUND(((D20/C20)*100)-100,0),"%)")

  21. I want calculate the count of students have 60 and above, 65 and above and 70 and above in their Subject 1 and subject 2 both. Having trouble as i need to do this for 2000+ students. Please help

  22. Hi,

    I have a doubt that if one column for ex. % varies from 0 -100% but i want less than 60%- zero and more than 80% -100 marks and between 60%-80% should get linear score from 0-100 can you tell me??/

  23. HI,

    I need date wise value of the A and b, manual entry date 1 value for A is 10 b is 20, manual entry date 2 value for A is 18 and b is 42, manual entry date 3 value for A is 12 and b is 45,,,,next sheet I need this date wise value for A and B

  24. I have a spreadsheet in which I moderate marks assigned to tests. The if formula I'd like to use should be able to do the following. If say cell C1 is the original mark and B1 is the revised moderated mark, I need excel to give me a mark in A1. If the revised mark in B1 differs more than 5 marks up or down from the orignal it should give me the revised mark if the difference is less than 5 marks it need to give me the original mark. I have entered this formula { =IF(C39=5>C40;C39; IF(C39=5<C40;C39;C40)) and also tried this one =IF((OR(G40=5G39));G39;G40) } but Excel doesn't seem to give me the false value when it should be false and only gives me the true values each time. What formula should I use or how should I change the present one I have?

    Thank you.

    1. Just to clarify it more, I changed the cell values in this reply.

      I have a spreadsheet in which I moderate marks assigned to tests. The if formula I'd like to use should be able to do the following. If say cell C1 is the original mark and B1 is the revised moderated mark, I need excel to give me a mark in A1. If the revised mark in B1 differs more than 5 marks up or down from the orignal it should give me the revised mark if the difference is less than 5 marks it need to give me the original mark. I have entered this formula { =IF(C2=5>C3;C2; IF(C2=5C2;C3=5<C2));C2;C3) } but Excel doesn't seem to give me the false value when it should be false and only gives me the true values each time. What formula should I use or how should I change the present one I have?

  25. pls i need correction trying using the less than(<) or equal to (=) sign to calculate a student result using the the following grading system format
    70 and above A 4.00
    60-69 B 3.00
    50-59 BC 2.5
    40-49 BC 2.5
    30-39 CD 1.0
    20-29 F 0.1

    =IF(A1<=100,"A","4.00" IF(A2<=69,"B","3.00" IF(A3<=59,"BC","2.5" IF(A4<=49,"C","2.00" IF(A5<=39,"CD","1.0"IF(A6<=29,"F","0.1"))))))

  26. helo i want to ask that i have four value in four cells like as in a1,b1,c1,d1 , now i want to apply a farmula sepratly two value , and two other value so tell me if condition???

  27. Hi
    I would like to set up a single cell to display the highest value in a column
    Any advice would be much appreciated thank you.

    1. Hi Jackie,

      You can use the MAX function, for example =MAX(A:A)
      If a cell with the formula is in the same column, then you need to specify a range, e.g. =MAX(A1:A100)

  28. Hi
    i have a doubt in ms excel can anyone help me . In a column there are different texts
    in that texts there are some same texts. i want to change that same texts as per the end of texts 01,02,03........ like this
    eg play
    play
    pray
    power
    power

    from these columns i want to change as per the end of text 01,02,03............... like this

  29. Hi,

    I am facing a challenge in excel. The problem is I have 5 different values which are an outcome of multiplication of 2 variables. So these 5 different values changes as anyone changes the input variables. Now with those 5 values, I have 5 ratings associated A, B, C, D and E. A should always come next to highest of the 5 values and E should be with the lowest of 5 values. I would want the ratings to reflect it. How can this be done?

  30. Hello....In one column i have 3 text values, how can i get the total of these 3 values

    1. Hi Arshi,

      I am not sure what exactly the total of 3 text values is. If, for example you have the following values: A1="a", A2="b" and A3="c", you can concatenate them into "abc" by using the concatenation operator =A1&A2&A3

      If you are looking for something different, please clarify.

  31. need help with multiple arguments and conditional formatting
    I have 03 fields (Cell 1, 2 & 3) (data validated) with a list.
    I want this Cel1 3 to be highlighted in red

    if Cell 1 is "None" and
    Cell 2 is "Non Staff" and
    Cell 3 is ">12 Months"

    The I want Cell 3 to be highlighted in Red

    1. You don't need the IF function in Excel conditional formatting. Just use a usual AND as follows:
      =AND($A$1="None", $A$2="Non Staff", $A$3=">12 Months")

  32. I have used a drop down box for column I. Each cell will have a different value based on the drop down box. I need column J to perform a different formula based on what was selected in the drop down box.

    If I8 = "bale", Result J8=H8
    If I8 = "cwt feed", Result J8=sum(C8*H8)/100
    If I8 = "hd/day", Result J8=sum(G8*H8*3)
    If I8 = "blk", Result J8=H8
    If I8 = "cwt mixing", Result J8=sum(G8*H8/100
    If I8 = "lbs", Result J8=sum(G8*H8)/100

    Thank you for your help

  33. Hi,
    I need a help in excel. Suppose in column A, user will be entering the date and in B column month will get updated automatically. formula would be in B column. Condition If cell in column A is blank, then it will return "Text", else it will return the month of the date entered in A column.

    Earliest reply would be highly appreciated..

    1. Hi!

      Try this one:

      =IF(A1="", "text", MONTH(A1))

  34. Hi, I am looking for a formula that will calculate the number of months for a specific time frame within the financial year, 01/04/2015-31/03/2016, and subsequent financial years for the next five years. The specific time frame can change based on two dates, start date and end date so the formula must be able to figure out how many months are specifically within that financial year. So for example cell B3 contains the start date and C3 contains the end date. B4 to B8 would contain the start of each financial year (01/04/2015 and so on). And A5 to A9 would contain the end of each financial year. The answer/formula would be present in cell C4 to C8. Any help would be much appreciated

  35. I need a formula that puts a value in Column C, based on the data in Column A AND B.

    Stock #LOA LOCATION
    1 26 C53
    2 46 D3
    3 30 C23

    Location D
    31-50' 0.5
    51-89' 0.55

    Location C
    21-25' 0.15
    25-30' 0.2

    So for Stock # 1, I would need 0.20 in column C.

    1. =IF(ISNUMBER(SEARCH("C",G2)),IF(AND(B2>=1,B2=21,B2=26,B2=31,B2=36,B2<=40),0.35))))))

      This works, but only if it's Location C... not sure if I can have a complete different set for Location D in the same formula?

  36. I need a formula that puts a value in Column C, based on the data in Column A AND B.

    Stock #LOA LOCATION
    26 C53
    46 D3
    30 C23

    Location D
    31-50' 0.5
    51-89' 0.55

    Location C
    21-25' 0.15
    25-30' 0.2

    So

  37. Hi Svetlana !!
    I am trying to highlight a cell if the part of a any text is repeated anywhere in the sheet. Like if I have a data like John 64, if the text 64 is repeated anywhere in the sheet that cell gets highlighted. Is it possible ???

  38. how to extract Dates from text? Example mentioned below:
    Support-to -TARR-APP-1/01/15-31/12/15.
    There are two Dates mentioned in one text string then how to extract both dates in different columns?

  39. Hello

    I have a due date column that is calculated at 30 days after a transfer date column. However, when I put the formula "=A1+30" in the due date column it returns a date of 01/05/1900 for rows that don't have a date entered in the transfer date column. How do I get this to stay blank unless there's a date entered in the transfer column?

    Thank you.

    1. Hi Jordan,

      If my understanding is correct, column A is the Transfer column. If so, you can use the following formula:

      =IF(A1<>"", A1+30, "")

  40. I am trying to make an if then that if a cell from another sheet and cell has a time on it i want it printed and if there is no time then to have a blank cell is this possible and if so how.

  41. Hi I would like to get help with nested if
    I would like to calculate performance of employee tasks based on 2 tasks types.conditions.
    1. if task type is "a" then if task finsih in 1 day the grade is "100" if 2 days then the grade "80" and so forth.
    ELSE if task type is = "b" then if 2 days then is "100" and if 3 then "80" and so forth.

    the first half of the formula works, but not the second.

    =IF(A1="task a",IF(B2<0,"0",IF(B2<=2,"100",IF(B2<=4,"80",IF(B26,"50",IF(A1="task b",IF(B2<0,"0",IF(B2<=3,"100",IF(B2<=5,"80",IF(B27,"50"))))))))))))

  42. I'm trying to run a formula for the following but I know I'm missing something. If you could help I would greatly appreciate it.

    In the same cell I would like to do the following arguments, I'm just not sure how to place them.

    IF H214 & D2>100 "PUSH OUT"
    "OK" for everything else that does not fall within this criteria

    Thanks for your help,
    Mindie

  43. Hi there

    Looking for help in writing efficient formula to calculate Forecast attainment in Col E. I have the Reps Target in Col D and I have a column for each of the 4 weeks of the month (Cols F-I). I'd like to use formula to calculate the Forecast attainment of the most recent entered week so probably using if I2 is blank, use H2, if H2 Blank use G2, if G2 blank use F2
    Much appreciated

  44. Hi,
    I am working on Payroll worksheet. In row 1 has 28 consecutive days. In row 2 there are 28 Option buttons under each date and the Cell Link to these button is C1( Form Control as I cannot use macros for security reasons ). So C1 will have any number between 1 and 28 representing button I selected.

    I will use the option buttons to select only 1 date at a time so later I can adjust, using a scroll bar, what date I did overtime and what time IN and OUT. One can only choose 1 option button at a time (i.e.1 date at a time).

    I can use IF(C1=1,A1,IF(C1=2,B1,IF(C1=3,C1,IF(C1=4,D1.....etc, which will be too long as I am planning to edit the worksheet later on for multiple 4 week roasters.

    Can you please help me with a shorter formula to pick the date in Row1 depending on the value in C1.That is if C1=1=date1, 2= date2 etc.

    Thanks Sam

    1. Hi,
      Sorry for wasting any of your time.
      Was thinking of IF function all the time when the formula was much easier
      =A1+(C1-1)
      Thanks and Apologies
      Sam

  45. Hello,
    I had a follow up question in posting 59 that I'm hoping you can answer for me. Thank you.

    Jordan

    1. Hi Jordan,

      If my understanding is correct, you want the formula to return those texts only if there is some value in the "transfer date" column.

      If so, then you need to add one more condition to the formula. For example, if the transfer column is D, then you add D2<>"" which means D2 is not empty. The entire formula may look like this:

      =IF(C2<>"", "complete", IF(AND(A2<TODAY(), B2="", C2="", D2<>""), "invoice required", IF(AND(A2>=TODAY(), B2="", C2="", D2<>""), "upcoming", "")))

      Where A2 is the "Due date" column, B2 is "Sold date", C2 is "Invoice number" and D2 is "Transfer date".

  46. I am putting together a questionnaire in Excel which is meant to "test" the level of users. The questionnaire automatically provides scores for the users which I've translated into percentages. I need a formula which would give me tell the user what his score (level) is ranging from -1 to 3. Yet, I cannot use MAX because if -for example, the user has 100% in levels 1 and 2, I need excel to recognize that the user is level 3. (See table below). Is there a formula that can help out here?

    Score Points Percentage
    -1 0 0%
    0 5 19%
    1 2 50%
    2 11 92%
    3 9 100%

  47. I have struggled for 2-days with no avail.
    If A1>87508750<=12000,(A2=1)*("8000"))

    Your assistance would be greatly appreciated

    1. Hi!

      Sorry, I am now sure I can follow you. This condition (A1 is greater than 87508750 and less than or equal to 12000) can never be met, probably you mean the opposite?

      Anyway, I think it should be something like this:

      =IF(AND(condition1, condition2), value_if_true, falue_if_false)

      If you can clarify the conditions and values to return, I believe we will get it to work.

  48. I tried using the following, but it returns the value if the columns don't match; I just want the missing data piece to return:
    =IF(F10:F14=$E$10:E16," ",E10:$E$16)

  49. I am needing help with what is probably a very simple formula.
    I need a formula that will return missing days of week (ex trying to show days off).
    Example, if I had the days below, what formula could be used to return Sun & Tue
    Mon
    Wed
    Thu
    Fri
    Sat

  50. I am giving students a baseline test and a post-test, seeing the difference within the 2. After the baseline test we come up with a growth target. It's the percentage increase we would like to see, and there are 4 tiers.
    So if the student scores:

    6%-18% should increase 40%
    19%-31% should increase 30%
    32%-45% should increase 20%
    46%-60% should increase 10%

    I would want a formula to take a baseline score between 6-60 and know what percent increase it should have.
    I tried =IF(B10>=46,"10",IF(B10>=32,"20",IF(B10>=19,"30","40")))
    It comes up Err:509

    Seems like the pieces are there, but still not working.
    Thanks in advance.

    1. Hi Tim,

      The formula looks correct to me and works just fine in my Excel, except that it returns text strings rather than numbers because you enclosed them in double quotes. If this is okay for you, then it's okay : )

      A quick search in google revealed that error 509 occurs when referencing cells in external Excel books. Is this the case?

      1. The formula doesn't work in Excel online, OpenOffice, or Google Sheets.

        Here is the formula: (my reference cell is cell B10)

        =IF(IF(B10>=46,10,(B10>=32,20,IF(B10>=19,30,40))))

        I did manage to use another function for a yes/no IF function:

        =IF(SUM(D10-B10)>=C10,"YES","NO")

        1. Tim,

          This IF formula is different from what you originally posted and incorrect. Please notice double IF in the beginning and missing IF before (B10>=32,...

          Copying/pasting makes a mess of formulas sometimes. Anyway, I've just tried the fixed formula in Excel Online and it worked fine:

          =IF(B10>=46, 10, IF(B10>=32,20, IF(B10>=19,30,40)))

      2. Thank you for the speedy response. I got rid of the " and it now says Err: 509. I am running openoffice instead of Office. Would that be an issue? Ideally, I would like to share with my fellow teachers in Google Drive. Gonna see if it works in that program. Are there spaces at all in the formula?

        1. Sorry, I have no experience with OpenOffice and very little with google sheets, and have no idea how formulas work there.

          1. I figured it out, Here is the formula:

            =IF(C2<=18,40,IF(C2<=31,30,IF(C2<=45,20,10)))

            It works for Excel, OpenOffice, Google Sheets.

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