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 on: Excel IF statement with multiple conditions
by Svetlana Cheusheva, updated on
Comments page 14. Total comments: 2544
lets says =IF(AND(R4>S4,S4>T4,T4>U4,U4>V4,V4>W4,W4>X4),"UP&DOWN",IF(AND(R4<S4,S4<T4,T4<U4,U4<V4,V4<W4,W4<X4),"PASS","NOT GROWN"))
up&down cannot be used idk why,the circumstance was r4 to w4 just one of them going down its gonna say up&down
please help
Hello!
Please describe your problem in more detail. It’ll help me understand it better and find a solution for you. Thank you.
Need help with formula
if D3=x,v4>120,"RED", "YELLOW"),If D4="Active", "BLANK")
trying to day if D3 = Active and V4 Greater than 120 then RED otherwise Yellow. But if D3 is not equal to Active then leave it blank.
Thanks
Anna
Hello Anna!
If I understand your task correctly, the following formula should work for you:
=IF(AND(D3="Active",V4 > 120),"RED", IF(D3 <> "Active","","YELLOW"))
I hope it’ll be helpful.
updated but still not correct
=IF(AND(B25>A15,B25A16,B25A17,B25A18,B25A19,B25A20,B25*C16)
Hello Keith!
Please describe your problem in more detail. The IFOR function does not exist. What does the B25A16 mean? It’ll help me understand it better and find a solution for you. Thank you.
=IF(AND(B25>A15,B25A16,B25A17,B25A18,B25A19,B25A20,B25*C16)
Hi,
I'm trying to write an "If" statement if 3 variables in different cells match up with each other then "Y" else "N"
If [A2]=[C2]=[D2]THEN "Y" ELSE "N" ENDIF
Hello Michelle!
Please try the following formula
=IF(A2=B2,IF(B2=C2,"Y","N"),"N")
I hope it’ll be helpful.
Hello, how do write a formula for this? If total is between .01%-.99%, output should be $40, if between 1% and 1.99% - output should be $210. I tried IF and also IF(AND with , but I can't get it to work...help please. Thank you so much!
0.01% ---> 0.99% $40.00
1% ---> 1.99% 210.00
2% ---> 2.99% 240.00
3% ---> 3.99% 270.00
4% ---> 4.99% 300.00
5% ---> 5.99% 330.00
6% ---> 6.99% 370.00
7% ---> 7.99% 410.00
8% ---> 8.99% 450.00
9% ---> 9.99% 490.00
10% ---> 10.99% 540.00
11% ---> 11.99% 590.00
12% ---> 12.99% 640.00
13% ---> 13.99% 690.00
14% ---> 14.99% 740.00
hello Phatima!
Write your values in columns A, B, C. A1 - 0.01% B1 - 0.99% C1 - 40.00, etc. If the final value is written in F1, then the formula for determining the output will be as follows:
=VLOOKUP(F1,A1:C15,3,1)
I hope this will help, otherwise please do not hesitate to contact me anytime.
Hi,
Great article!
In my example below, is it possible to add to the formula so that results in column 'C' would also take into consideration the value in column 'B' and (subtract 10 if 'B'=1) or (subtract 20 if 'B'=2)
Hope this makes sense, thank you!
A B C
1 35 1 150
2 33 150
3 40 2 200
4 41 200
=ifs(and(A1>=30,A1=38,A1<42),200)
Hello!
Your formula does not work. I did not understand what result you want to get. If there are already numbers in column C, then they cannot be changed by the Excel formula. Please describe your problem in more detail. It’ll help me understand it better and find a solution for you. Thank you.
thanks for the response.
I didn't realize I pasted wrong the formula
here is the correct one I use:
=ifs(and(A1>=30,A1=38,A1=" and "<") from Column 'A' which are size values. Now I want to add additional criteria (Column 'B') which is a quality grade (A,B,C or can be switched to numbers 1,2,3) so the calculated value (price) in column 'C' would be less by 10 if 'B=1', less by 20 if 'B=2', less by 30 if 'B=3', if 'B=blank' leave it as it is.
Hope this makes sense and thank you for all your help!
Alex
ifs(and(A1>=30,A1=38,A1<42),200)
I'll put it in words:
if "A1" is "greater than or equal to" 30 and "less" than 38, than 150, if "A1" is "greater than or equal to" 38 and "less" than 42, than 200
hope this makes sense as it won't let me paste the formula in the chat correctly.
Hello Alex!
If I understand your task correctly, the following formula should work for you:
=IF(B1<>"", (IF(AND(A1>=30,A1<38),150, IF(AND(A1>=38,A1<42),200,""))) - (B1*10), IF(AND(A1>=30,A1<38),150, IF(AND(A1>=38,A1<42),200,"")))
I hope this will help, otherwise please do not hesitate to contact me anytime.
Yes! That's it!
Thank you sooooo much!
Appreciate all your help!
ifs(and(A1>=30,A1<38)=38,A1<42),200)
...Never mind, the forum messages will change the formula every time I submit it.
...here it is again, for some reason when I publish the comment it changes the formula pasted.
I'll try again with no "=" sign
ifs(and(A1>=30,A1=38,A1<42),200)
Hello,
I need the following formula:
if in column A I have the word "Revision", I need to put it in column B but not at same level (-1 level).
Exemple: if A4=Revision: 2.1--> put all content in B3
Thank you very much
Hello Pascale!
If I understand your task correctly, the following formula should work for you:
=IF(SEARCH("Revision",A4,1)>0,A4,"")
If there is anything else I can help you with, please let me know.
I am trying to work out an IF formula for the following. I have a spreadsheet that details stock and whether it is in date, out of date or expiring soon. I need the cell to show whether an item expires one month from today, is in date or out of date. I am struggling to work out the IF formula - this is what I have come up with so far, but am unable to get any further. Any help would be greatly appreciated by this newbee to Excel
=IF(E3<TODAY()*AND(-365-335),"Exp 1 mth",IF(E3<TODAY()-365,"Out of date","In date"))
Hello Sarah!
If I understand your task correctly, the following formula should work for you:
=IF(E3>TODAY(),"In date",IF(E3 > EDATE(E3,1),"Exp 1 mth","Out of date"))
I hope this will help, otherwise please do not hesitate to contact me anytime.
WHAT SHOULD BE FORMULAE CONSIDERING BELOW CONDITIONS:
- Cell is greater than equal to 5 = P-HOME
- Cell is less than 5 = HLWP
- Cell is "0" OR "NULL" OR "#N/A" = LWP
Hello!
If I understand your task correctly, the following formula should work for you:
=IFERROR(IF(B1 >= 5,"P-HOME", IF(B1=0,"LVP","HLWP") ),"LWP")
Hope this is what you need.
How do I extend this formula to retrun "Agency" if employer column does not = J Smith Ltd.
Employer Source Fomula
J Smith Ltd Direct =IF(C4="J Smith LTD","Direct")
ABC Recruit Agency
XYZ Recruit Agency
Hello Tim!
If I understand your task correctly, the following formula should work for you:
=IF(ISERROR(SEARCH("J Smith LTD",C4)),"Agency","Direct")
Hope this is what you need.
I want the reason for result of IF formula. That if result came was PASS then why? Is it due to column 2 or 3.
Hello SHRIKRISHNA!
To control how the formula is executed, you can use the Evaluate Formula tool. It is located in the menu on the Formula tab.
I hope this will help, otherwise please do not hesitate to contact me anytime.
Thanks sir, evaluate formula shows it but I want that this evaluation in column next to result of formula.
Kindly help me to get percentage in multiple amount different percentage ratio
For example:
60000 to 100000 = 5.5%
100001 to 150000 = 8%
150001 to 200000 = 12%
200001 and above 15%
How to calculate if i have many column in different amount and need to get percentage as per above how to use formula, kindly help urgently. Appreciate any can help me in this regards urgently.
Thank you
Pravin Rupapara
Hello Pravin!
You may use the FREQUENCY function to calculate the number of values in the particular range:
=FREQUENCY(B2:B100,F2:F5)
Where B2:B100 - your data range and F2:F5 - the cells that contain your lower bounds (i.e. 60000, 100000, 150000, 200000)
Select the range of empty cells (G2:G6, for instance) that has one cell more than F2:F5. Paste =FREQUENCY(B2:B100,F2:F5) in the formula bar and apply it as an array function by pressing Ctrl+Shift+Enter. Then divide every resulting numbers by the total of values (the formula for the last one would be =COUNTA(B2:B100)
Enter these formulas into H2:H6 and you'll get the percentage you need.
Ok so I'm trying to compile a file that shows if a particular person has been called during the week.
Each Day has a sheet that populates a persons detail from a unique identifier (Tech Id) and can be marked as YES or NO as to whether they have been called that day.
Tech ID, Name, Called?
I then have a weekly review sheet that i need to populate. same column lay out
So if the Tech ID in A2=FS999 and the corresponding tech ID = YES on any of the daily sheets then it should fill in on the weekly tracker. I Just cant for the life of me get my head around the complexity of so many IF's and OR's .
Can Anyone Advise?
Hello Mike!
If I understand your task correctly, the following formula should work for you:
1. To calculate data for several conditions on several sheets, you can use the formula
=SUM(COUNTIFS(INDIRECT({"Sheet1","Sheet2"}&"!A2:A1000"),"FS999", INDIRECT({"Sheet1","Sheet2"}&"!C2:C1000"),"Yes"))
2. Use this formula in your condition
=IF(SUM(COUNTIFS(INDIRECT({"Sheet1","Sheet2"}&"!A2:A1000"), "FS999", INDIRECT({"Sheet1","Sheet2"}&"!C2:C1000"),"Yes")) > 0,"Yes","No")
I hope this will help, otherwise please do not hesitate to contact me anytime.
Hi All,
I need an excel formula to TEXTJOIN of multipel cells with a matching condition of two column vaues. For example.
Column 1 has Managername Manager1, Manager2, Manager3, Manager4, Manager5
Column 2 has Employees EMP1, EMP2, EMP3, EMP4,EMP5, EMP6 etc
column 3 has OnBench, InProject,InProject,InProject,OnBench
I need to list this into other sheet of the same workbook, with the below condition.
List of all employees, with join text separated by comma into single cell against that Manager name who are OnBench.
Can anybody help with this?
Thanks a lot in advance.
Hello Surya!
I recommend using function Vlookup for multiple values/ Read more here.
I hope this will help, otherwise please do not hesitate to contact me anytime.
I need a formula that looks at a date range on 1 sheet but returns the value on a different sheet, looking at values on the original sheet but only IF a different column on the original sheet shows certain TEXT.
I'm doing the formula on a sheet named 'April!'
Data is on 'Ian!' sheet
So column B6:B500 on Ian! will have the date
Column J6:J500 has the income but it is dependant on the value in column C whether it shows as 'New' or 'Renewal'
Please help, thank you
I currently have this formula that works looking at the total but I now need it to refer to a date range on the same sheet for Ian!
=SUMIF(Ian!$B$6:$B$500,April!$A$1(Ian!$C$6:$C$500,April!$B$1,Ian!$J$6:$J$500))
If column A is the equally weighted value of a score and column B is the score (0-5 or N/A), and a score of N/A is entered on a row, I want the weight to be removed from the row and equally redistributed across all scored rows, thereby increasing the weights in column A.
Is this possible?
Said another way, if all rows had a numeric score, then all weights would be 3%, for example. However, if there were several rows that got an N/A, for each N/A, the 3% value of the weight would be equally redistributed across column A to show what the final, equally weighted values would be.
Thanks for your help! I hope this question makes sense!
Hello Lisa!
If N/A is a text, not an error value #N/A, then use the following formula to calculate the weight:
= IFERROR (B1/SUM($B$1:$B$50), 0)
I hope this will help, otherwise please do not hesitate to contact me anytime.
I am looking for help. My worksheet tracks work as well as issues. I need a formula for conditional formatting that will allow for a visual quick identification. This is what I think the formula should look like but I get an error. =IF(G22="Y","CORROSION",""(AND(H22="X","CLEANED"))); or =IF(G22="Y","CORROSION","",IF(G22="Y"(AND(H22="X"),"CLEANED"))
The first part of the Formula works it is trying to add the second half that causes the error. Anyone have a suggestion?
Hello Jesse!
Your formula contains errors. 1. You cannot use the = sign inside a formula. 2. The conditions AND and OR are incorrectly described. You have not explained how the formula should work, so I can’t fix it. To do it yourself, read the instructions in this article above.
I hope this will help, otherwise please do not hesitate to contact me anytime.
On my spread sheet I have criteria that represent if an item is corroded or not, block 1 "item", block 2 is "inspected", block 3 "corroded", block 4 'cleaned/primed", and block 5 "notes". If it is corroded I put a "Y" in the corroded block. In my note block I have a formula, =IF(G4="Y","CORROSION",""), if this block has a "Y" it will show the word "CORROSION" this works perfectly. What I need to do is add to the formula that considers the corroded block with a "Y" and the clean primer block with a "X" to return with the word "REPAIRED". Is there a way to do this? I need this as a quick reference so at a visual glance the item can be easily identified as repaired.
Hello Jesse!
If I understand your task correctly, the following formula should work for you:
=IF(G4="Y", IF(H4="X","Repaired","CORROSION"),"")
I hope this will help, otherwise please do not hesitate to contact me anytime.
Thank you sir works perfectly.
I am trying to figure out how to return one value in a cell by evaluating 6 other cells. For instance, if cell C2 has a valid value, return C2. If "NA" I need it to look at D2 and do the same evaluation and return the valid value if not "NA". So, column c2 = CAR1, columns d2 through h2 have NA. I want column B2 to say CAR1. If column d2 = CAR2 and column c2, e2 through h2 have NA. I want column b2 to say CAR2. I am guessing I need a string but cannot figure it out. Thanks!
Hello Frank!
If I understand your task correctly, the following formula should work for you:
=INDEX(C1:H1,1,MATCH("NA",C1:H1,0)-1)
I hope this will help, otherwise please do not hesitate to contact me anytime.
Hi I need a formula to state this
In there will be Yes or No. If its Yes then use cell A1 if its No then use cell A2
Hello,
Please try the following formula:
=IF([condition],A1,A2)
You can learn more about Excel IF statement in this article on our blog.
Hope this is what you need.
Sir i want if ((1 to 2 = 2 , 2 to 4 = 3, 5 to 9 = 5 ))how to make that on if logic in single cell if any argument Parameter in between 1 to 2 must showing 2 , argument Parameter in between 3 to 5 = 3
Hello!
If I understand your task correctly, please try the following formula:
=IF(AND(A1 >= 1,A1<= 2),2,IF(AND(A1 > 2,A1 <= 4),3,IF(AND(A1 >= 5,A1 <= 9),5,"")))
I hope it’ll be helpful.
Hello, I need your help. I have a formula that is trying to figure out over and under numbers to meet goals.
=IF($B$4<$C$1,"")
actual B4=20 and C1=15(goal)
If the number on B4 is less than 15, I have it giving us a blank, which works fine. But I cant figure out how to add another IF formula that will tell it to give me the number we are over our goal by. In this case, total is 20, goal is 15, answer should be 5. So how do I add an if to my formula?
=IF($B$415, XXX)
Hello Maria!
If I understand your task correctly, please try the following formula:
=IF($B$4 < 15,"",$B$4-$C$1)
I hope this will help, otherwise please do not hesitate to contact me anytime.
WOW! Thank you. Here I thought I needed a second IF statement. Thanks so much for your help. Maria.
I am very new to excel but I have a very detailed comparison I do manually on an excel sheet currently and I think you maybe able to help me.
I need to compare three columns and depending the message in those columns I need an out come.
Data:
A1 will contain update needed or blank
B2 will contain coordinator needed or blank
C2 will contain licensed or blank
Anytime column C2 equals Not Licensed no matter what any of the other columns say then I need column D2 to say Ineligible
If C2 is blank and b2 is coordinator needed and A1 is update needed the d2 needs to say coordinator and update needed
If c2 is blank and b2 is blank but A1 is update needed then I need D2 to say Eligible update
If all are blank then I need D2 to say No Action Needed
Thanks for any help !
Hello Diane!
If I understand your task correctly, please try the following formula:
=IF(C2="Not Licensed","Ineligible", IF(AND(C2="",B2="coordinator needed",A1="update needed"),"coordinator and update needed", IF(AND(C2="",B2="",A1="update needed"),"Eligible update", IF(AND(C2="",B2="",A1=""),"No Action Needed", "" ) ) ))
Hope this is what you need.
hi,
my requirement is, I have 3 columns. If column 1 value meets my requirement and the columen 2 text is "x", extract column 2 text. Else no. could you please help how I can write formula?
Hello!
You can learn more about about functions IF and AND in Excel in this article on our blog.
Hope you’ll find this information helpful.
if AH1=Conus and D1=12, then 17
if AH1=Conus and D1<12, then 15
if AH1=Oconus, then 28
Hello Jessica!
If I understand your task correctly, please try the following formula:
=IF(AH1="Conus",IF(D1=12,17,IF(D1 < 12,15,"")),IF(AH1="Oconus",28,""))
ok, i need to throw another variable in - can this be done?
if AH1=Conus and D1=12, AND G1=9820580 or 159384 ....then 17
if AH1=Conus and D1=12, AND G1=6620363 ....then 15
if AH1=Conus and D1<12, then 15
if AH1=Oconus, then 28
Hello Jessica!
Please try the following formula:
=IF(AH1="Conus",IF(AND(D1=12, SUM(IF(G1={9820580,159384},1,0))=1),17, IF(OR(D1 < 12,AND(D1=12,G1=6620363)),15,"")), IF(AH1="Oconus",28,""))
I hope it’ll be helpful.
WOW. thank you so much
Thank you so much!
Thank you sir for your attention, if B1"", C1"", & D1"", it should give me the value of both K1, L1 & M1 & so on.
"Please I need help on this;if B1"", it should give me the value of K1,"", if C1"", it should give me the value of L1,"", if D1"", it should give me the value of M1,"" and so on to about fifteen arguments. But when I input the formula, the Excel is telling me that I have input too many arguments. How can I resolve this please.
Thanks.
Hello Enity!
Unfortunately, you did not give me detailed explanations. What formula did you use? Are you checking the condition in 15 cells at the same time? In which cell do you want to write the result? What should this result look like? How is the sum of the values of K1, L1 & M1 and so on? What are 15 arguments if there are only 10 columns between columns B and K? I can assume the following formula:
=IF(SUMPRODUCT(--(B1=""),--(C1=""), --(D1=""),--(E1=""),--(F1=""),--(G1=""), --(H1=""),--(I1=""),--(J1=""))=1, K1,B1)
But this is just a guess. And I'm not a telepath.
I have two columns of data with the number set at 2 decimal places. column A for example in Cell A2 could read '10.2' and Cell B2 could read '11.3'. I want to be able to have cell C3 to state 'out of tolerance' if the range is outside of 10%. Is that possible?
Hello Shane!
You did not explain what it means "the range is outside of 10%". Maybe the following formula should work for you:
=IF((B2-A2)/A2>0.1,"out of tolerance","ok")
Expression (B2-A2)/A2>0.1 can be replaced with another.
I hope it’ll be helpful.
Hi I need to do a calculation total. I have a column called Quote/Won with one word in each row. I have a Labour £ column. I need to create a total of all labour revenue won. So if any figure in work labour column is won, the revenue figure shows in the total field. (single combined total field)
eg.
Total Labour revenue won £ 200
Won/Quoted Column---- Work Labour £ Column
Won £100
Quoted £150
Won £100
Hello Deb!
If I understand your task correctly, the following formula should work for you:
=SUMIF(A2:A7,"won",B2:B7)
You can learn more about SUMIF in Excel in this article on our blog.
Hope you’ll find this information helpful.
My apologies if you answered this already. I have a spreadsheet with Monday-Sunday in each column (A-G). State Names in Columns H-O. Unique People names in P column and their phone number in column Q.
Monday
Monday
Monday
Monday
Monday
Monday
I want to know who is available to travel on Monday to Utah.
I want the formula to provide me with a list of people and their phone number
Hello Sylvia!
I think that in your case the simplest and the most convenient way is to make use of Advanced Filter in your table instead of formulas. Please find advice on applying filters on our blog:
how to add filter, about advanced filter, highlight duplicates
Set a filter in the table and indicate criteria (what values you would like to see) in the necessary columns.
how to connect two condition under each have some conditions?
Hello!
See above Example 1. Using IF & AND function in Excel
I am trying to Populate a Date cell in L7 and I have two cells to choose from T7 and U7. I want L7 to be T7 if T7 has a date if not I want it to be U7 since that will always have a date. The Date in T7 will change due to plan optimization.
Hello Blaise!
If I understand your task correctly, maybe the following formula should work for you:
=IF(T7<>"",T7,U7)
If there is anything else I can help you with, please let me know.
hello, iam trying to counts pages based on if formaula, but i am not getting the answerer
If pages are 1 to 5 my answer should be 1
if more than 5 pages and less than or equal to 14 should be 2
If anything more than 14 pages my answer should be 3 - but this i am not getting
Here is my formula which i applied, can anyone suggest me on this
=IF(B25,"2",IF(B215,"3")))
Hello Ratheesha!
Please try the following formula:
=IF(B2>=1,(IF(B2<=5,1, (IF(B2<=14,2,3)))),0)
Hope you’ll find this information helpful.
I am trying to create a formula that will calculate the LTV (loan to value) I can use. Here is what I have so far.
=IF(D3>=850000, "75%", IF(D3>=500001, "80%", IF(D3<=500000, "85%")))
However, I want to add another two IF conditions that will increase each percentage by 5% if they have 740+ credit AND $120000 annual salary. Thanks in advance for the assistance!
Hello Yixo!
If I understand your task correctly, maybe the following formula should work for you:
=IF(AND(D4>=740000,D5>=120000), IF(D3>=850000, "80%", IF(D3>=500001, "85%", IF(D3<=500000, "90%"))),IF(D3>=850000, "75%", IF(D3>=500001, "80%", IF(D3<=500000, "85%"))))
Hope you’ll find this information helpful.
Hi,
Would the if function work for this?
Port/City Vancouver Montreal
Shanghai 27 34
Shenzhen 28 35
Guangzhou 35 42
Example:
Select: Shanghai, Vancouver
Time: 27 days
Select: Shanghai, Montreal
Time 34 days
Hello Jesse!
Please try the following formula:
=INDEX(B2:C4, MATCH("Shanghai",A2:A4,0), MATCH("Vancouver",B1:C1,0))
or
=INDEX(B2:C4, MATCH($E$2,A2:A4,0), MATCH($E$1,B1:C1,0))
I hope this will help, otherwise please do not hesitate to contact me anytime.
1)If= first month(1 Jan to 31 Jan) all floors commission 2%
2)If= second month (1feb to 29 Feb) floor wise commission
Lower floor - 3%
Middle floor - 3.5%
Higher floor - 4 %
3) if = third month ( 1 March onwards) onwards floor wise commission
Lower floor - 2 %
Middle floor - 2.5%
Higher floor -3%
Please help how to create formula in 1 cell in excel
Hello Swapnil!
if you write data -
A B C D E
Month 1 2 3 2/10/2020
Lower floor 2 3 2 Middle floor
Middle floor 2 3.5 2.5
Higher floor 2 4 3
Please try the following formula:
=INDEX(B2:D4,MATCH($E$2,A2:A4,0), MONTH(E1))
I hope this will help, otherwise please do not hesitate to contact me anytime.
sorry I do not understand of this
A1 - Month, A2 - Lower floor, A3 - Middle floor, A4 - Higher floor, B1 - 1, B2 - 2, B3 -2, B4 - 2, C1 - 2, C2 - 3, C3 - 3.5, C4 -4 and so on. E1 - 2/10/2020 , E2 - Middle floor
please help its very urgent
Hello!
If you want to learn something, try to understand how the formula works, and not ask for help all the time.
=IF(E1 < DATE(YEAR(TODAY()),3,31), (INDEX(B2:D4,MATCH($E$2,A2:A4,0), IF(E1 < DATE(YEAR(TODAY()),2,3),1,IF(E1 <= DATE(YEAR(TODAY()),2,29),2, IF(E1 <= DATE(2021,3,31),3,0))))), "Date out of range")
If you write table
Column a : name of customer
Column b : source
Column c : source name
Column d : value
Column e : commission %(2%)
Column f : need formula this column
Source type 3
* broker
* Ref
* direct sale
Commission type
1)If broker sale 1 product get 2% commission ( value * 2%)
2 ) if any existing sale his ref through 1 product get amount benefit
* sale 1nd product deal get 10000 rs
*sale 2nd prduct deal get 20000 rs
* sale 3 rd product deal get 30000rs
And
3) if direct sale get 0% commission
Please help me how to merge in one cell (f column) formula
Thank you very much it's working
but one more problem one Month Condition was changed. Condition is Date of period so formula not working...
*Date of period
1 ) 1 Jan 2019 to 2 Feb 2020 -commission all floors 2%
2 ) 3 Feb 2020 to 29 Feb 2020 - commission floor wise( L, M, H) 3 %, 3.5%,4%
3 ) 01 March 2020 to 31 march 2021* commission floor wise (L, M, H) 2%,2.5%,3%
Can you please help .....
Hello Swapnil!
Please try the following formula:
=IF(E1 < DATE(YEAR(TODAY()),3,31), (INDEX(B2:D4,MATCH($E$2,A2:A4,0), IF(E1 < DATE(YEAR(TODAY()),2,3),1,IF(E1 <= DATE(YEAR(TODAY()),2,29),2, IF(E1 <= DATE(YEAR(TODAY()),3,31),3,0))))), "Date out of range")
Hope you’ll find this information helpful.
Please can someone help me with an excel formula?
3 sources of get commision
1)Comany
2) Customer ref
3) Direct sale
1) If multiple company commission for 2 %
2)if customer ref commision
*first ref commission rs 10k
*Second ref commission 20k
And
*Third ref commission 30 k
3) Direct commission "0%"
How to get commission formula in one cell
And one more condition add
3 types of sources in this
X, y, z
X and z eligible for all condition commission
But y not eligible for all. Only eligible 2% commission in all types of condition
Please help....
Formula not working on pending period 01 april 2020 to 31-03-2021 ...working only march 2020 ..
Third condition not completed.. Please help.
=If(and(A2="A",B2="A" or "B"),"Yes","No")
Using like this in excel, if A2 value is A, and B2 value is A or B, the result I want as Yes.. Please clarify
Hello
Please try the following formula:
=IF(A2="A",IF(OR(B2="A",B2="B"), "Yes","No"),"No")
Hope you’ll find this information helpful.
hoping someone can help
im trying to sort my lab charges
if i have in column c I have "cbc" i want column e to have price for cbc which is $8.98
if in column c i have "bmp" then column e should show $14.56
etc
i have about 60 different labs and pricing. what is easiest way to do this so i dont have to manually enter the price each time
thanks!
Hello Aracely,
Please try a VLOOKUP formula. You can learn more about VLOOKUP in Excel in the above linked article on our blog.
Hope you’ll find this information helpful.
I have a spreadsheet with rows of data results for 5 tests that have been done and as such if the test has been done would expect to see either a PASS or a FAIL in each cell. However if one of test in that row is not done then that particular cell would be blank. Now I want to add a column which looks at each entry in the row and if any fails then it will remind the user to add some additional information, have tried using logic but I cannot seem to get it to ignore the cell if it is blank. Thus I am after something to solve the following
PASS, PASS, PASS, PASS, PASS = No Action required
PASS, " ", PASS, PASS, PASS = No Action required i.e ignore any blanks
PASS, PASS, FAIL, PASS, PASS = Add addition Info as it sees a FAIL
PASS, " ", FAIL, PASS, PASS = Add addition Info, again as it sees a FAIL
Anyway any help would be greatly appreciated as this is doing my head in...
Hello Andrew!
If I understand your task correctly, the following formula should work for you:
=IF(SUM(--(A1:E1="PASS"),--(A1:E1=""))=5, "No Action", "Action")
I hope this will help, otherwise please do not hesitate to contact me anytime.
Hi Alexander,
Firstly thanks for the response, much appreciated. Now apologies, my fault as I should have probably mentioned that there were other cells between each of the entries which contains other information as such I need to look at each of these cells individually and then make a collective decision on the action, thus the spreadsheet breaks down as follows:
Cell 1 = Numeric data, Cell 2 = Time(hh:mm:ss), Cell 3 = Text, Cell 4 = Text & Cell 5 = Result (PASS/FAIL/"")
This then repeats itself another 4 times as each row has 5 probable tests, thus all I want to do is look at Cell 5 of each test and from those entries ascertain whether there is an action to be carried out for that row. i.e. if they are all PASS or blank then no Action is necessary, however if a FAIL is seen then ACTION is required.
Hope this makes sense and apologies for messing you about as I know you guys are likely busy.
Hello Andy!
Change the formula
=IF(SUM(--(E1="PASS"),--(E1=""), --(J1="PASS"),--(J1=""),--(O1="PASS"),--(O1=""), --(T1="PASS"),--(T1=""), --(Y1="PASS"),--(Y1=""))=5, "NO Action", "Action")
I hope it’ll be helpful.
Hi. Please help
I have 23,000 in income. I need to pay 0% in the first 2,000.
3% up to 5,000.
4% from 5,ooo to 10,000
5% on anything above 10,000
I need all the calculation in one formula.
Hello Marina!
Hello
If I understand your task correctly, the following formula should work for you:
=((A1-10000)*0.05)*(A1>10000) + ((A1-5000)*0.04)*(A1>5000)*(A1<10000) + (5000*0.04)*(A1>=10000) + (A1*0.03)*(A1<5000) + (5000*0.03)*(A1>=5000)
I hope this will help, otherwise please do not hesitate to contact me anytime.
In other words, we don't pay anything in the first 2,000 and we pay 3% from 2k up to 5K.
Thank you so much again
Hello Marina!
Please use the following formula
=((A1-10000)*0.05)*(A1>10000)+((A1-5000)*0.04)*(A1>5000)*(A1<10000)+(5000*0.04)*(A1>=10000)+((A1-2000)*0.03)*(A1<5000)*(A1>2000)+(3000*0.03)*(A1>=5000)
Hi Alexander. You are awesome. Thanksfor the help. I review and everything looks good except the last part of the formula. That should be 0% anything 2,000 or less and from 2,000 to 5000 is 3%. We are supper close.
650 ((A1-10000)*0.05)*(A1>10000) correct
200 ((A1-5000)*0.04)*(A1>5000)*(A1=10000) correct
150 (A1*0.03)*(A1=5000) review
what formula should I write here?
90-94, with honors
95-97, with high honors
98-100, with highest honors
Please help.
Hello!
Please use the following formula
=IF(A1 >= 90,IF(A1 >= 94,"Honor",IF(A1 >= 97, "High Honor", "Highest Honor")),"")
I hope this will help, otherwise please do not hesitate to contact me anytime.
How to write the formula of if cell contains the range ±0.25 then return YES otherwise return NO in the Excel.
Hi Samy,
Assuming you are comparing cell B1 against A1, the following formula will return "yes" if B1 equals A1±0.25, otherwise "no":
=IF(AND(B1>=A1-0.25, B1<=A1+0.25), "yes", "no")
Hi..pls help me for the below conditions.
condition 1: wen entering D4 value =A, then E4 value should be=A
condition 2: wen entering D4 value =blank, then E4 value should be =blank
condition 3: if D4A and D4blank, means it has some data. so that time E4 should be "B".
Give me formula for above condition.
Thanks..
Hi Praveen,
If I understand your task correctly, the following formula should work for you:
=IF(D4="A", "A", IF(D4="", "","B"))
I'm trying to embed a MID statement for the answer if true. My forumula isn't working, however: =IF(B2=55,(mid(A2,4,2),B2).
Any ideas?
Hello, Annie!
Please try the following formula:
=IF(B2=55,MID(A2,4,2),B2)
Hi,
if it is in excel workbook in one sheet >95 and in other sheet <95 written there, so how we can take average of that( with sign).
please help me to resolve this issue.
Regards,
Vivek
Hello!
If I understand your task correctly, the following formula should work for you:
=IF(OR (AND(Sheet1!A1<95,Sheet2!A1>95), AND(Sheet1!A1>95,Sheet2!A1<95)), AVERAGE(Sheet1!A1,Sheet2!A1),0)
I hope it’ll be helpful.
Please will you give me the function IF in a cell C3 depending of a value in cell B3 with two values , as follows:
in cell b3 with only two values conditions TRUE, with B3=1 or B3=3, in order that:
-for the TRUE two conditions: if B3=1 to give in cell C3 the value effect 5 , and
if B3=3 to give in cell C3 the value effect 10
-for the FALSE condition, if B3 has different value of 1 or 3, to give in cell C3 the value effect 0 (zero).
Thank you.
Hello Dim!
If I understand your task correctly, the following formula should work for you:
=IF(B3=1,5,IF(B3=3,10,0))
If there is anything else I can help you with, please let me know.
Hi
Please help me..
My cell values are as follows:-
A1 B1 C1 D1
62 31 0
In cell D1 I need to use a formula that will calculate the value in A1 if C1 =0, but if
C1 >0, then I need to SUM B1+C1
hi.
try this
=IF(C10,C1+B1,0)
Hi - I am trying to work out how to write a formula for the following:
If cellvalue1, "No"
If cellvalue is blank, ""
I have got the yes and no working by using this formula: =IF(E7>1,"No","Yes") but at the moment if there was nothing in cell E7, the cell I have the formula in shows "Yes" and I really want it to be blank (i.e. only say yes or no where there is an actual value in E7). Hope someone can help!
Thanks
kirstie
Hi Kristie, did you find any solution to this? I am in the same condition, looking for that function
I want multiple of this formula =IF(SUM(COUNTIF(A3,{"*ANCHORAGE*"})),"Alaska",IF(SUM(COUNTIF(A3,{"*Atlanta*"})),"Georgia"))
for several cities, but excel does not let me do more than two IF statements, is there another way to do multiple if statements in one formula?
You can nest as many IF statements as you'd like in Excel.
Just follow the formula:
=IF(Condition,True,IF(Condition,True,IF(...,False)...)
You're just replacing the False part of the IF statement with another IF statement every time. If the first condition isn't true, then it checks for the second, and then the third, etc.
Hello,
I want to calculate an expiry date of a product. The products either have a 5year or 10year working life depending on whether its construction is "rubber" or "synthetic". Over three columns I have MATERIAL, DATED FITTED, EXPIRY DATE. If the material is rubber then I enter =DATE(YEAR(K6)+5,MONTH(K6),DAY(K6)) in the EXPIRY DATE. If the material is synthetic I enter =DATE(YEAR(K6)+10,MONTH(K6),DAY(K6)). Is there a way to make the EXPIRY DATE Column decide automatically how many years to add depending on the contents of the MATERIAL column?
I'm Really struggling with this.
Many thanks.
Tom
hello!
try using if formula for the solution like
=if(A2= "rubber", whole condition with 5 years expiry, if(A2="synthetic", whole condition with 10 years expiry,"")
I have an IF formula =IF(D8="X","1","") and now I want to add up the lines that have a 1 in them .. what type of formula will I need?
Take the quote out:
=IF(D8="X",1,0)
Hi Brad did you get any solution for that? I am also looking for the same.
Try =COUNTIF([Your Desired Range],"1")
Is it possible to write a formula for a whole column?
IE, Currently column 'C' divides 'D' but i need a formula that If C>D divide D by C.
Hi Gary,
Usually, you write a formula for the first cell using relative references (without $ sign), and then drag it down to as many cells as needed. This will copy the formula across the column and adjust the references for each row. For example, if your data begins in row 2, enter this formula in any cell in that row:
=IF(C2>D2, D2/C2, "")
And then, double-click the fill handle (a small square at the lower right-hand corner of the cell) or drag it over the cells where you want to copy the formula.
For more information, please see How to copy formula in Excel.