On our blog, we already have a handful of Excel IF tutorials and still discover new uses every day. Today, we are going to look at how you can use IF together with the AND function to evaluate two or more conditions at the same time. Continue reading
Comments on: IF AND formula in Excel
by Svetlana Cheusheva, updated on
Comments page 4. Total comments: 307
=IF(AND(L11>=30, L11<100), "OBESE", IF(AND(L11=25), "OVERWEIGHT", IF(AND(L11>25, L11>=18.5), "NORMAL WEIGHT", IF(AND(L110), "UNDERWEIGHT")))))
Hi Patrick,
If I get it right, you mean the following:
1) an ‘OBESE’ note is required if the value in L11 is equal to or more than 30 but less than 100;
2) an ‘OVERWEIGHT’ note is required if the value in L11 is equal to 25;
3) a ‘NORMAL WEIGHT’ note is required if the value in L11 is equal to or more than 18.5 but less than 25;
4) an ‘UNDERWEIGHT’ note is required if the value in L11 is equal to 0.
If so, I would rewrite your formula applying the IFS function:
=IFS(L11=0, "UNDERWEIGHT", AND(L11>=18.5, L11<25), "NORMAL WEIGHT", L11=25, "OVERWEIGHT", AND(L11>=30, L11<100), "OBESE")
Please note that the above formula will bring ‘#N/A’ every time you try to make it process values like 15, 27, or 102 since they are not covered by your conditions.
Can someone tell me what's wrong with this formula, I can't seem to get it to work. Thanks!
=IF(AND(L11>0, L11=18.5, L11=25, L11=30, L11<100, "Obese"))))))))
That's not the formula I wrote, hang on...
I have a list of customers each with a unique ID number
I can have the same customer multiple times on a report
Each Customer has a status of Ordered or Ordering
Using the customer number I need to know which customer number has both "Ordered" & "Ordering" in its status field.
The Result should be a Yes or No.
or to go simpler
COL A COL B COL C COL D
ROW 1 Customer ID Ordering Ordered Both
ROW 2 1111 yes yes yes
IN D2 put the following formula: =IF(AND(B2="yes",C2="yes"),"yes","no")
then just simply copy and paste it down the Column D
:)
Hi Rich,
Let’s say your customers’ ID numbers are in Column A and the information on their status, which is either ‘Ordered’ or ‘Ordering’, is in Column C. I would suggest applying Ablebits’ ‘Merge Duplicates Wizard’ first to keep only unique ID numbers in Column A and a summed up record saying ‘Ordered;Ordering’ or ‘Ordering;Ordered’ in Column C. Don’t omit the ‘Backup this worksheet’ option so as not to lose your original table. Then I would write the following formula in Cell, say, H21:
=IF(AND(H20=INDEX(A:A,MATCH(H20,A:A,0)),OR(INDEX(C:C,MATCH(H20,A:A,0))="Ordered;Ordering",INDEX(C:C,MATCH(H20,A:A,0))="Ordering;Ordered")),"Yes","No")
That formula reacts to changing an ID number in Cell H20 and brings either “Yes” or “No”, depending on the situation. If you try to type in the ID number which Column A does not contain, the formula will bring ‘#N/A’.
Thanks for your great work you do by helping others..
I have a unique problem seeking formula for use in Office 2010.
Problem: Looking for a formula to :- Find matches in any two cells in the same row where.. For example column A has exactly five or 8 digit numbers and column B has only a single digit with result in column c stating match/no match
Say.. i have two columns in Excel each having numbers.In the first column A i have 5 or 8 number digits exactly. in B i have a single digit. what i need to do is find if the number present in column B, is matching in column A with result in column c stating match/no match i have enjoyed your formula given "Example 2. Find matches in any two cells in the same row
If you are looking for a way to compare columns for any two or more cells with the same values within the same row, use an IF formula with an OR statement: =IF(OR(A2=B2, B2=C2, A2=C2), "Match", "") " ...... But not helping with my problem Please reply. thanks
How to solve this?