Share via

How do I combine more than and less than into an excel If formula

Adrian Turner 0 Reputation points
2024-01-04T20:40:17.1833333+00:00

Hi, I want to run a formula that asks if cell B# is 51 or more but less than 100....what is the series....? I thought it would be IF(B3>51<100)....but that doesn't work....

Thanks

Microsoft 365 and Office | Excel | For business | Windows

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Andreas Baumgarten 123.6K Reputation points MVP Volunteer Moderator
    2024-01-04T22:35:01.8333333+00:00

    Hi @Adrian Turner ,

    you can try this:

    =IF(AND(B3>51; B3<100);"Yes";"No")

    if you get an error please try this:

    =IF(AND(B3>51, B3<100),"Yes","No")

    (If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

    Regards

    Andreas Baumgarten

    1 person found this answer helpful.
  2. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  3. Barry Schwarz 3,746 Reputation points
    2024-01-07T02:05:44.6+00:00

    You have a multiple errors with your parentheses. Here is your code broken up into separate lines for each argument. I have added line numbers for ease of reference. Remember, the IF function takes three arguments: condition, true-result, and false-result.

    1. IF(B3<50,
    2. (N3*$C$57)+$C$51+$C$52+$C$55),
    3. IF((AND(B3>51,B3<100),

    (N3*$C$57)+$C$51+$C$53+$C$55), 

    
1. 	  IF(B3>101,
1. 
1. ```sql
  (N3*$C$57+$C$54+$C$55),

    1. 	 0)

    Line 1 is the start of IF#1. It contains the 1st argument of IF#1.

    Line 2 contains the 2nd argument for IF#1. The final right parenthesis is unbalanced. It actually matches the left parenthesis in line 1 which means IF#1 has only two arguments when it needs three.

    Once we remove the offending parenthesis, line 3 starts the 3rd argument to IF#1. This argument is another IF function, IF#2, and line 3 contains the 1st argument of IF#2 Notice that this line contains a double left parenthesis. The first left parenthesis is correct; it is the standard one between the function name and the arguments. The second left parenthesis is incorrect. Its presence causes both lines 3 and 4 to be considered part of the 1st argument of IF#2. Obviously the 1st argument should just be the AND function. So we eliminate the second left parenthesis on line 3. Now, line 3 contains only the 1st argument of IF#2.

    Line 4 now has the same problem as described for line 2 and we eliminate the final right parenthesis. Now line 4 contains the 2nd argument to IF#2.

    Line 5 now contains the 3rd argument to IF#2. This argument is another IF function, IF#3, and line 5 contains the 1st argument of IF#3.

    Line 6 contains the 2nd argument of IF#3. While not incorrect, the parenthesis on this line are completely unnecessary.

    Line 7 contains the 3rd argument of IF#3 and the right parenthesis that terminates IF#3

    Now you are missing the two right parentheses needed to terminate IF#2 and IF#1, in that order.

    Once I fixed these problems, the formula was accepted.

    Also note that all your parentheses surrounding the multiplication terms are unnecessary but not incorrect. However, they do seem to have sufficiently confused you to cause the other problems.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.