Though very popular, the nested IF statement is not the only way to check multiple conditions in Excel. In this tutorial, you will find a handful of alternatives that are definitely worth exploring. Continue reading
Comments on: Excel Nested IF statement: examples, best practices and alternatives
by Svetlana Cheusheva, updated on
Comments page 4. Total comments: 231
i am looking fro a formula for below problem.
We have 3 cells a1,b1&c1.
in cells d1-->if out of 3 cells only single cell contain value >0 then it will show Ok otherwise not ok.
FOr EX:
a b c d
0 0 0 OK
1 0 0 OK
1 1 0 not ok
0 1 0 ok
Hello!
The formula below will do the trick for you:
=IF(COUNTIF(A1:C1,">0")=1,"OK","Not OK")
Hope this is what you need.
=IF(P7="A",IF(Q715000,Q750000,Q7*60%/30*O7,0)))),IF(P7="B",IF(Q715000,Q750000,Q7*60%/30*O7,0))))
the above formula not run please give the solution to me
Thanks Svetlana. Your article above was really helpful.
i want to do below type formula in column K
column I is numeric 23.45
and i want to do is if column L will -23.45 so "SL", if column L will subtract with 2 is equal to 23.45 then "1:2" and last if column L will subtract with 3 is equal to 23.45 then "1:3"
=IF(L2=-I2,"SL",IF(L2=I2,"1:1",IF(L2/2=I2,"1:2",IF(L2/3=I2,"1:3"))))
i get only "SL" , "1:1" so
can you help to resolve?
for some reason did not show in the formula
h40
g40
I have 4 budget/accounting columns
F4 = Budget Transfers (can be +or-)
G4 = Purchase Order Amount Allowed
H4 = amount paid OR "closed"
I3 = Beginning balance (which could be zero)
=if(H4="closed",I3+F4,IF(H40,I3+F4-H4,IF(G40,I3+F4-G4,I3+F4)))
Please assist:)
Hello!
I’m sorry but your task is not entirely clear to me.
Please describe your problem in more detail. Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you. Thank you.
I have actually sorted this formula out now so no help needed. The formula I ended up with was
=IF(AND($A$4=$A$6,ISBLANK(J13),ISBLANK(K13),ISBLANK(L13)),"",IF(AND($A$4=$A$7,ISBLANK(M13),ISBLANK(N13),ISBLANK(O13)),"",IF(AND($A$4=$A$8,ISBLANK(P13),ISBLANK(Q13),ISBLANK(R13)),"",IF(AND($A$4=$A$9,ISBLANK(S13),ISBLANK(T13),ISBLANK(U13)),"",CONCATENATE(F13,G13)))))
Hi
I am trying to put a formula together and cant seem to get the nesting correct. What i am trying to do is get a concatenate value if the IF and AND are true based on another cell.
My data:
cell A1= Qtr 1
cell J10 = Jan
cell K10 = Feb
cell L10 = Mar
cell M10 = April
cell N10= May
cell L10= June
cell F10 = project name
cell G10 = project number
What I am after is a formula that will look at cell A1 and if its equal to Qtr 1, and if cells J10(Jan), K10 (Feb) and L10 (Mar) are blank, then i want it to return blank, otherwise concatenate the project name and project number. But if A1= Qtr 2, and if M10, N10 and L10 are blank then return blank,otherwise concatenate project name and project number. And so on for quarters 3 and 4. Is this possible or is there a simpler way to do this?
Appreciate your assistance.
Hello Charlie!
If I understand your task correctly, the following formula should work for you
=IF(AND(A1="Qtr 1",J10="",K10="",L10=""),"", IF(AND(A1="Qtr 2",M10="",N10="",O10=""),"", IF(AND(A1="Qtr 3",P10="",Q10="",R10=""),"", IF(AND(A1="Qtr 4",S10="",T10="",U10=""),"",F10&G10 ) ) ) )
I hope this will help
Hello. I am working to find a formula in a column that will have 1 of 4 outcomes; N/A, Not Started, Active, & Expired.
Column A - # of Warranty Years
Column B - Start Date of Warranty
Column C - End Date of Warranty
Column D - Status (N/A, Not Started, Active, or Expired)
There are times when column A is 0, so that status is N/A.
There are times when column A has a value, but column B & C are empty, so that status is Not Started.
I am trying to use the TODAY() function as well so that whenever the sheet is opened it is current for expired warranties.
Thank you for your help.
Hello Denise!
I hope you have studied the recommendations in the above tutorial. Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. In that case I will try to help you.
Here are some of the formulas I have tried, none of them work, and most only account for 3 of the 4 outcomes.
=IF(AND(A1=0,"N/A","Not Started"),IF(C1>TODAY(),"Active",IF(C1TODAY(),"Active"),IF($C10,$D1=""),"Not Started"),IF($C1>TODAY(),"Active"))
=IF(A1=0,"N/A",IF(C1>TODAY(),"Active",IF(C1<TODAY(),"Expired")))
Hello Denise!
If I understand your task correctly, the following formula should work for you:
=IF(A1 > 0,IF(AND(B1 <> "",C1 <> ""),IF(C1 < TODAY(),"Expired","Active"),"Not Started"),"N/A")
I hope this will help, otherwise please do not hesitate to contact me anytime.
Thank you for your help. It worked perfectly. Take care.
Hello Alexander Trifuntov
I was hoping you could help with my excel function have being having trouble with.
Am trying to archive below multiple query.
=IF(AND (A4 = "WEEK 1"(OR( D9=({"E", "EOC1", "EOC2", "L"})) & ( D10=({"E", "EOC1", "EOC2", "L"})), "WORKING", "NOT WORKING"))).
I can also shortened it by saying----
=IF(AND (A4 = "WEEK 1"(OR( D9=({"E*","L"})) & ( D10=({"E*","L"})), "WORKING", "NOT WORKING"))) using a wildcard for the E's.
so if both cells contain the range values is TRUE else FALSE.
The formula work for single cell like this
=IF(OR(D9=({"E";"L";"EOC1";"EOC2"})),"WORKING","NOT-WORKING" ) but am having trouble with 2 cells and concatenating it.
Thanks in advance
Hello Leks!
If I understand your task correctly, the following formula should work for you:
=IF(AND(A4="WEEK 1", OR(D9={"E","EOC1","EOC2","L"}, D10={"E","EOC1","EOC2","L"})), "WORKING","NOT WORKING")
I hope this will help, otherwise please do not hesitate to contact me anytime.
Hi Alex
That work perfectly for me. Thank you so much, i really appreciate your quick response.
Many Thanks
Hello Bharath
I was hoping you could help with my excel function have being having trouble with.
Am trying to archive below multiple query.
=IF(AND (A4 = "WEEK 1"(OR( D9=({"E", "EOC1", "EOC2", "L"})) & ( D10=({"E", "EOC1", "EOC2", "L"})), "WORKING", "NOT WORKING"))).
I can also shortened it by saying----
=IF(AND (A4 = "WEEK 1"(OR( D9=({"E*","L"})) & ( D10=({"E*","L"})), "WORKING", "NOT WORKING"))) using a wildcard for the E's.
so if both cells contain the range values is TRUE else FALSE.
The formula work for single cell like this
=IF(OR(D9=({"E";"L";"EOC1";"EOC2"})),"WORKING","NOT-WORKING" ) but am having trouble with 2 cells and concatenating it.
Thanks in advance
E4 is either blank or contains a date
I'm trying to get A4 to: 1) to be blank if E4 is blank or 2) place an X in A4 if e4=<today().
Everything I have tried fails to produce the desired results. I have tried choose functions, if and nested ifs but nothing I'm doing is working.
Thanks for any help.
Hello Ray!
If I understand your task correctly, please try the following formula:
=IF(E4=TODAY(),"x", IF(E4="","","not today"))
I hope it’ll be helpful.
I need a formula if A1 is greater the or equal to 15 December 2019 or lesser then 14 March 2020 and if A2 says listed then say Yes if not say no or if A1 is greater then or equal to 15 March 2020 then say Yes.
Hello Bharath!
If I understand your task correctly, please try the following formula:
=IF(AND(A1 > = DATE(2019,12,15),A1 < = DATE(2020,3,14),A2="listed"),"YES",IF(A1 > = DATE(2020,3,15),"YES","NO"))
I hope it’ll be helpful.
1)If= first date of period all floor commission 2%
2)If=second date of period
lower floor commission 3% ,
middle floor commission 3.5%
Higher floor commission 4%
3)If = third date of period
Lower floor commission 2%
Middle floor commission 2.5%
Higher floor commission 3%
*(Date of periods
(1) 29-09-2019 to 02-02-2020
(2) 03-02-2020 to 29-02-2020
(3) 01-03-2020 to 30-03-2021)*
Can you please help any formula to get correct commission %
Please help it's very urgent....
Please help me!!!
I want to use an IF Command, but I want it in such a way that it fetches another IF Statement from a totally different Cell...
How do I do it?
Hello Abubakar!
If I understand your task correctly, the following formula should work for you:
=IF(C1,1,0)
in cell C1 write down the formula
=IF(A1>0,TRUE,FALSE)
I hope it’ll be helpful.
Hello,
I would ask for your advice in choosing the easiest procedure for determining ratings testing physical ability to take data from two different cells that are related to gender (male or female) and age group (is different for men and women, and can be easily determined on the basis of age using nested IF functions). Therefore, it is necessary to include data relating to gender and age category in the test results to obtain a score which is also categorized as laid down norms. Pre grateful!
My apologies... it sent before I finished my question.
Over Amt Short Amt Retailer ID
351.20 25862
10.00 37586
10.00 67952
351.20 25862
I would like to have the formula highlight amounts in red that are the same between Over Amt (col C) and short Amt (col D), but ONLY if the Retailer IDs (col E) are the same. As you can see here, the 351.20 amounts have the same retailer ID, but the 10.00 amounts do not. Any help you can give would be appreciated.
Thank you!
Chris
Good morning. I'm trying to figure out how to do the following:
Over Amt Short Amt Retailer ID
351.20
I NEED A ONE FORMULA FOR THIS:
I WANT TO WRITE "A" IN 2 CELLS AUTOMATICALLY IF WE BOOK AN APPOINTMENT FOR SERVICE "A"- 1 HOUR
AND TO WRITE "B" IN 2 CELLS AUTOMATICALLY IF WE BOOK AN APPOINTMENT FOR SERVICE "B" - 1 HOUR
AND TO WRITE "C" IN 4 CELLS AUTOMATICALLY IF WE BOOK AN APPOINTMENT FOR SERVICE "C" - 2 HOURS
AND TO WRITE "D" IN 3 CELLS AUTOMATICALLY IF WE BOOK AN APPOINTMENT FOR SERVICE "D" - 1.5 HOURS
PLEASE NOTE THAT THE ONE CELL MEAN 30 MINS.
I have a table of information. In the D column I have multiple products with various units of measure (g, mm, ml, kg, etc) I am attempting to build a formula that searches for the specific unit of measure and populates a new column with only that unit. Eg:
Column B3 information : DEN BRAVEN ACRYLIC BEECH/ OAK 280ml
Information I want the formula to find and place into column D "ml"
I have tried building a multiple IF but I it only identifies the first range of data successfully. The moment the Formula gets to a different unit of measure then it returns a #VALUE! issue.
Column B4 Data that the formula has issues with : ALCOLIN WOODFILLER OREGAN PINE 200g
Here is a copy of the IF:
=IF(FIND("ml";B3;1);"ml";IF(FIND("g";B3;1);"g";IF(FIND("m";B3;1);"m";"no")))
Could anyone give me some pointers?
A1 value is Male or Female
B2 Value is 10000 or 5000 or 15000 or 30000
in C2 result want if male is greater than 10000 then 200 or greater than 7500 then 175 or 0
or C2 result want if Female is greater than 10000 then 200 or 0
SOS, I am totally lost here.
I have 5 colons I need to take in my formula, with a total of 4 conditions and I need to calculate the following:
IF and and Then
condition1 D1460 G14=0 P14=0 =D14*$L$10
condition3 D14+G140 G14=<60 P14=Y D14*$L$10+$M$9
- and in all other cases it should be D14+G14
How do I get them all in one field and make excel calculate the result with all those parameters? Is it possible at all?
I tried with: IF((AND(D1460, G14=0), D14*$L$10, IF(D14+G14<=59, $M$9, IF((AND(G14=<60, P14=J), D14*$L$10+$M$9, D14+G14))))
But the formula is obviously wrong :-(
Since I am a linguist and the last time I had maths was in 1983, you can understand my confusion...
Many thanks!
Hi
I have to update the three different states professional tax values (PT) in column B based on the Column A (Salary) & Column C (states). state wise & Salary wise PT slab given below. I tried with if condition. It is throwing me an error. Can you help
Col A Col B Col C
Salary PT State TN PT Slab
1800 KL 12501 208.00
5500 KL
6000 KL KA PT Slab
6500 TN 0-15000 0
7500 TN >150001 200
7600 KL
8600 TN KL PT Slab
9000 TN 20834 208
25000 KA
I have a table with values, no text. The data look something like this
0.00 0.51 1.01 0.00 1.43
0.82 2.48 5.40 1.96 7.75
0.39 0.00 0.00 0.93 0.00
I need to present these values in four categories:
0 [presenting as 0]
>0 and <0.05 [presenting as =0.05 and =1 [presenting as the actual value]
I have not found a way for IFS to test against a range of values within a single unit of the function, as the old AND function used to enable in IF statements. Is this possible?
A chunk of the four categories got erased. Here are the categories, again:
0 [presenting as 0]
>0 and <0.05 [presenting as 0.04999 and 0.99999 [presenting as the actual value]
Thank you for better understand the if forula
I can’t figure out how to write this...the cell that I’m evaluating is a %
The formula I’ve tried: IF(B17≥65,"THRIVING",IF(B17≥50,"Ahead Of The Curve",IF(B17>35,"TURBULANT","Making Ends Meet")))
Criteria: 66+% = Thriving,
51-65% = Ahead Of The Curve,
36-50% = Making Ends Meet,
0-35% = Turbulent
One cell I’m evaluating has a value of 45%, another of 88%...the formula says both are Making Ends Meet
Can you help me?
HI Donna,
I thing this will you.
IF(A11>=66%,"THRIVING","")&IF(A11<=65%,"Ahead Of The Curve","")&IF(A11<=50%,"Making Ends Meet","")&IF(A11<=35%,"TURBULENT","")
169 #N/A
169 #N/A
169 #N/A
169 Punjab National Bank
169 #N/A
169 #N/A
i want to have punjab national bank in all the other places where it is #N/A. please share logic to write in other column
I need a formula for excel -
If (Salary<=13000) Then 9617 Else 12022 Elseif(Salary<=24000) Then 15100 ElseiF(salary<=30000) Then (Basic*0.45)
Help me
Sub Value()
Dim Salary As Integer
Dim Basic As Double
Basic = Cells(?, "?")
Salary = Cells(?, "?")
If Salary < 13001 Then
Cells(?, "?") = 9617
ElseIf Salary < 24001 Then
Cells(?, "?") = 15100
ElseIf Salary < 30001 Then
Cells(?, "?") = Basic * 0.45
Else
Cells(?, "?") = 12022
End If
End Sub
I am trying to find a max value from a list which belongs to another range of data. For example, when I have a data: A=1, B=2, C=3, D=4, E=5. If the list contains A, C, D, the output should be 4 (the maximum value).
I made a formula that works (F column: A,B,C,D,E; G column: 1,2,3,4,5; J column: list)as below: =MAX(IF(F22:F29=J21,G22:G29),IF(F22:F29=J22,G22:G29),IF(F22:F29=J23,G22:G29),IF(F22:F29=J24,G22:G29))
But I wonder, if there is any way to make the formula simpler.
Please help. My Formula mentioned below is working correctly.
=IF(I4>=9000, 2%, IF(I4>7500,1.75%, IF(I4>6000, "1.5%", IF(I4>4000, "1%", IF(I4=9000, 2%, IF(I4>7500,1.75%, IF(I4>6000, "1.5%", IF(I4>4000, "1%", IF(I4<4000, "0")))))), "0")
Thank you - this was extraordinarily helpful! The IF & IF & IF was exactly what I needed to make my column work properly. I used it in Google Sheets and it worked like a charm!
Afternoon, looking for some help with trending some date with date ranges. Have a data set with a lot of fluid data from multiple people and downloads. I need to 'Snap shot' the data in specific cells to track progress quickly for a trending report week on week. Complete a large search on the net but it I can not find anything the help with my problem. My current formula looks back at last weeks results which is misleading the data. Currently I have to manually over write the formula result each week manually so the data is retained and not re calculated, or over written. Any help appreciated..
hi, i have 3 different if formulas that works when entered separately. However, when i tried to combine them, the result shows "FALSE".
=IF(E3="PREVIEW 1",IF(N3>=11,30,IF(AND(N3>=10,N3=9,N3=12,30,IF(AND(N3>=11,N3=10,N3=14,30,IF(AND(N3>=13,N3<14),24,IF(N3=13,18,""))))))))))))
Please help me get the formulae for the below Ms excel problem. I need the card rate to appear automatically on the 4th column when the amount of Fixed Deposit (in millions), period of fixing (in days) & interest offered (%) is given. The card rates are given below.
FD(Mns) Days Int Offered(%) Card Rate (%)
2.65 31 7.50
11.34 91 7.50
64.21 181 8.00
178.58 365 8.75
CARD RATE
Days 10Mn50 Million
30 to 90 6.00 6.50 7.00
90 to 180 6.50 7.00 7.25
181 to 364 7.00 7.25 7.75
365 to 730 7.25 7.75 8.25
'=IF(AND(F9>0,F9=11,F9<=15),30,IF(F9<15,25,0)))
=IF(AND(F9>0,F9=11,F9<=15),30,IF(F9<15,25,0)))
@Nishith Rana try:
=IF(AND(F9>0,F9=11,F9<=15),30,IF(F9<15,25,0)))
=IF(0<F9<=10,45,IF(11<=F9<=15,30,IF(15<F9,25,0)))
THIS WONT WORK. HOW TO CORRECT THIS??
I run a badminton booking spreadsheet with 7 named players (as column headings in row 2) and I want to identify the first 4 people who have said "YES" (in row 3), working from the left. I have nested IFs, but I want to stop trying after I have achieved the 4th YES (because you can only get 4 player on a badminton court!).
This:
=CONCATENATE("This week it's ",
IF(C3="YES","me, ",""),
IF(D3="YES",$D$2,""),IF(D3="YES",", ",""),
IF(G3="YES",$G$2,""),IF(G3="YES",", ",""),
IF(I3="YES",$I$2,""),IF(I3="YES",", ",""),
IF(J3="YES",$J$2,""),IF(J3="YES",", ",""),
IF(K3="YES",$K$2,""),IF(K3="YES",", ",""),
IF(L3="YES",$L$2,""))
gives
"This week it's me, Roger, Sanath, Agnelo, Greg, Alec"
which is 6 names because José in column D had said "NO".
I would like the result to read
"This week it's me, Roger, Sanath, Agnelo".
I think the more gramatically correct
"This week it's me, Roger, Sanath and Agnelo"
might be too much of a challenge!
Any ideas please?
Hi Guy,
What if all the players reply in the affirmative? Will it be possIble to pick only four of them for a game without upsetting the others? If random selection sounds good to you, I can suggest applying a formula that will bring the names of those who want to take part (Step 1) and turning the values received into a 'Custom list' to delegate Ablebits' 'Random Generator' to take an unbiased decision (Step 2). If you like the idea, this is the formula which is needed in Step 1:
=IF($3:$3="YES", CHOOSE(1, $2:$2), "")
FYI =IF(G39="","",IF(J39=G39,"Contact individual",IF(M39="","",IF(M39<=J39,"Returned","Contact individual")))))
Hello please could you wizards advise on the following:
=IF(G40="","",IF(J40=G40,"Contact individual",IF(M40="","",IF(J40>=M40,"Returned","Contact individual")))))
I require a nest IF (if think)
I have three dates and three different document status outcomes depending on the sequence below.
If the issue date is is filled in only, then the status shall be "in circulation".
if there is now a document withdrawal date in the next cell that the is greater or equal to the issue date, the value shall be "Contact individual".
If the return date value is missing from the required cell then the status shall remain as "Contact individual", but if a date is entered into the withdrawal date cell the status then the status is to return "Returned"
Please Help Friends
I need to do an IF statement to get a range for:
Volumes Greater than and equal to 2,000,000
Volumes Less than 2,000,000 but greater than and equal to 200,000
Volumes less than 200,000 but greater than and equal to 500
Volumes less than and equal to 500
I just want to say thank you. I was able to create an if-choose on my excel thanks
Awesome article. I switched nested ifs for Lookup - made it so much easier. Also now I can go to the lookup table and change values without having to copy paste the formula again. Thanks....
I need help in defining the reorder level using "IF" or any other formula in excel
Column A = Shortage = 18500
Column B = MOQ = 5000
Column C = Reorder level = ?
I would like to calculate the reorder level as follows :-
(ie. Reorder level should be = to MOQ if shortage is less than MOQ
OR Reorder level should be 20000 if shortage is between 15001 & 20000
ie. Reorder level should be in multiples of MOQ but > shortage
Kindly confirm how to use "IF" formula or any other formula in excel
Tony,
You do not need an IF function for this.
Try:
=CEILING(shortage, 5000)
This will round your shortage number up to the next 5000.
K
Thanks a lot. it did work
Error in your explanation of the SWITCH function. Line 1, you use SWIFT. I expect you mean SWITCH. #yourewelcome
Of course, I meant SWITCH. Fixed, thank you!
I have a formula I am trying to use for Overbillings and underbillings, each a separate column. If I use one column the formula looks like this:
=IF([@[% Comp]]<30%,[@[Earned Cost]],[@[% Comp]]*[@[Contract Amount]])-[@Billed]
but I want it to give the result of zero if the answer to this in the overbillings is over zero. What do i add?
I am attempting to create a formula with IF statements.
Here it is:
G6 =TODAY() which inputs today's date.
J6 If "Yes" is selected
L6 will input TODAY()+7
=IF(J6="Yes",G6=TODAY()+7) the result will go into L6
Thank you
I am attempting to create a dynamic table where the value in one cell makes another equal a 3rd cell plus the data in another cell. So for example, =IF(F2=P7,G2+Q7,"-") I can get it to work with that one---but I need to nest that statement with 10+ others of the same type (=IF(f2=P8,G2+Q8) etc etc (pulling data from a table). I'm lost.
Not working. Please help please. I need to use 16 conditions for if statement but as you see, it is only 10 and not working. It said that "this formula uses more levels of nesting than you can use in the current file format". Anyone please.
Thanks!
=IF(S10>=95,20, IF(S10=94,19, if(s10=93,18, if(s10=92,17, if(s10=91,16, if(s10=90,15, if(s10=89,14, if(s10=88,13, if(s10=87,12, if(s10=86,11))))))))))
I know this is kind of late, but try this:
=IF(S10>=95,20, IF(and(S1085),s10-75,))
For some reason that did not format correctly. I will try it one more time:
=IF(S10>=95,20,IF(and(S10 85),s10-75,))
Can I use nested IF to do the following :
I have a column with three possibilities entered in the cells : ABC DEF GHI
the next column in the contagious cell needs a number based on the above so that :
ABC would be 123
DEF would be 456
GHI would be 789
Hi Bob,
Your formula may be as follows:
=IF(A2="ABC", 123, IF(A2="DEF", 456, IF(A2="GHI",789)))
Please have a look at Svetlana Cheusheva's article 'Excel nested IF statement - multiple conditions in a single formula' to learn more about the IF function.