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 47. Total comments: 4557
Hi,
I am trying to put in a formula that will have 6 awnsers depending on a drop down which has 3 choices and another drop down which is a yes/No drop down. But i cant get it to work im using the IF And fuctions. I you can help it would be much appreciated.
=IF(AND($P$4="YES",H4="STANDARD"),'Support Item Name'!C2:C8,IF(AND($P$4="YES",H4="INTENSITY 2"),'Support Item Name'!C9:C15,IF(AND($P$4="YES",H4="INTENSITY 3"),'Support Item Name'!C16:C22,IF(AND($P$4="NO",H4="STANDARD"),'Support Item Name'!C23:C29,IF(AND($P$4="NO",H4="INTENSITY 2"),'Support Item Name'!C30:C36,IF(AND($P$4="NO",H4="INTENSITY 3"),'Support Item Name'!C37:C43))))))
Hello Donald!
I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Describe in detail all the conditions that you use. What result corresponds to each of the conditions? It’ll help me understand it better and find a solution for you. Thank you.
I want to calculate a Reorder Level for Inventory.
The Formula in text is:
[Opening Inventory + Material Received - Material Sold = Closing Inventory]
Closing Inventory cannot be negative and a particular material should be ordered only if
the Closing Inventory falls below a particular level, say below 5kg.
Hello!
If I understand your task correctly, the following formula should work for you:
=IF(Closing Inventory<5,"Reorder",Closing Inventory)
=IF(AND([@[PO Number]]"",[@[Fob Date.]]"",[@[Invoice No.]]"",[@[Onboard Date]]="",[@[BL NO.]]="",[@[Doc''s Sub HSBC]]=""),"NEED GSP SUBMIT",IF(AND([@[PO Number]]"",[@[Fob Date.]]"",[@[Invoice No.]]"",[@[Onboard Date]]"",[@[BL NO.]]"",[@[Doc''s Sub HSBC]]=""),"BANK DOCS PENDING",IF(AND([@[PO Number]]"",[@[Fob Date.]]"",[@[Invoice No.]]"",[@[Onboard Date]]"",[@[BL NO.]]"",[@[Doc''s Sub HSBC]]""),"PROCESS OK","WARNING")))
Please clarify above formula logic I do not understand
Hello!
Unfortunately, without seeing your data it hard to give you advice.
The formula uses named ranges and references to an Excel spreadsheet.
In many cases, it’s easier to write your own formula than to try to understand someone else’s formula.
Hello, thank you and Please help:
=IF(R3219="Shopify Payments",(N3219*0.965-0.3), OR(R3219="Stripe Connects",(N3219*0.971-0.3)))
If shopify payment on Cell R3219, multiple N3219 by 0.965 minus 0.3 cents, but if R3219 is stripe connects, multiple N3219 by 0.971 and minus 0.3 instead.
Thank you
Hello Philip!
If I understand your task correctly, the following formula should work for you:
=IF(R3219="Shopify Payments",N3219*0.965-0.3, IF(R3219="Stripe Connects",N3219*0.971-0.3,0))
I hope this will help
Hello, I am trying to combine some if statements with index match (Exact Formula Below) and have a mostly working formula except I would like to return a value if there is no value in the returning cell. Basically I am getting "1/0/1900" or "0" when the formula finds an empty cell. I am not sure if the if(len( can be added to the below? If so I am not sure where to place it :)
=IFERROR(IF($A2="","ADD SERIAL",INDEX('RACS 06-15-2020'!CS:CS,MATCH($A2,'RACS 06-15-2020'!$M:$M,0))),"NOT IN RACS")
Hello Josh!
Unfortunately, without seeing your data it hard to give you advice.
Perhaps this IF formula will replace 0 with another value.
=IF(IFERROR(IF($A2=””,”ADD SERIAL”,INDEX(‘RACS 06-15-2020′!CS:CS, MATCH($A2,’RACS 06-15-2020’!$M:$M,0))),”NOT IN RACS”)<> 0,IFERROR(IF($A2=””,”ADD SERIAL”,INDEX(‘RACS 06-15-2020′!CS:CS, MATCH($A2,’RACS 06-15-2020’!$M:$M,0))),”NOT IN RACS”),"Value")
I hope it’ll be helpful.
5. Brittany wants to identify employees who are eligible to take a CPR course at the clubs' expense. Employees who can work as camp counselors are eligible for the course. In cell M3, enter a formula using a nested IF function as follows to determine first if an employee has already been trained in CPR, and if not, whether that employee meets the qualifications to take the course:
a. If the value in the CPR Trained column is equal to the text "Yes", the formula should display Trained as the text.
b. Otherwise, the formula should determine if the value in the Camp Counselor column is equal to the text "Yes" and return the text Yes if true and No if false.
Hello!
Read the article above carefully. It has all the necessary information on using the IF function. You will be able to complete your task.
I need to create a formula that will determine the % Error based on a low and high range for the % of error. In my spreadsheet, I have a table (shown below) that has the % of Error and ranges assigned as follows. If I want to determine the % of error on a cell value that is 29, I need to create a formula that will check all of the potential ranges in the table to find that the right answer is 20%. I have tried to create a formula using IF and AND, but can't quite get it to work. Any help is appreciated!
% Error Low High
10% 36 45
20% 27 35
40% 18 26
60% 9 17
80% 5 8
If cell A value >50000,1% rebate is obtained. If cell A value >100,000,2% rebate , >200,000,2.5% Rebate , >300,000 3% rebate is obtained
Hello!
See here the solution to the same problem
I have two columns. If there are duplicates in column A of a certain namex, column B MUST be the same for each duplicate with a true/false as the output. Example
A1 B1
123 t2
123 t2
I have over 6000 rows and have tried to use two if statements but this entails searching for the duplicates of each name which is tedious as there could be human errors and I could miss one. There is not an equal amount of duplicates for each input in column A either. I also don't know what column B should be. All i know is that they should be equal for the each duplicate of that type in column A. Let me know if you have questions
Hello Blanca!
Your explanation is not very clear. Manually working with so much data is very difficult. Therefore, I recommend using the ready-made solution Ablebits Data - Duplicate Remover.
This tool is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and use for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial
I want a function that work like this
I have this options (Invoice, Payment, Credit note, Expense) as a drop-down-list in Cell A1
entering an Amount on Cell B1 and expect the change in Cell C1 where the balance will change when an option is selected at cell A1
1. Invoice, the amount in cell B1 will add up to the balance Amount in cell C1
2. if other options are selected the Amount in B1 will reduce the value in Cell C1
Hello Gabriel!
If I understand your task correctly, the following formula should work for you:
=IF(A1="Invoice",B1+C1,C1-B1)
The formula can be written in D1
D71521151 22/05/2020 0.02
D71519430 20/05/2020 0.27
D71520950 22/05/2020 0.15
D71520985 22/05/2020 0.40
D71522327 28/05/2020 0.32
Third coloumn value is weight and if i want to get result in 4th coloumn with conditions that if weight is <.1 then 50, If it is between .1 to .25 then 100 and if it is .25 to .5 then 150.
How we can do this???
Please suggest.
Hello!
To select the third value from a cell, use the formula
=--TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",20)),20))
Then use the recommendations from this article to create the IF formula
for every full R100 due(amount after discount),the manager gives R12 to a charity fund .
form an if statement ..
I need help
Hello!
If I understand your task correctly, to calculate the amount of contributions to a charity fund, use the formula
=INT(Q100/R100)*R12
Q100 - Sales Amount
Hope this is what you need.
Greetings,
i seeking you kindly support and assistance on below table range, how can i use the IF Function on below.
if a transaction value 40000, the first 2999 to be rewarded @ 0.50@ and 3,000 -9,999 @ 1% and so on as on below table.
Spend Range Domestic Cashback % International Cashback %
0-2,999 0.50% 1.00%
3,000 -9,999 1.00% 1.50%
10,000-19,999 1.25% 2.00%
20,000-39,999 1.50% 2.50%
>=40,000 3.00% 5.00%
See here the solution to the same problem
I have multiple products in column b. What I want to do is in Column A - I need to be able to have only 3 product labels namely Alpha, beta and charlie.So for example all products in column b having the word alpha in their product name will be labeled as alpha. Then for all products with beta labeled as beta and for the rest which does not meet the criterias will be labeled as charlie..checking for your asssistance and thanks in advance.
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 a nominal concentration with +- 15% acceptance criteria.
Some data generated (at least 500), in which some are within acceptance criteria and some are out of acceptance criteria.
I need to calculate, mean, SD, CV and nominal after including and excluding out of acceptance criteria values.
1 2 3 = =IF(A2,NUMBERVALUE(A2),B2) = Pass
2 3 = =IF(A3,NUMBERVALUE(A3),B3) = Pass
3 = =IF(A4,NUMBERVALUE(A4),B4)*(IF(B4,NUMBERVALUE(B4),C4)) - Fail
Can you please let me know why 3rd formula is not working.
Hello!
I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? In your data, B4 = 0? C4 = 0? Formula 3 returns 0. What value should formula 3 return? What result would you like to get with these formulas?
Please be so kind and support me with the formula:
=IF(AND($K13;"";$N13;"";$O13;"");"No Risk";IF(AND($K13;"";$N13;"";O13;"");"Middle Risk"))
Somehow it doesn't work and I get only "No Risk" based on first logical test.
Thank you
Hello Olya!
You used the same formulas for different IF conditions. Maybe you should use something like this formula
=IF(AND($K13="",$N13="",$O13=""),"No Risk", IF(AND($K13<>"",$N13<>"",O13<>""),"Middle Risk",""))
I hope this will help, otherwise please do not hesitate to contact me anytime.
Hi
I am trying to get multiple areas to display based on 2 or 3 chars. Tried this below formula but they are not returning the right Areas.Column F2 has a about 7-8 chars and i want to search the first 2 and 3 chars. Can you please help?
Hope this makes sense. Thank you!
=LOOKUP( LEFT(F2, 3), {"AL,N1,N2,N3,NW,EN","SL9,SL0,WD,HP","SG,LU,CM,CB,RM","GU,TW,KT,SL5,SL4","SL3,HA,UB"}, {"Area 1","Area 2","Area 3","Area 4","Area 5"})
Below is the criteria
GU Area 4
TW Area 4
KT Area 4
SL9 Area 2
SL3 Area 5
SL0 Area 2
SL2 Area 5
SL5 Area 4
SL4 Area 4
SG Area 3
LU Area 3
CM Area 3
CB Area 3
RM Area 3
WD Area 2
HA Area 5
UB Area 5
HP Area 2
EN Area 1
NW Area 1
N1 Area 1
N2 Area 1
N3 Area 1
AL Area 1
Hello!
If I understand your task correctly, the following formula should work for you:
=CHOOSE(MATCH("*"&LEFT(F2, 3)&"*", {"AL ,N1 ,N2 ,N3 ,NW ,EN ","SL9,SL0,WD ,HP ","SG ,LU ,CM ,CB ,RM ","GU ,TW ,KT ,SL5,SL4","SL3,HA ,UB "},0), "Area 1","Area 2","Area 3","Area 4","Area 5")
I hope this will help
This worked. I just had to change the F2,2 and will just have to ignore the 3 chars as this will complicate allot of it more. Thanks you again Alex.
I am trying to make a column populate a specific rate based on criteria in the two columns before it.
Column 1 is their name
Column C is their insurance (BLTC or MCLTC)
Column D is the level assigned (Level 1, Level 2, level 3)
Column E is the rate per day we receive based on insurance and level as they are distinct. For example if you have BLTC and are a level 2 it would be $207.31. If you are MCLTC and level 2 is would be $200.07. I would like column 4 to auto populate based on insurance and level as the rates are specific by insurance and level.
Can anyone help me with this? Thanks in advance!
Hi I'm having troubles nesting all 4 formulas together. Is it possible? Please help!
1) =IF(AND(BF2>=62,BH2>=5),"ELIGIBLE TO RETIRE", "not eligible to retire")
2) =IF(AND(BF2>=60,BH2>=20),"ELIGIBLE TO RETIRE", "not eligible to retire")
3) =IF(AND(BF2>=55,BH2>=30),"ELIGIBLE TO RETIRE", "not eligible to retire")
4) =IF(AND(BF2>=55,BH2>=10),"ELIGIBLE TO RETIRE", "not eligible to retire")
Example #1 (BF=Age)Age 63 with BH=yrs of svc)15 years of service - should be true for both 1 & 2 arguments
Example #1 (BF=Age)Age 57 with BH=yrs of svc)15 years of service - should be true for argument 4
Hello!
If I understand your task correctly, the following formula should work for you:
=IF(OR(AND(BF2>=62,BH2>=5,AND(BF2>=60,BH2>=20),AND(BF2>=55,BH2>=10))),"ELIGIBLE TO RETIRE", "not eligible to retire")
I hope it’ll be helpful.
I figured it out. Just needed to read an earlier post you provided to someone else!! Thank you for all you do! All of you Excel gurus!! Have a great day!
=IF(AND(BF2>=62,BH2>=5),"ELIGIBLE TO RETIRE",IF(AND(BF2>=60,BH2>=20),"ELIGIBLE TO RETIRE",IF(AND(BF2>=55,BH2>=30),"ELIGIBLE TO RETIRE",IF(AND(BF2>=55,BH2>=10),"ELIGIBLE TO RETIRE","not eligible to retire"))))
Hi,
I really need your help, identifying the proble with my formula.
if value range of is as bollow,
For income range 20,000 to 29,999 ratio is 35% for type A, 30% for type B.
and income range 30,000 to 49,999 ratio is 40% for type A, 35% for type B.
Here,
D9 = location of the cell which is a dropdown menue whether to choose A/ B
G22 = location of the cell which is a used for input ranging from 0 - 50000
H127 =
=IF(AND(G22<30000,D9="A"),.35,IF(AND(30000<=G22,G22<50000,D9="A"),.40,IF(AND(G22<30000,D10="B"),.30,IF(AND(30000<=G22,G22<50000,D10="B"),.35,))))
Thanks
Nizam
Hello!
If I understand your task correctly, the following formula IF should work for you:
=IF(AND(G22 > 20000,G22 < 30000),IF(D9="A",0.35,IF(D9="B",0.3,0)),IF(AND(G22 > 30000,G22 < 50000), IF(D9="A",0.4,IF(D9="B",0.35,0)),0))
I hope it’ll be helpful.
Hi,
I need your help with the below condition.
If Cell B7=30000 then B9 should be 4(this value is in the cell H10)
If Cell B7=50000 then B9 should be 4(this value is in the cell H11)
If Cell B7=100000 then B8 should be 4(this value is in the cell H12)
If Cell B7=150000 then B8 should be 5(this value is in the cell H13)
If Cell B7=200000 then B8 should be 5(this value is in the cell H14)
If Cell B7=330000 then B8 should be 4(this value is in the cell H15)
If Cell B7=500000 then B8 should be 4(this value is in the cell H16)
Kind regards
Mohsin
Hello!
If I understand your task correctly, the following formula IF should work for you:
=IF(B7=30000,H10, IF(B7=50000,H11,IF(B7=100000,H12,IF(B7=150000,H13,IF(B7=200000,H14, IF(B7=330000,H15, IF(B7=500000,H16,"")))))))
I hope it’ll be helpful.
Hello Alexander,
I want to decide the candidate is eligible or not, if the cell is contain NA or date ..how to write formula
I am trying to calculate commission income. If gross commission is >220,000 then the net amount of commission they earn goes up. Here are my conditions:
0-220,000 - .60
220,001-440,000 - .65
440,001-660,000 - .70
660,001-880,000 - .75
880,001+ - .80
This is the formula I'm working with but it keeps coming back as #VALUE! =IF(OR(BR125>220001,BR125440001,BR125<=660000),BR8*BR4,"")
What am I doing wrong?
Thank you so much for your help, I'm ready to pull my hair out.
Megan
Hello Megan!
If I understand your task correctly, the following formula IF should work for you:
=IF(A1>880000,B1*0.8, IF(A1>660000,B1*0.75, IF(A1>440000,B1*0.7, IF(A1>220000,B1*0.65, B1*0.6))))
Hope this is what you need.
You are a life saver! Guess I was making this harder than it needed to be. Thanks for getting me on the right track and thank you for taking the time to help all of us!
Hi Can someone help me please?
I am trying to put a formula for example if the Price is let's say £100 to be split into three columns like column one will be 0-£50
Column two will be for anything that is above £50 but less or equal to £60
Column three anything that is above £60 but less or equal to £70.
Column four anything about £70.
If any of the conditions don't meet let's say if column two is less than £50 to bring 0.
Any help is much appreciated.
Mita.
Hello Mita!
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.
Hello, can you kindly help me with this formula? Been stuck on this for hours.
Total amount: Cell E29
Discount: >3000 -10% >5000 -15% >10000 -20%
Formula written: =IF(E29>=3000,"E29*-0.1", IF(AND(E29>=5000,"E29*-0.15", IF(AND(E29>10000,"E29*-0.2","0")))))
Thankyou for helping! Your help is very much appreciated!
Hello Jerelina!
The formula below will do the trick for you:
=-IF(E29>=10000,E29*0.2, IF(E29>=5000,E29*0.15, IF(E29>=3000,E29*0.1,0)))
I hope this will help
Hello, I am trying to accomplish a formula that is evaluating multiple cells to return a specific answer. I am using if/and but I cannot seem to get a does not contain to work? Here is my formula, the AE2 part is where it is failing. Any ideas?
=IF(AND(K2="Parent",AQ2="Chassis",AE2"*DECOMM*"),"Chassis/Parent","NOT Chassis/Parent")
Supposed to work a.... K2 = Parent and AQ2 = Chassis and AE2 does not contain DECOMM then return the false/positive value.
Hello Josh!
If I understand your task correctly, the following formula should work for you:
=IF(AND(K2="Parent",AQ2="Chassis", ISERROR(FIND("DECOMM",AE2,1))),"Chassis/Parent","NOT Chassis/Parent")
I hope it’ll be helpful.
Alexander, Thank you so much this worked perfectly!
If column A is 1 then cell X If Column b is 1 then Cell Y otherwise C*D.
Any help?
Hello Sean!
Your conditions contradict each other. What if both column A = 1 and column B = 1? If both conditions are met? Read carefully the IF function with multiple conditions above.
=IF(E2>=70, "Excellent", IF(E2>=60, "Good", IF(E2>40, "Satisfactory", "Poor ")))
What are the best alternatives for this formula?
I tried = If(AND...), / If(OR...) but couldn't get the expected result.
Little help will be well appreciated.
Hi, I really need help to add a final condition to this formula below, I have tried a few different things and just keep getting errors so any input would be great if it is possible
=IF(AND(C3=$A$3,I3<1,"Y","N") BUT IF M3="Transfer Debit"=N
Hello Sabina!
I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail?
What does it mean "IF M3="Transfer Debit"=N"??
Thank you!
Hi Alexander, thanks for your quick response so basically I need to add an extra criteria to the formula. The first part is fine but the extra condition is basically but if cell M3 contains the text Transfer Debit change it to N... is this even possible? Or would it be a completely different formula?
(summary of formula - If column C = A and column I < 1 = Y but if M3 = Transfer Debit N and everything else N).. hope this makes sense, sorry I'm not great at excel so might be why i am not very clear.
Hello Sabina!
If I understand your task correctly, the following formula should work for you:
=IF(M3="Transfer Debit","N", IF(AND(C3=$A$3,I3 < 1),"Y","N"))
I hope this will help
Thank you so much, it worked!!!
Total Taxable amount USD 10,00,000/-Tax free 3,00,000/- reducing balance 7,00,000/- 1st slab amount 1,00,0000/- How will i show in the row by formula 1,00,000/- next row 2,00,000/- next row 4,00,000/- (Auto reducing method not)
I will be grateful if you help me
YASIN
Hello!
I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
It’ll help me understand it better and find a solution for you. Thank you.
Please help me, to generate IFs formula: given the situation, that I have 3 cases and fall into 80,000, the percentage should be 9% because it is below 100,000. Please help in formulating formula. I tried several times but I can't.
Below are the conditions to be met.
CASES 50000 75000 100,000 150000 200,000 250000
1 2% 5% 10% 15% 21% 27%
2 3% 7% 13% 20% 28% 35%
3 4% 9% 17% 27% 35% 45%
4 4% 9% 17% 27% 35% 45%
5 4% 10% 19% 30% 38% 47%
6 4% 10% 19% 30% 38% 47%
7 5% 12% 22% 32% 40% 50%
Hoping for your usual support on the matter.
Hello Joe!
You need to use the function NDEX MATCH with multiple criteria in rows and columns. Read these instructions in detail.
If you need any further assistance, please don’t hesitate to ask.
Hi, I want to take a table where column A is a simple numbered list, 1-10, and column B is the value corresponding to the number to it's left in that row, in column A. Then I want to automate my spreadsheet so that when I enter any number, 1-10, in column C, it returns the correct value from the table. I know how If and OR and INDEX work, but I cannot figure out how to create the command, which I am assuming will be a string of 10 nested commands, such as for my 1st entry in C1: "If(OR(A1=1,"B1"),(A1=2,"B2"),(A1=3,"B3"))...etc". Thanks!
Hello Peter!
if you entered a number in cell C1, you can replace it with some other value only using the VBA macro. A cell can contain either a value or a formula. Depending on the value of C1, cell D1 can be changed.
Hi I am working on a shift schedule where we have different shifts namely as follow:
Shift: 1 - 06h00 - 14h00 = 6hours
Shift: 2 - 14h00 - 20h00 = 6hours
Shift: 3 - 20h00 - 06h00 = 10hours
Shift: 4 - 08h00 - 17h00 = 9hours
Shift: + - 06h00 - 10h00 & 16h00 - 20h00 = 8hours
Shift: N - 18h00 - 06h00 = 12hours
or if employee is off then it will be a Letter O and that should equal to 0
So the idea is that should an employee work a shift on say block C9, whether it is any of the above shifts that it automatically gives the hours on the on say block K9.
So I tried the following formula but it simple does not work.
=IF(C9=1;6;0)OR(IF(C9=2;6;0;)(IF(C9=3;10;0)(IF(C9=4;9;0)(IF(C9=+;8;0)(IF(C9=N;12;0)(IF(C9=O;0;0)
Not to sure if I explained it correctly, but hope you can help with this.
Hello Dwayne!
If I understand your task correctly, the following formula should work for you:
=IF(OR(C9=1,C9=2),6, IF(C9=3,10, IF(C9=4,9, IF(C9=""+"",8, IF(C9=""N"",12, IF(C9=""O"",0,0))))))
Hope this is what you need.
Hi,
Can you help for following condition.
If P3 is >0.05 then P3-0.05
If P3 is <-0.05 then P3+0.05
if P3 is in between 0.05 and -0.05 then PASSED
HOW TO WRITE FORMULA
THANKS :)
Hello Charles!
If I understand your task correctly, the following formula IF should work for you:
=IF(P3 > 0.05,P3-0.05, IF(P3 < -0.05,P3+0.05, "PASSED" ) )
I hope this will help
=IF(P3>0.05,P3-0.05,IF(P3<-0.05,P3+0.05,"Passed"))
=IF((AND(H6,H8,H11)="Valid"),"Valid","Invalid")
Can you please identify the problem
I am trying to pass on a text" Valid" when all 3 cells display "Valid" otherwise "Invalid"
Is there a way that I can combine an IF with an AND and OR functions?
Here are the two that I need help combining:
IF(AND(A1="fruit",B1="old"),C1*70%,C1*55%)
IF(AND(A1="veggie",B1="old"),C1*50%,C1*40%)
Hello!
These two formulas cannot be combined into one, since they contradict each other. If A1 = ”fruit”, B1 = ”new”. These values mean FALSE in both formulas. What should the formula return - C1 * 55% or C1 * 40%?
That's what I was afraid of... -_-
Thank you for your help!
Dear Alexander,
I am sorry for confusing you,
I have four columns: Gender(there it is written Male and Female), than Score(Percent of score written), Productivity(number written) and empty column named(Good boy student/good girl student)
The task is to make formula which checks if it is a male of female, than if the score is more than 70% and if the productivity is more than 0.5. If all conditions is met it should write in column "Good boy student/good girl student" that it is a good boy student if it is Male and a good girl student if it is Female.
Is it also possible that if conditions are not met it would leave blank or write N/A in the cell?
Best regards
Hello,
I have three different strings(Gender(Male/Female), Score and Productivity.
My task is to tel that if it is Male and score is more than 70% and productivity is more than 0.5 it is a good boy student, otherwise it is a good girl student. How to make that if one of conditions are not met it would leave blank or N/A?
I have tried something like this: If(OR(And(B2="Female", I2>70%, G2>0.5)),"Good girl student", "Good boy student").
Hello Dom!
Specify your conditions. According to you, "if it is Male and score is more than 70% and productivity is more than 0.5 it is a good boy student, otherwise it is a good girl student." What does "otherwise" mean? And if it is Male and score is less than 70 % and productivity is less than 0.5 it is a good girl student? It suits your conditions.
I am sorry for confusing you,
I have four columns: Gender(there it is written Male and Female), than Score(Percent of score written), Productivity(number written) and empty column named(Good boy student/good girl student)
The task is to make formula which checks if it is a male of female, than if the score is more than 70% and if the productivity is more than 0.5. If all conditions is met it should write in column "Good boy student/good girl student" that it is a good boy student if it is Male and a good girl student if it is Female.
Hello!
If I understand your task correctly, the following formula should work for you:
=IF(AND(B1 > 0.7,C1 > 0.5),IF(A1="Male","Good boy student","Good girl student"),"")
Hope this is what you need.
Thank you very much,
It works,
Best regards
Dear Alexander,
Is it also possible that if conditions are not met it would leave blank or write N/A in the cell?
Best regards
=IF(AND(C10="upendra",C11>=70%),"yes",IF(AND(C10=" vivek",C11>=60%),"yes"," no"))
1st set of data Input data Result data
1 4 1 4 6 1 9 1
2 6 3 2 3
9 9 4 9
4 6
I have the data in 3 columns,named as "first set of data". If I enter the secondary data called as "input data" How I can design the formula to get the result data according to mentioned in result data? Any body can help me please.
Hello!
I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
It’ll help me understand it better and find a solution for you. Thank you.
Hi;
I need some advise for this situation :
Any advise how would the formula looks like for multiple conditions
Conditions:
IF number<=5,and size <= 10 return "Small"
IF number<=10,and size <= 10 return "Upsize"
IF number<=10,and size <= 20 return "Upsize"
IF number<=15 and size <= 30 return "Large"
IF number= 30 return "Upsize"
IF number= 30 return "Upsize"
Hello!
The article above details how to use IF nested functions.
You can use something like this
=IF(AND(A1<=5,B1<=10),"Small", IF(AND(A1<=10,B1<=10),"Up", IF(AND(A1<=10,B1<=20),"Up", IF(AND(A1<=15,B1<=30),"large", IF(A1=30,"Up","")))))
Hello Sir,
I need you help for below situation:
In column A there are Fruit names, in column B Purchase dates, in column C values are Open or Closed and in Column D Today's date is mentioned. Please help me generate a formula so that it meets below condition:
If Fruit names are either Apple, Mango or Grapes and if Purchase dates is less than Today's date and in column C value is Open, then result should be 1 else 0.
Thanks,
Klywin
Hello Klywin!
If you apply the IF function to many conditions, the formula will be very large and complex. I recommend using this formula:
=SUM(IF(A1={"Apple","Mango","Gapes"},1,0)) * (--(B1<TODAY()) * (--(C1="Open")))
I hope this will help, otherwise please do not hesitate to contact me anytime.
i am unable to merge two formulas
=IF(F2<=1000,IF(G2=1,2499,IF(F21000,F2<=1800),IF(G2=1,2999,IF(G2=2,1999,"Invalid detail")))
if i write this formula in one column then it only gives result of first formula condition.
Hello!
I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what problem or error occurred. The formula you wrote is incorrect. This may be a copy error. Give an example of the source data and the expected result.
It’ll help me understand it better and find a solution for you. Thank you.
If Column A has 3 same values i.e 1,1,1 and column B has three different values i.e A,B,C and column C also have three different values P,Q,R so column D should display only Values which are available for A in column C i.e P.
A B C D(Formula)
1 A P P
1 B Q P
1 C R P
2 A L L
2 B M L
2 C N L
Hello Sir,
Need your help to create formula for this.
Dear Alexander
please help me with this.
I need a formula that can return the highest value in a group of Data.
let say
A1=D-300 B1=1 C1=1
A2=D-300 B2=1 C2=2
A3=D-300 B3=2 C3=1
I need a formula that can take into consideration the value in column A and B (even if column A has the same value and column B has different value) and return the highest value in column C. Such that the result will read
D-300 FOR 1 = 2
D-300 FOR 2 = 1
I did not quite understand what result you want to get (number, text, or something else). But I think that this formula will be useful.
To find the value in column A that matches the maximum value in column B, use the formula
=INDEX(A1:A37,MATCH(MAX(B1:B37),B1:B37,0))
Dear All,
I require to find three successive cell data A, WO, A in multiple rows in a single page.Then I have to replace any cell data of my choice based on the condition met.
For eg. the following shows multiple successive cell data present in a sheet.
P A WO A
If successive cells are A WO A respectively, then I Have to replace "WO" with "A"
Please solution
Hello!
If I understand you correctly, some values are written in several cells. You want to change some of them using a formula with the IF function. But an Excel formula can only change the value of the cell in which it is written. In your case, you need to use VBA.
I have product names in multiple columns and marked the customer name and quantity they ordered in rows. Now I want a different sheet with customer name, quantity and name of product customer ordered. is there a formula to bring the name and quantity of the product customer ordered in columns if the quantity is >= to 0.5.
I have used IF formula and it works for just one column. How do I go about giving multiple commands to have the result as in below in one cell?
2 Apples, 1 Orange, 5 mangoes
Hello!
I think the article "How to Vlookup in rows and columns (two-way lookup)" will be useful to you.
=IF((AND(I13=,I22=0,I29=0,I30=0),"0.00%",SUM(I13:I35)/SUM(K13:K35)))
is this possible?
Hello!
Perhaps you wanted to write down the formula
=IF(AND(I13=0,I22=0,I29=0,I30=0),"0.00%", SUM(I13:I35)/SUM(K13:K35))
I have a multi-layer problem set, if anyone can help.
This is a data of around 50k Rows. So i have 2 rows. Row A contains item ordered, Row B has timestamps. If i want to calculate the item wise time gaps, how do i go about it? So for example:
Row A: Row B:
Dell Laptop 11:23:04
Mouse 11:39:00
Snickers 12:45:01
Dell Laptop 12:49:08
Dell Laptop 12:51:46
Mouse 12:45:00
I need Row C to show time difference between the next sale of Dell Laptop and first sale, time gap between then third sale of Dell Laptop and second sale.Same goes with Mouse, Snickers, etc
so Row C1 should ideally be (12:49:08-11:23:04) = 01:26:04
C2 (12:45:00-11:39:00) = 01:06:00
and so on. The gaps should calculate only time difference of items sold for only those particular items.
Complicated for me. Would appreciate the help thanks.
Hello!
If in your table the first row is the heading, column A contains the goods, column B contains the time of sale, then in cell C2 write down the formula
=IF(INDEX($B$1:B1, LARGE(IF($A$1:A1=A2,ROW($A$1:A1),1),1))=0,"", B2-INDEX($B$1:B1,LARGE(IF($A$1:A1=A2,ROW($A$1:A1),1),1)))
I hope this will help, otherwise please do not hesitate to contact me anytime.