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 8. Total comments: 2544

  1. Hi Alexander,
    I have the following table:

    No. on St. Street Name
    1 A2 (European Road)
    2 A2 (European Road)
    3 A2 (European Road)
    1 E5 (National Road)
    2 E5 (National Road)
    4 A2 (European Road)
    5 A2 (European Road)

    I need a formula to number the points located on the same road (column A), in ascending order using the B column (Street name, which is text)

    I've tried to use =IF(B2B1,1,A1+1) and the result is ok if the streets are organized well (ordered by name) but for the last 2 cells, the values will be 1,2 and not 4,5.

    Can you help me with the correct one, please?
    Thanks in advance!

    2. Solved with =COUNTIF($A$2:A2,A2)

  2. Hi,

    I am trying to build an if statement which will tell me to either "Strong bet", "bet" or "fade" the capper. Conditions are ROI 20%+ for "Strong bet", ROI 10-19% for "bet". Less than 10% or sample size less than "10" from the capper is a "fade"

    thanks

  3. please help me on the formula to use here
    The college wishes to analyze the applicants’ data in order to find those applicants who qualify for admission to pursue a course in IT. Successful candidates MUST meet the following minimum requirements;
    • Must have scored a mean of 45 marks and above;
    • Must have scored 60 marks and above in Mathematics;
    • Must have scored 50 marks and above in either English or Kiswahili.

    Enter an appropriate function in cell I4 and copy it to other cells to determine whether the student qualifies for admission. If the student qualifies, the function should display ‘Successful’. Otherwise it should display ‘Unsuccessful’.

  4. Hi Alex,

    Trying to do something with AVERAGE but got stuck, hope you have some ideas!
    Let's say we count visitors in shop for last 10 days. We have 90 visitors per day for 9 days but on one particulate day we had 3x more customers then usual. I would be happy if I could exclude this day from average count ("paranormal day" or something like that). So we have days in columns A to J, row 1. In row 2, we have our daily count. K2 cell is average count (9*90+1*270). That cell with 270 value should be excluded from average count.

    Hope this above makes sense!

    Thanks
    Ivan

    1. Hello!
      To calculate the mean without anomalies, you can use the standard deviation. Only values are taken into the calculation that deviates from the average value by no more than the value of the standard deviation.
      In Excel2019 and below, enter this formula as an array formula.

      =AVERAGE(IF((A1:A10>=AVERAGE(A1:A10)-VARP(A1:A10)^0.5)*(A1:A10<=AVERAGE(A1:A10)+VARP(A1:A10)^0.5),A1:A10))

      You can also try the TRIMMEAN function:

      =TRIMMEAN(A1:A10,0.2)

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

  5. How many nested if statements can be used in Excel 2013

  6. I two columns, one has cities and the other column some cells empty,

    i will create 3rd column if the second column is empty get the data from the first column and if not empty get the data from the second column.

    Thanks

  7. unfortunately Using IF & AND only work for 2 cells at a time but if we have more than 2 cells/column to compare then it would not work in excel.

  8. =IF(G10=1828,"1.770",IF(G10>2558,"1.812")))

    if > 2558 result 1.812 not working

      1. =IF(G10=1828,"1.770",IF(G10>2558,"1.812")))

        Dear Alex
        Now My G10 Value is > 2558 but the result is 1.770 (wrong result)
        By formula 1.812
        First two condition working ,

  9. do you have an email to send you the attachment as well?

  10. This article was very helpful and the IF nested within an IF formula is working with one exception. I've created the formula (below) for a table using headers
    =IF([@Cart]>=120,"5",IF([@Cart]>=100,"4",IF([@Cart]>=80,"3",IF([@Cart]>=1,"2","1"))))

    On a couple of lines [@Cart] is "0" and displays as an empty cell. Excel is returning "5" instead of "1". If I am understanding the formula correctly, my column [@CartPick] should display "1"

    e.g.;
    TM | RMAscn | Cart | DPJ | OP | FL | LTL | Problem | CartPick
    Jarod 53.17 24.13 5

    I appreciate any help you can provide!

    - Jed

    2. Sorry, it appears my comment was formated in a way that may have been hard to read after I submitted. Here is a better understanding of my table:
      e.g.;
      [@TM] | [@RMAscn] | [@Cart] | [@DPJ] | [@OP] | [@FL] | [@LTL] | [@Problem] | [@CartPick]

      [@TM] Jarod
      [@RMAscn] ""
      [@Cart] ""
      [@DPJ] 53.17
      [@OP] 24.13
      [@FL] ""
      [@Problem] ""
      [@CartPick] (this is the cell that contains the formula) 5

  11. I am Looking For Formula to to see the one date is greater than other date for multiple columns

    For Example we have multiple dates in different column for example
    Seq No. A B C D E F G

    1 26-Feb-2021 9-Mar-2021 9-Mar-2021 9-Mar-2021 9-Mar-2021 9-Mar-2021 9-Mar-2021

    Now i need to check Date for B>A C>B D>C E>D F>E G>F

    Please guide me how to use the formula to check above condition

    Thank you in Advance

    1. Hello!
      I don't really understand what kind of result you want to get. But dates can be compared in the same way as regular numbers.

      1. Hi,

        This is my formula "=IF(J7=16,AND(V7=1)*56,0)+OR(V7=2)*108+OR(V7=3)*159" & i wanted to continue it with "J7=19" then "J7=22" (parallelly changing the values of "V7") & so on..

        Plz help, if it is not possible with "IF" formula than suggest other formula to be used.

  12. Product Code Sales currency Exchange rate Product code Sales in Euro
    E0032M 9,000.00 Euro Euro 90 E0032M
    E0032M 7,000.00 Dollor Dollor 70 E0034M
    E0032M 10,000.00 INR INR 100
    E0032M 30,000.00 Pound Pound 30
    E0032M 14,000.00 Dollor
    E0032M 20,000.00 INR
    E0034M 30,000.00 Pound
    E0034M 14,000.00 Dollor
    E0034M 20,000.00 INR

    need total sales in Euro after conversion in a single formula

  13. HELP! its true, if you don't use it, you'll lose it. I'm trying to say if J4-D4 is >9.5 then "Y" and if not then "N". It sounds simple but I'm so frustrated. Can anyone help me please?

    1. Hahahaha ! Sir the answer is hidden in your question itself.

  14. I'm trying to find the gender and housing status (Single or paired) in a list of animals, (this could be my data information: 7-01/001-002F or 7-001/001-002M or 7-01/001F or 7-01/001M).
    I've combined the Housing and gender in to one cell already, then I use the "IF" "OR" formulas below to find them but I would like to know if I can combine formula A with B in one cell (currently I use each formula in separate cells)
    Formula A =IF(OR(U:U="7-01/001-002F"),"1PF","1PM")
    Formula B =IF(OR(U:U="7-01/001F"),"1SF","1SM")

    1. Hi!
      Your formulas A and B cannot be combined as they contradict each other.
      The value "7-01/001F" according to formula A returns ”1PM”, and according to formula B - ”1SF”.

  15. Please advise the formula to return the value, lets say if A1*A2 is higher than 100, return value as 100 and if A1*A2 is less than 50 return value as 50 if not A1*A2.

  16. I need to find an excel formula that will take a number in a cell (example "a1") and rounds up to the nearest hundredth. It's for product increases and the number has to be even. Like $3.28, not $3.27. If I have the number $3.333 I need it to round up to $3.34. Can this be done in Excel?

    1. Hello!
      You can learn more about rounding numbers in Excel in this article on our blog.
      There you can find a formula like this:

      =ROUNDUP(A2,2)

      This should solve your task.

  17. =IF((AND(I3="No",N3="Good IMU")),"Action - Feeds",""),IF((AND(I3="Yes",N3="Low IMU")),"Action - IMU")

    Can someone please help - I need 2 different return values based on 2 different and statements. help!! I can get one or the other, but I don't know how to combine into one formula!

    1. Hello!
      You can learn more about nested IF and multiple conditions in a single formula in this article on our blog.

      =IF((AND(I3="No",N3="Good IMU")),"Action - Feeds",IF((AND(I3="Yes",N3="Low IMU")),"Action - IMU",""))

  18. Hi, is it possible to generate percentage on excel with multiple arguments based on the data populated each day?

  19. I need if formula with logic if A1 column is blank data pick from A2 column
    Thanks in advance for supporting

  20. hi, is it possible to put number 1 or 2 in c1 while i used this formula in the same cell =IF((C1)=1,"I",IF((C1)=2,"II",IF((C1)=3,"III","blank")))

    i just want to put number then its automatically change or convert to text which i want it, Help me pls

      1. thanks

  21. Hi,

    I am looking for an if, else function formula in excel that can identify who will "win", or "loss" in the sample situation below?
    Score
    Player #1 - 5
    Player #2 - 1
    Player #3 - 8

    In the result, Player #3 should be "win", and Player #1 and #2 should be "loss" . Is there a possible if, else formula for this? Thank you.

    1. I'm trying this formula (where H is the column in excel) ,data in H3 is 5, H4 is 1, H5 is 8
      but the result is not correct , it resulted to win, where it should be loss.

      =IF(H3>H4,"win",IF(H3>H5,"win","loss"))

      Thank you, appreciate your reply.

  22. I have a table sheet (CMT) with formula. it was created to monitor patient clinic attendant. I want to create other sheets that will extract data from my original table sheet (CMT). For example, I want to have a sheet that will show list of patient due for clinic appointment for the present month base on the table formatting.
    What excel techniques will I use to create such automated sheets from my table sheet?

  23. (+/-) 6 - 10% (+/-) 1 - 5% 100%
    1,000 1,500 2,000
    how to create formula that should result into this
    90% 1,000.00
    91% 1,000.00
    92% 1,000.00
    93% 1,000.00
    94% 1,000.00
    95% 1,500.00
    96% 1,500.00
    97% 1,500.00
    98% 1,500.00
    99% 1,500.00
    100% 2,000.00
    101% 1,500.00
    102% 1,500.00
    103% 1,500.00
    104% 1,500.00
    105% 1,500.00
    106% 1,000.00
    107% 1,000.00
    108% 1,000.00
    109% 1,000.00
    110% 1,000.00

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

      =IF(ABS(A1-100%)>5%,1000,IF(A1=100%,2000,1500))

  24. Hi,
    could some one help me to create a formula - if my loss is less than 2%, 100 marks, if my loss is between 2.1 to 2.5, 80 marks, if my loss is more than 2.4 - 0 marks

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

  25. hello, 1st I'd like to say I found this site very interesting; so great that you're willing to help !

    here's my question.. I have 2 lists in a column, for example, 8,6,2,-2,-4 and the second list 5,3,-2,-4,-6. now if I multiply by rows I get 40,18,-4,8,24. The issue here is that the list a ranking of points in game for 5 people. based on the 3rd column, the dude with -4 and -6 is ranked 2nd while he's actually 5th. one way to get around this is to multiply by (-1) if both values are negative. I wanted to know if there is a IF statement I can use such that (if a2<0 and b2<0 then multiply by (-1), else just multiply as usual) . I'm aware that another way to get around it is to just add the values then both negative values would remain negative; I just wanted to know if I could multiply by (-1) . that's it ! thanks for reading this. hope to hear from you this week.

  26. There will be a given data of a person with their Height, Weight and Mid upper arm circumferance , from that we have to derive whether the child is Severe Accute Malnutrition or Moderate Accute Malnutrition or Normal or Obese. What will be the formula in Excel . Length (cm) "SAM
    < –3""MAM ≥ –3 to +2 to ≤ +3" "Obesity> +3"
    Weight (kg)
    45 0–1.8 0–1.9 1.9 2.0–3.0 > 3.3
    46 0-1.9 2.0–2.1 2.2–3.1 3.2–3.5 > 3.5
    47 0–2.0 2.1–2.2 2.3–3.3 3.4–3.7 > 3.7
    48 0–2.2 2.3–2.4 2.5–3.6 3.7–3.9 > 3.9
    49 0–2.3 2.4–2.5 2.6–3.8 3.9–4.2 > 4.2

    1. Hi!
      The result you want to get me is incomprehensible. But recommendations from this article will be useful for your formula.

  27. I need a formula that can look at value in B column (there will be up to 10 different values in this column)
    and return a different result in the next cell of column C IE: cell b1 = text then c1 = text2

  28. I want to put the "statements in column 2" when I select "items in column 1" from menu bar in another sheet.

    Column 1 Column 2
    item1 sample for statement1
    item2 sample for statement2

    I tried pivot table, but it provides statement upto some extent, I need to display the whole statement in column 2, no matter how bigger it is. Please help!

  29. I had read all the examples, and maybe I miss it but I can't figure it out a formula:

    If the value of cell A1 is equal or more than 10% the amount will be $500.00 but if the same cell is less than $500.00 it will be 10% less. Can you please help me? I do appreciated

  30. Hello ,

    Could you please help me on the below formula? I'm trying to define the below rule:
    The formula that I have defined is this =IF(K2>=I2,"Achieved",IF(K2<I2,"Not Achieved"))
    but I need to add another condition so if the method of meeting colum2 is Phone Call whatever value it might have not to be calculated as Achieved.

    2 Meeting 1 Not Achieved
    1 Meeting 0 Not Achieved
    6 Meeting 6 Achieved
    6 Meeting 6 Achieved
    6 Meeting 2 Not Achieved
    6 Meeting Not Achieved
    1 Meeting Not Achieved
    0 Phone Call Achieved

    1. Hello!
      If I understood correctly, add the condition to the formula

      =IF(J2="Phone Call","Achieved",IF(K2>=I2,"Achieved",IF(K2<I2,"Not Achieved")))

  31. Hello,
    I'm trying to come up with a formula that will give me the same return as the following, but include more room numbers and more sections of the facility:
    for example this formula in column A returns "North" or blank "=IF(AND(B14>209,B14<227),"North","")"

    What I would like is to have the following:

    If the value in Column B = between 202-208, and 232, Column A = North East
    If the value in Column B = between 210-226, Column A = North
    If the value in Column B = between 234-242, and 258-274, Column A = East
    If the value in Column B = between 244-256, Column A = West
    If the value in Column B = between 280-294, Column A = South

    I don't need it to return blank.

    I thank you ever so much for any help you can offer!

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

  32. Hello guys
    I have a problem that i cant solve for days. Can't find an answer on web. So here is what i wanna do;
    There are almost 250 pieces of products i have and all have names and codes
    (e.g name:15cm silver furn. leg and code: ob0001)
    I want to enter the name of one products in a random cell and excel will automatically enters the code in the next cell.

    Its all but i cannot make it work. im about to smash my computer (:

    Is there a solution? Please help me!
    thanks in advance

    1. Hello!
      If the product name and code are in a separate table, you can use the VLOOKUP function to search that table. Your search for the name gets the corresponding code. Or vice versa.

      1. Thank you very much.. Im gonna try this function.

  33. sir,

    pls help

    IF total of B4=1 then, appear the total of column D7 ( whatever it is.....)

    Thanks,

  34. I'm trying to create a nested IF function that includes an AND condition but get an error that says there are too many arguments. Is there any way to create a nested IF that includes an AND?

    Here are the three conditions/formulas I would like to combine to create a single output in one Status field (the word "blank" is just a placeholder so that I can see the results):

    if F2 is not blank, then return "Done":
    =IF(F2"","Done", "blank")

    if F2 is blank AND E2 is greater than 10 days, then return "2nd call":
    =IF((AND(F2="", TODAY()-E2>10)), "2nd Call", "blank")

    if D2 is blank, then return "1st call":
    =IF(D2="", "1st Call", "blank")

    Here is what I think the full formula should look like:

    =IF(F2"","Done", "blank", =IF((AND(F2="", TODAY()-E2>10)), "2nd Call", "blank", IF(D2="", "1st Call", "blank")))

    Here is a screenshot of sample data:

    https://drive.google.com/file/d/1UtB_MMrfM25MyDX-NFfqw_Zroxopdcj1/view?usp=sharing

    Thank you so much for any assistance you can provide. I have learned so much from your website. I really appreciate the hard work you've put into making these formulas easier to understand.

    1. Hello!
      Perhaps this formula will work for you

      =IF(F2<>"","Done", IF((AND(F2="", TODAY()-E2>10)), "2nd Call", IF(D2="", "1st Call", "blank")))

      1. Ah, I see what I did wrong by including the "blank" output. That does help. Thank you so much for your assistance.

  35. I need help calculating an Average for multiple subjects in one row
    e.g.
    Student Maths Science Geo

    Peter 80 80% 0 0% 78 78%
    John 50 50% 78 78% 0 0%
    Sue 60 60% 0 0% 80 80%

  36. I am trying to add an SumIF statement to my sum.

    If the Sum of A1:A4, is greater than 100, only display 100.

  37. Hello,

    I hope someone can help me with this.
    If cell C is not blank, result should be "Done"
    If the cell is blank and cell B and cell A result should be the following:
    If 15 Day Difference, "Too early"
    If 30 Day Difference, " Needed"
    If 60 Day, "Cancelled"

    Here is the data:
    Cell C:

    Policy Effective

    02/16/2021
    02/11/2021
    02/11/2021
    01/22/2021

    Cell A & B
    Cell A Cell B
    02/18/2021 02/16/2021
    02/17/2021 02/04/2021
    02/12/2021 02/06/2021
    02/12/2021 02/06/2021
    01/22/2021 01/18/2021
    03/01/2021 02/27/2021

    Thank you so much

    1. Hello!
      Please check the formula below, it should work for you:

      =IF(C1<>"","Done",IF(B1-A1<=15,"Too early",IF(B1-A1<=30,"Needed","Cancelled")))

  38. Hi,

    many thanks for the detailed tutorial here. love it!

    i have a1 linked to a drop down list from a separate worksheet.

    i want my m1 to reflect a price based on the item selected from the list in a1 (list of prices also from a table in separate worksheet)

    eg. if a1 is selected as apple, I want this to be reflected in m1. if it is selected as a orange, i want it to be reflected as such in m1.

    how would i do this please?

    many thanks in advance!

  39. Hello,

    I'd like to create a formula for the following, but I don't know how to do it.

    If status in column F is "Draft", then use the File Path in Column I to enter the file name in that folder into Column J called 'File Name'.

    Can this be done with a formula? Or is VB needed?

    Any help is greatly appreciated.

    1. Just to give more information on this. The excel worksheet lists information to track a large list of documents. Each document is kept within its own folder. The worksheet lists a lot of data for those documents including the document's status and its File Path.

      Here is the issue: Even though there is only one document in that folder, the file name of the document changes daily. So instead of updating the file names manually (which is tedious and time consuming), I'd like to use the Status column and the File Path column to automatically update the file names of those documents.

  40. Hi there,
    Im trying to return Status (to return Not Started/ Ongoing/ Done/Delayed) of a Task based on starting date, due date and today. I am stuck on adding condition for "Delayed", when Due date has been postponed.

    Today = L2
    Start Date = E3
    Due Date = F3

    I have tried using the OR formula as well, but I cannot add a 4th condition for delayed:
    =if((or(F3>L2="Delayed",E3&F3="",F3<L2)),"Done","Ongoing")

    Could you kindly assist on this question? Thank you in advance!

    1. Hi Diana,

      To check multiple conditions and output different results depending on which condition is TRUE, use nested IF functions:
      =IF(F3>L2, "Delayed", IF(AND(E3="",F3=""), "Done", IF(F3<L2,"Ongoing", "")))

  41. How to Calculate if there are 5 parties having different price how to know the which of the party has less price showing the party name

  42. I'm having a tough time trying to write a formula where 2 conditions must be true, then using the value from a third formula.

    Sheet -1 (google quiz)
    Score Name Student # Class #
    10 Jim 5 6/1
    6 John 5 6/2
    6 Greg 5 6/3
    9 Tom 5 6/4

    So take the above table for example. That would be the sheet that's linked to a google form and it populates as the students take the quiz. No, I have 4 different classes, 1 sheet for each class. I'm having a hard time writing a formula where say, "condition 1 = 6/2", "Condition 2 = 1 (the student number)", and if both of those conditions are met, then the "score" is automatically populated on the roster sheet.

      1. Okay, that helps. However, I'm still having difficulty getting the score as the value.

        So I have multiple sheets that are pulling data from 1 sheet where all my google forms are populating. The google form has a cell "score (1-20)", a cell with the "student numbers (1-40)" and a cell for the "class". However, I have a sheet for each class (6/1 - 6/4), with 40+ students in each. The =AND(logical_expression1, [logical_expression2, ...]) formula highlights those cells, but I can't get it to pull the actual score (from the score cell) when both conditions are met.
        =IF(AND(F1:F100="6/2",E1:E100="1") F1:F150 being the class, E1:E150 being the students number, because each class has students numbered 1-##, there is overlap, therefore I need to have both class and student number to be true, if both of those conditions are met, then I need it to pull the score (1-20) from that specific row.

        1. If you refer to multiple cells at once in your formula (F1:F100 rather than F1), you should also wrap it in the ARRAYFORMULA.

          If you're still not sure how to make it work, please consider creating a sample spreadsheet with the following tabs: (1) a couple of sheets with 10-20 lines from your forms (if there's confidential info, replace it with some data but keep the format), (2) an example of the result you want to get.

          Share this spreadsheet with us: support@apps4gs.com, I'll look into it.

          Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

  43. I'd like to look at 2 cells (J9 and BH9) to see if there is anything in either of them. If something in either cell then show the value in AN9. If nothing is in J9 or BH9, then the IF statement would be skipped.
    I tried several variations of this...
    IF(OR(J9=””,BH9=””),””,AN9)

    1. I figured it out after much trial and error.
      IF(AND(OR(J9=""), OR(BH9="")),"",AN9)
      I'm grateful that I found this website.

  44. I want to use the countif statement with two conditions, how do I do this?

  45. Hi,
    Im trying to set a condition where if it says the word "Conns" the answer will be "600" number and if it says the word "MDO" show "570".
    This is one of the formulas I have try so far:
    =IF((D587=Conns,MDO),"600","570")

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

      =IF(D587="Conns",600,IF(D587="MDO",570,""))

      1. I was using "" wrong, but thank you for your help.

  46. Hello,
    Is there a way to use an IF or IFS formula if you have more than 127 conditions?

    1. Hi!
      You see, there is a limit in Excel that defines how deeply the IF function can be nested. For example, Excel 2007 allows 7 levels of nesting only. Starting from Excel 2010 you may nest up to 64 Ifs. Office 365 increased this limit even more - to 127.

  47. I need help with formula.

    Sheet 1
    Prdtucts Name Buy/sell Qty
    Bed BedBuy 100
    Chair ChairBuy 200
    Table TableBuy 50
    Sofa SofaBuy 300
    Park bench Park benchBuy 440
    Coeffe Tabel Coeffe TabelBuy 602
    Bed BedSell 20
    Chair ChairSell 30
    Table TableSell 12
    Sofa SofaSell 120
    Park bench Park benchSell 40
    Coeffe Tabel Coeffe TabelSell 205
    Table TableSell 30
    Park bench Park benchSell 250
    Chair ChairSell 105
    Tabel TableBuy 45

    I want a formula to find out the stock of each item in different sheet.
    Thanks,
    Sandip Dhakal

  48. Hello, would you be able to help me with this.

    For example I have your conditions (which consist of two different statements) and 4 different answers for each.

    For example:

    I have
    A1=no, B1=short
    A1=no, B1=long
    A1=yes, B1=short
    A1=yes, B1=short

    Both conditions must be met:

    If No/short - formula (E1-D1)*C1
    If no/long - formula (D1-E1)*C1
    If yes/short (D1-G1)*C1
    if yes/long (G1-D1)*C1

    All this needs to go to one cell, because there are 4 outcomes possible. How it needs to be written in one cell?

    I am confused.

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

      =CHOOSE((A1="no")*1+(A1="yes")*2+(B1="short")*3+(B1="long")*5, "","","",(E1-D1)*C1,(D1-G1)*C1,(D1-E1)*C1,(G1-D1)*C1)

      You can learn more about CHOOSE function in Excel in this article on our blog.
      I hope I answered your question.

    2. correction

      A1=no, B1=short
      A1=no, B1=long
      A1=yes, B1=short
      A1=yes, B1=long

  49. Hello,

    Looking at the examples of IF AND and OR on your page I'm trying to create a formula that displays whether a call was made during an evening and weekend call plan.

    Evenings would be 7pm-7am
    Weekends would be 7pm Friday - 7am Monday

    If the call was made during Evenings/Weekends I'd like to display Yes in column E otherwise display No.
    Column D is what I would expect to see.

    I've created the following two parts of the formula which work on their own but I don't know how to create 1 formula that includes all the rules for evening and weekend calls .
    =IF(AND(C2"Sat", C2"Sun"),"No", "Yes")
    =IF(AND(B2>=TIMEVALUE("07:00:00"),B2<=TIMEVALUE("19:00:00")), "No", "Yes")

    I've manually entered values into the expected output column. Apologies if the column headings don't line up.

    A B C D E
    Date Time Weekday Expected output Covered by evening and weekend call plan
    02/08/2021 14:11:11 Mon No
    31/07/2021 12:37:10 Sat Yes
    31/07/2021 16:52:23 Sat Yes
    30/07/2021 21:11:19 Sun Yes
    29/07/2021 20:25:36 Sun Yes
    28/07/2021 20:30:21 Wed Yes

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

      =IF(OR(WEEKDAY(A1,2)+A1-INT(A1)<(1+TIMEVALUE("7:00:00")), WEEKDAY(A1,2)+A1-INT(A1)>(5+TIMEVALUE("19:00:00"))),"Yes","No")

      Hope this is what you need.

      1. Hi Alexander,

        Thanks for taking a look at this and for your suggestion, unfortunately it didn't give me the result I was looking for.

        The conditions are a Yes if either Weekday=Sat or Sun, OR time of day for remaining days is =19:00

        Date Time Weekday Expected_output Alexander_solution_evening_and_weekend_call
        02/08/2021 14:11:11 Mon No Yes
        31/07/2021 12:37:10 Sat Yes Yes
        31/07/2021 16:52:23 Sat Yes Yes
        30/07/2021 21:11:19 Sun Yes No
        29/07/2021 20:25:36 Sun Yes No
        28/07/2021 20:30:21 Wed Yes No

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

          =IF(OR(A1-INT(A1)<=TIMEVALUE("7:00:00"), A1-INT(A1)>=TIMEVALUE("19:00:00"),WEEKDAY(A1,2) > 5),"Yes","No")

  50. If in a column range is Safdar and in next column his number is 6 which formula can tell me that if in that range Safdar exist then bring his number 6

Post a comment



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