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 3. Total comments: 231
hi the first ie increases by .1 works ,but in the second if it does not go down by .2 per g2-36 (g2 is 40)and i also need to decrease by .3 .4 .5 per differance.
ps number of brackets at the end may be incorrect as i shortened the formula for this question.
=IF(AND(F2>=0;F2<=4.4;G236;(F2-(G2-36)*0.1);IF(AND(F2>4.4;F2<=11.4;G236;(F2-(G2-36)*0.2)))
Hello!
I’m not sure I got you right since the description you provided is not entirely clear.
I assume that TRUE and FALSE are mixed up in the formula. Try to swap them
IF(AND(F2>=0;F24.4;F2<=11.4);(F2-(G2-36)*0.2), .......))
Observe the correct syntax of the IF function.
Hello,
I am looking to perform a nested IF which looks a the value of two cells (column v and column an) and returns values as follows:
If cell V2 ="Pass" show the text "Proposed" else "Requires Exam" then as part of the same if statement:
If cell AN2 =text "Licenced" else the result of the earlier V2 result
I am sure I am missing something obvious as so far I have tried the following:
=IF(V2="Pass","Proposed","Requires Exam"(IF(ISTEXT(AN2),"Licenced","Result of V2="Pass","Proposed","Requires Exam"
=IF(AND(V2="Pass",AN2="Licensed"),"Licensed",
IF(AND(V2="Pass",AN2=0,"Proposed"),
IF(AND(V2=0,AN2=0,"Requires Exam"),"")))
Hi!
According to the condition of cells V2 and AN2, only one variant of the text can be shown. You offer 2 options. This cannot be done with the IF function. Read the instructions for using the IF function.
Hi Sir, Why this if condition is not working =IF(AF="FJ",AND(AP$16>=$AG21,AP$16<=$AI21))
Hello!
The formula does not work because the conditions are written incorrectly. Check out the guidelines in this article.
Can this be done with formulas or do I need to go down the VBA route?
i am trying to add to the if(and function to display information from other cells after it is through as shown below
column info
A-their first name
B-their last name
D their sex "M" or "F"
E- client or staff
=IF(E3="staff", "Dr.", IF(AND(E3="client", D3="M"), "Mr.", IF(AND(E3="client", D3="F"), "Ms.",)))
I am trying to get the above formula to display (Dr./Mr./Ms.) (First Name from column A) (Last Name from column B) in one cell "Dr. Gregory House"
Hello!
Please try the following formula:
=IF(E3="staff","Dr.",IF(AND(E3="client",D3="M"),"Mr.",IF(AND(E3="client",D3="F"),"Ms.",)))&A3&" "&B3
Hope this is what you need.
Hello,
I am attempting and if and or statement, but having trouble getting to the correct result. part I
If(AJ11="US","TRUE",IF(AJ11="",IF(AG11="US","TRUE",IF(AG11="",IF(AI11="US","TRUE","review"))))), returning false instead of True or review.
there are 3 col with data points, if no US or blank = true, if any us, blank and non us, test
if result is to test, the 2 part is to test for 2 letter code to either test or not test.
suggestions?
Hi!
Unfortunately, your explanations are incomprehensible. Describe the problem more clearly.
Hi there,
I am struggling with my formula I'd love some help:
I have 8 data values validated in cell I2, and will be entering a manual number in cell J2 and believe I need an IF formula in column K to easily calculate the following scenario.
If the frequency = weekly (cell I2), and the number of hours =x, then the monthly hours when annualised would be (J2*52)/12).
The other values in I2 are:
Fortnightly
2 x Weekly (needs annualising then multiplying by 2)
3 x Weekly (needs annualising then multiplying by 3)
4 x Weekly (needs annualising then multiplying by 4)
5 x Weekly (needs annualising then multiplying by 5)
6 x Weekly (needs annualising then multiplying by 6)
7 x Weekly (needs annualising then multiplying by 7)
One-Off (does not need annualising)
The current formula I have is:
=IF(I2="Weekly",((J2*52)/12),IF(I2="Fortnightly",((J2*26)/12),IF(I2="3 x Weekly",((J2*52)/12)*3),IF(I2="2 x Weekly",((J2*52)/12)*2""""))))
It worked until I put the last IF combination in
I'd really appreciate some help. Thank-you :-)
Hello!
Please check the formula below, it should work for you:
=IF(I2="Weekly",J2*52/12,IF(I2="Fortnightly",J2*26/12,IF(I2="3 x Weekly",J2*52/12*3,IF(I2="2 x Weekly",J2*52/12*2,""))))
That worked. Thanks very much!
Hi!
Needed your expertise to correct my formula:
Column A: Interval: 1,2,3,4,5,6,7,8,9,10
Column B: P/MP/OP: OP,OP,OP,OP,MP,MP,MP,P,P,P
Column C: Dispatch: 1.29,0.45,1.29,1.24,0.26,1.51,1.31,0.14,1.25,0.25
Column D2: 1.30
Formula: =IF((OR(A2="MP,A2="OP")),AND(IF(C2>=0.5,$D$2,C2)))
The result must be: if A2 is MP or OP, and the value of C2 is greater than O.5, it will give me 1.30 answer, if C2 is below 0.5, it will give me the value of that cell. If A2 is P and C2 is lower or higher than 0.5, it will give me 0 answer. I only needed to capture the value of MP and OP.
Thank you.
Hello!
Your task is not completely clear to me.
The condition "C2 is lower or higher than 0.5" does not make sense as it is any number. Action is not specified if no condition is met. There is a reference to D2 in the formula. Your description doesn't say anything about D2.
Clarify.
Column D2: Firm Dispatch: 1.30 (any value in column C at a minimum requirement dispatch criteria of 0.5, it will still deliver(choose) 1.30 firm. If column C dispatch is lower than 0.5 dispatch criteria, it will choose the value of that cell in column C. Another criteria is for MP and OP only, if B2 is "P" = 0
Column F2: My initial formula: =IF((OR(B2="MP",B2="OP")),AND(C2>0.5,$D$2,C2)))
Sorry for the confusion, I am referring to "B2" and not "A2":
The result must be: if B2 (not A2) is MP or OP, and the value of C2 is greater than O.5, it will give me 1.30 answer, if C2 is below 0.5, it will give me the value of that cell. If B2 is P, result must be 0. I only needed to capture the value of MP and OP.
Hope this helps.
I have achieved the result I have been looking for. Thank you very much!
Hello!
If I understand your task correctly, the following formula should work for you:
=IF(OR(B2="OP",B2="MP"), IF(C2>=0.5,1.3,C2),IF(B2="P",0,C2))
Hello,
=IF(D5>75,16,IF(D5>70,15,IF(D5>65,14,IF(D5>60,13,IF(D5>55,12,IF(D5>50,11,IF(D5>45,10,IF(D5>40,9,IF(D5>35,8,IF(D5>30,7,IF(D5>25,6,IF(D5>20,5,IF(D5>15,4,IF(D5>10,3,IF(D5>5,2,IF(D5<=5,1))))))))))))))))
Using above formula to show 1 manager for every 5 employees - (ie. 6-10 employees requires 2 managers, 11-15 employees would require 3 managers, 16-20 employees would require 4 managers)
Is there a more efficient/condensed way to accomplish this?
Hello!
You can use this formula:
=ROUNDUP(A2/5,0)
You can learn more about ROUNDUP function in Excel in this article on our blog.
"IF(R10>1,Q10*5,IF(R10<0,Q10*7))" Is tje formula is right or contains any error, because i am getting only Q*5 result where it should be Q*7,
Hi!
I don't know what values you are using. If R10 contains -10, then the formula returns Q10*7
Need help to have text & number sequencing based on a drop down list value (to confirm document type and number)
Column C lists document type from a 3-choice drop down list "Policy","Standard", or "Other"
Columns E: G contain sequential values for each of the document types.
Column E "Policy" list of sequential values 0001-PL (continues as 0002-PL, etc. starting in row 2)
Column F "Standard" list of sequential values 0001-ST (continues as 0002-ST, etc. starting in row 2)
Column G "Other" list of sequential values 0001-OT (continues as 0002-OT, etc. starting in row 2)
Since Column C would have a list that is not sequential, how can I ensure column D sequences document number correctly?
User enters in Column C Row 2 "Policy", Column D will populate as "0001-PL"
User enters Column C Row 3 "Standard", Column D populates as "0001-ST"
User enters Column C Row 4 "Policy", Column D populates as "0002-PL"
How can I make sure that when someone enters one of the three document types, column D picks up last sequence of "xxx-PL"?
Hello!
Write the formula in cell D2:
=TEXT(COUNTIF($C$1:C2,C2),"0000")&"-"&C2
After that you can copy this formula down along the column.
Please have a look at this article: How to count cells with certain text
You can learn more about TEXT function in Excel in this article on our blog.
I hope I answered your question.
Hi
I have doubt
Please help to formula below:
If A1 "-", B1 "-", answer "ok"
If A1 "", B1 "", answer "ok"
If A1 "", B1 "-", answer "check"
Thanks
Please I have a question that I need to solve in nested if c ++
How do I combine =IFERROR(AVERAGE(E6:E8),"") with =AVERAGEIF(E6:E8,"0")
Hi,
The formula AVERAGEIF (E6: E8, ”0 ″) and AVERAGEIF (E6: E8,” ″) means that you are calculating the average over blank and text cells. It doesn't make sense as it will result in an error. Explain what you want to calculate.
SOMEONE PLEASE HELP:
So I am working on jasperactive, one of the projects has me come up with a function that will display Two different texts and if it doesn't match either, it will display a blank
-the original function is: IF(AND(G2>F2,F2>E2),"Growing energy source","")
-Then it asks for you to add in a function that will display "Shrinking energy source" is G2<F2 and F2<E2
-i have been trying to make a nested formula for it that will work for hours with no luck. if anyone can help that would be amazing
Hi,
I hope you have studied the recommendations in the tutorial above. It contains answers to your question
=IF(AND(G2 > F2,F2 > E2),"Growing energy source", IF(AND(G2 < F2,F2 < E2),"Shrinking energy source",""))
=IF(G17>1.63,12CFW, IF(G17>1.3,10CFW,IF(G17>0.978,8CFW,IF(G17>0,6CFW,""))))
excel says there is a problem with this formula? help would be much appreciated, thank you.
Hi,
Text values in formulas must be enclosed in quotation marks.
=IF(G17>1.63,"12CFW", IF(G17>1.3,"10CFW",IF(G17>0.978,"8CFW",IF(G17>0,"6CFW",""))))
I hope it’ll be helpful.
demurrage charges (free time = 3 days) 1-4/1/2021) free time
demurrage charges = 18 days
4-6 days = usd25
7-9 days = usd40
10-12days = usd60
Thereafter = usd75
what is if?
Hello!
Your task is not completely clear to me.
Explain: 4-6 days=usd25. 25 - is it in one day or all the time?
If cell A1 =family and cell A2=1 A3 =A7
if cell A1 =family and cell A2=2 A3 =A8
if cell A1 =family and cell A2=3 A3 =A9
if cell A1 =Single and cell A2=1 A3 =B7
if cell A1 =Single and cell A2=2 A3 =B8
if cell A1 =Single and cell A2=3 A3 =B9
Could someone help me to figure out one If formula that involve multiple conditions.
For example, I have 7 different project code name in G2, and G3 is associated project name.
and when I clicked project code in G2, and G3 will auto pop out the right project name.
I know how to compare within two code. but not sure how to do with multiple different ones.
Below is an example that i made it up for comparing two projects.
=IF(TASK_CODE="123456-789","Bill","Cheques")
Thank you so much
Hello!
I recommend using the VLOOKUP function to select the desired value.
Please check out the following article on our blog, it’ll be sure to help you with your task: How to do Vlookup in Excel
I hope I answered your question. If something is still unclear, please feel free to ask.
Hello- I am trying put a formula together for the below conditions
For Example
IF C2=7.5 Then it should RANK 5 similarly IF C2=8.8 Then it should RANK 4
Below Scale for Each RANK
>11 = RANK 1
<10 - 9- 8-<=9 = RANK 4
<8 = RANK 5
Please help.
Hello!
Please check out the following article on our blog, it’ll be sure to help you with your task — how to use multiple conditions in a IF function.
I hope it’ll be helpful.
Hi, I'm hoping that I'm not too far off the mark in this area :-).
I have a register of risks where I want to flag as overdue. A critical must be attended to within 7 days, High 14, Med 30 and Low 60. My stab at it as below fails with an error. Any ideas please?
X and F contain the age and severity data.
=
IF(AND(X10="Critical",F10<7),OK,
IF(AND(X10="High",F10<14),OK,
IF(AND(X10="Medium",F1060),OK,
Overdue))))
Hello!
If I got you right, the formula below will help you with your task:
=IF(AND(X10="Critical",F10<7),"OK", IF(AND(X10="High",F10<14),"OK", IF(AND(X10="Medium",F10<60),"OK", "Overdue")))
I hope my advice will help you solve your task.
I have three options...3 or less yes ...less committed, 4 yes...average commited. More than 4 yes... hifhly committed for E5 to K5...please help
Hello!
The description of your conditions is not very clear. Please reread the article above, it covers your case completely. Thank you.
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")