On our blog, we already have a handful of Excel IF tutorials and still discover new uses every day. Today, we are going to look at how you can use IF together with the AND function to evaluate two or more conditions at the same time. Continue reading
Comments on: IF AND formula in Excel
by Svetlana Cheusheva, updated on
Comments page 9. Total comments: 494
Have following data
ABCD
Column A credit days 60,90,75,30,
Column b actual days 143,200,115,28
Column C Deviation -83,-110,-40,2
Column D Deviation % 138%,122%,53%,-7%
Column E required : If column D >100% then column A/2, If D50% then A*3/4, If D<50% then A
First two conditions are working but not the last one. Pl help me in fixing this issue.
Using 365
This going to be pretty elaborate so im doing my best to describe whats going on.
I have two sheets..one is called Summary and the other is called Cost.
The Summary has 4 drop downs, which in A9 drop down I am to choose my paint system. The other three drop downs, D7, D8 and D9 i am to choose my surface preps...i can have one, two or three surface preps, depending on the job.
Paint system choices in A9: I have 39 choices, ...the main one driving a majority of the formula is TSA....so id choose either TSA or one of the other 38 choices (im not going to list them all..to much to list)
Surface Prep choices for D7,D8 and D9 are as follows (they all pull from the same list):
N/A
LPWC
SP2
SP3
SP6
SP7
SP10
SP11
SP15
I can chose any combination of the above surface preps in the three drop downs.
On the cost sheet, L28 ( which is the cell the formula will be in) it should populate the existing values in cells L21 or L25, based on the 4 drop down selections on the Summary.
The criteria for L28 populating would be as follows:
If A9 on Summary equals TSA and D7,D8 and D9 contain any one or more of the following in any combination: SP3, SP6, SP7, SP10, SP11, SP15 then give me the value in L25.
So for an example, I can have this as a combo:
A9=TSA
D7=SP10
D8=SP11
D9=SP2
If I have that above combo then I should have the value of L25 show up in L28. There may be a scenario where D8 and D9 will have the choice of N/A...(that means for this particular job only one surface prep was required, not three, but i still have to make a choice in D8 and D9 so I made N/A a choice and in this case I would still get the value of L25 because SP10 and SP11 is in the combination of the three.)
On to the next criteria:
If A9 on Summary equals anything but TSA and D7,D8 and D9 contain any one or more of the following in any combination: SP3, SP6, SP7, SP10, SP11, SP15 then give me the value in L21.
For example, to get the value of L21 to show up in L28, I would have this combo:
A9= anything but TSA (I have 39 choices in this drop down...so im chosing any one of the other 38 options)
D7=LPWC
D8=SP2
D9=SP3
In that scenario above im choosing anything but TSA in A9 ..and again, if D7,D8 and D9 contain any one or more of the following, in any combination: SP3, SP6, SP7, SP10, SP11, SP15 then this time give me the value in L21....(N/A could be a choice too but since I have that SP3 in there, then L21 value should still show up in L28)
The combo to leave L28 at zero would be anything but TSA in A9 and any one or more of the following combinations in D7, D8 and D9: SP2 , LPWC
A9 = anything but TSA
D7=LPWC
D8=SP2
D9=N/A
So basically the only time L28 is left at zero is if anything but TSA is in A9 and the only surface prep is SP2 and/or LPWC.
That pretty much sums up what im looking for. I hope I was as precise and clear as possible.
Thanks for your time and reading and possibly helping me out.
here is the formula for my description above:
=IF(Summary!B9="TSA",IF(OR(OR(Summary!D9="SP3",Summary!D9="SP6",Summary!D9="SP7",Summary!D9="SP10",Summary!D9="SP11",Summary!D9="SP15"),OR(Summary!D8="SP3",Summary!D8="SP6",Summary!D8="SP7",Summary!D8="SP10",Summary!D8="SP11",Summary!D8="SP15"),OR(Summary!D7="SP3",Summary!D7="SP6",Summary!D7="SP7",Summary!D7="SP10",Summary!D7="SP11",Summary!D7="SP15")),L25,""),IF(OR(OR(Summary!D9="SP3",Summary!D9="SP6",Summary!D9="SP7",Summary!D9="SP10",Summary!D9="SP11",Summary!D9="SP15"),OR(Summary!D8="SP3",Summary!D8="SP6",Summary!D8="SP7",Summary!D8="SP10",Summary!D8="SP11",Summary!D8="SP15"),OR(Summary!D7="SP3",Summary!D7="SP6",Summary!D7="SP7",Summary!D7="SP10",Summary!D7="SP11",Summary!D7="SP15")),L21,0))
I am trying a conditional statement with this logic:
If Q101ABS(Q101), then Q101*0.01672
If Q101<0 and K101<Q101, then K101*0.01672
If neither statement is true, then return a blank cell.
This is what I tried that doesn't work
=IF(AND(Q101ABS(Q101)),Q101*0.01672),IF(AND(Q101<0,K101<Q101), K101*0.01672,"")
ty for your help.
I am trying to write a formula which will return answers based on data within the 'I' column, plus return a blank cell if column 'D' is blank. I've tried the following two formulas (based on information you provided to another user which I now can't find in the chat thread):
=IF(I2="Retaliation","TO DO","NA",IF(AND(D2=""),"").
The first half of the formula works, but when I add my IF/AND statement it fails.
=IF(AF2="Not determined","NA",IF(AF2="Substantiated","TO DO",IF(AND(G2=""),"")))
The response I get is "FALSE".
I have checked for typos multiple times but clearly I'm missing something. I hope you can provide some guidance!
Hello!
If I understand your task correctly, the following formula should work for you:
=IF(D2="","",IF(I2="Retaliation","TO DO","NA"))
Hope this is what you need.
Trying to build an if and statement If in a column there are multiple Letters but each letter = a word.
Hello!
The information you provided is not enough to understand your case and give you any advice, sorry. Could you please describe it in more detail? What result do you want to get?
I'm sure this is simple but I can not seem to figure out how to get it to work.
Cell M9 is dependent on what happens in cell L9
if L9 is greater than or equal to 3, M9 should be "P",
if L9 is less than 3, M9 should be zero
if L9 is equal to "D", M9 should be zero
if L9 is equal to "N", M9 should be zero
What is the best way to make this happen - if it is possible to make happen?
Thanks
D
Oh.... I got it. Sorry. I knew it was easy, but my mind is fried.
Sorry.
Ok.... but I still have a problem.
So..... the initial part of the formula is obviously =IF(L9>=3,"P")
I have another column that is counting the "P"s, another counting the "D"s, and another counting the "N"s. So here is the problem. When I type in a "D" (for instance) in L9, M9 is putting a "P" in the column and thus, being counted in the "P" count. Also happens if I type a "N" in L9.... a "P" shows up in M9. So I definitely need to know how to make M9 appear as a zero when typing either a "D" or an "N" in L9.
I was think the If/And/or formula would work but can't seem to get the correct combination of things.
So ….. yea, I could still use some help on this.
thanks again
d
=IF(L9="D",0)*AND(L9="N",0)*OR(L9>=3,"P")
This is the formula I was trying. It woks until I add the "OR" part. is it because it two different types of functions? the first two are dealing with alpha characters and the 3rd with a numeric?
I didn't think it would be, but I could be wrong.
Hello!
If I got you right, the formula below would help you with your task:
=IF(OR(L9="D",L9="N"),0,IF(L9>=3,"P",0 ))
Hope this is what you need.
Alexander,
Thank you so much.
I see now why mine didn't work.
I really appreciate the help.
It totally works now!!!!
D
Sorry, even though my formula shows correctly to me, by the time it sends it changes to something else, interesting!
Sorry, when I cut and pasted the formula did not paste correctly:
=IF(B27=7,B27=11,B27=14,B27<=16),4)
Here is the formula I created, but it returns nothing but "0". I am trying to assign a scale of 1-4 for the following brackets:
0-6=1
7-10=2
11-13=3
14-16=4
=IF(B27=7,B27=11,B27=14,B27<=16),4)
Any help is greatly appreciated!
Hello!
I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get? Thank you!
if a1>=b1 then a1=5
if a1<b1 then a1=0
How to join above in one formular??
Kindly help
Hello!
A formula cannot refer to the cell in which it is written.
Hi,
I have a pivot table with three invoice types: X,Y,Z paid from two POs: A & B. I need to separate invoice X to be paid out of A, and Y&Z to be paid out of B. How should I write my IF function to add Y&Z to be paid out of B?
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
I'm unsure why the following formula is not working. I tested all 3 separately and they function correctly but not when I put them together. When I use them together the result is always "Not Started", even when I add text to C3 and a date to I3.
=IF(ISTEXT(E3), "Not Started",IF(AND(ISTEXT(E3),ISTEXT(C3)), "In Process", IF(AND(ISTEXT(E3),ISTEXT(C3),ISNUMBER(I3)), "Complete")))
can you add a bit more context to your post. its a little vague as to what you're trying to do.
=IF(AND(D2>55,E2>65),"Passed both Theory and Practical"),IF(AND(D2>55,E2<65),"Passed Theory and Failed Practical Assessment"),IF(AND(D265),"Passed Practical Assessment Failed Theory Assessment"),IF(AND(D2>55,E2>65),"Unsuccessful"))))))
Hi,
Try this:
=IF(AND(D2>=55,E2>=65),"Passed both Theory and Practical",IF(AND(D2<55,E2=55,E2<65),"Passed Theory and Failed Practical Assessment",IF(AND(D2=65),"Passed Practical Assessment Failed Theory Assessment",""))))
I hope that helps :)
OOPS sorry thta one has a typo, the correct one is below:
=IF(AND(D2>=55,E2>=65),"Passed both Theory and Practical",IF(AND(D2<55,E2=55,E2<65),"Passed Theory and Failed Practical Assessment",IF(AND(D2=65),"Passed Practical Assessment Failed Theory Assessment",""))))
Ok for some reason this website is changing my posts:
after this bit:
This part of the formula: IF(AND(D2<55,E2=55,E2=55,E2>=65),"Passed both Theory and Practical", IF(AND(D2<55,E2=55,E2<65),"Passed Theory and Failed Practical Assessment", IF(AND(D2=65),"Passed Practical Assessment Failed Theory Assessment",""))))
jeez this is frustrating me remove the e2=55, bit
IF DEBIT DAYS 0-31 OR SEC.=2,"5%", SEC=1,"4.5%", SEC=0,"3"AND DEBIT DAYS 31-62 OR SEC=2,"4%", SEC=1,"2%",SEC=0,"1.5%" AND UP TO 62 ,"0" HOW PUT IF FORMULA
I'm looking for a formula that will calculate how much someone gets paid if they work 46.50 hours a week with the following criteria (36-37 hours paid at regular pay, 37-45 paid @1.5x, 45 hours and above paid @ 2x). Regurla pay is $25/per hour.
Hello Adam!
The formula below will do the trick for you:
=(MAX(0,C1-45)*2+MIN(MAX(0,C1-37),8)*1.5+MIN(MAX(0,C1-36),1)*1)*25
This can replace the IF and AND functions.
Please may someone help me with a If/And formula?
I'm working on a formula to highlight if annual spend is 50% higher than estimated spend.BUT ignore if estimated spend value is blank.
I have a simple - If A1(spend) is greater than B1(estimted spend), yes or no. (Below)
=IF(A1>B1,"Yes","No")
But i'm struggling to understand how i can expand that to ignore if B1 value is blank?
Any help would be greatly appreciated - thank you.
Hello Catherine!
If I understand your task correctly, the following formula should work for you:
=IF(A1<>"",IF(A1>B1,"Yes","No"),"")
I hope this will help
I am looking to combine the following 2 statements:
=IF((AND(D3="Put",F3>E3)), ((E3-F3)*(B3*C3)), 0)
=IF((AND(D3="Call",F3<E3)), ((F3-E3)*(B3*C3)), 0)
They work independently from one another but I cannot figure out how to combine them. The goal is to use the correct formula depending on what I have in D3 (Call or Put).
Some guidance would be greatly appreciated.
Thanks.
Hello Rob!
If I got you right, the formula below will help you with your task:
=IF(D3="Put",IF(F3 > E3,((E3-F3)*(B3*C3)),0), IF((AND(D3="Call",F3 < E3)), ((F3-E3)*(B3*C3)), 0))
This will combine the two IF formulas
I've worked it out on my own.
hello sir,
i want count sunday of till date in excel
=COUNTIFS(L9:AO9,"sun")it give me 4 sunday, whole month of june-2020(date 01/06/2020 to 30/06/2020)
i want for till date like =countifs(01/06/2020:10/06/2020,"sun")
How can I do this with excel formula?
if possible please suggest or help me
jai
Hello!
I recommend that you read the COUNTIFS Feature Guide. 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.
Set 1 Set 2 Set 3 Result
Y N N AA
N N Y BB
Y N Y CC
N Y N DD
Y Y N EE
Y Y Y FF
N Y Y GG
N N N HH
Need help with the logic formula - Conditions to be used are only IF, AND. OR.
Hi Shariff,
Is the formula below what you're looking for?
=if(and(A1="Y",B1="N",C1="N"),"AA",if(and(A1="N",B1="N",C1="Y"),"BB",if(and(A1="Y",B1="N",C1="Y"),"CC",if(and(A1="N",B1="Y",C1="N"),"DD",if(and(A1="Y",B1="Y",C1="N"),"EE",if(and(A1="Y",B1="Y",C1="Y"),"FF",if(and(A1="N",B1="Y",C1="Y"),"GG",if(and(A1="N",B1="N",C1="N"),"HH",""))))))))
ASD DFG SDF Result
Y N N AA
N N Y BB
Y N Y CC
N Y N DD
Y Y N EE
Y Y Y FF
N Y Y GG
N N N HH
Need help with the logic formula - Conditions to be used are only IF, AND. OR.
Hello!
I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? Thank you!
I have two cells that are binary either 1 or 0 so I have 4 variables
A1=0, A2=0
A1=0, A2=1
A1=1, A2=0
A1=1, A2=1
and I need a cell to determine what the cells are and if of example
A1=0, A2=0 then cell A3= open
A1=0, A2=1 then cell A3= Right
A1=1, A2=0 then cell A3= Left
A1=1, A2=1 then cell A3= Short
How can I do this with excel formula?
Thanks
Eddie
Hello Eddie!
If I understand your task correctly, the following formula should work for you:
=IF(AND(A1=0,A2=0),"Open", IF(AND(A1=0,A2=1),"Right", IF(AND(A1=1,A2=0),"Left", IF(AND(A1=1,A2=1),"Short",""))))
I hope it’ll be helpful.
HI, I have 2 tables, one table has information on Base quantity, another table has the following information;
Sales/ M for Jan & Feb Average Monthly Base starting at Growth of average of Jan&Feb
> 15000 10%
> 10000 5%
> 7500 15%
> 5000 20%
how do i simplify the formula in excel
Hello Pankaj!
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 let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. It’ll help me understand it better and find a solution for you. Thank you.
I have a spreadsheet with over 5000 rows of a machine id all with varying installation dates ranging from 2012 to today. Column A is machine id. Column B is Installed date. In Column C each machine has a classification code about 8 different classes eg Ste, Alu etc. In column D a disposal date. In Column E i have costs for year 2012. Column F for 2013 and so on. If disposed of for eg in 2012 there will be no costs for other yrs. I need to summarise by month by year the count of installations by classification. Then sum by month and year and by classification the costs. What is the best approach and formulas
Any help appreciated
Hello Lizzy!
The easiest and most correct way to get an answer to your questions is to use a pivot table. Our blog has many articles about this. I recommend here and here.
I hope it’ll be helpful.
Easy to follow explanation
Dear Mam,
I have some problem in if functions.
if sales value is less than equal to 2 lakh then % incentive will be of 2.5% of net sales value.
if sales value is more than 2 lakh and less than equal to 5 lakh then % incentive will be 3% of sales value.
if Sales value is more than 5 lakh and less than equal to 10 lakh then % incentive will be 3.5% of sales value.
And if sales value is more than 10 lakh then % incentive will be 4% of sales value.
please suggest how to solve it.
With regards,
I need help with a multiple IF /IF AND formula because 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(D1460, G14=0) then D14*$L$10,
IF(D14+G14<=59, then $M$9,
IF(AND(G14=<60, P14=J), then 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 Maria,
It looks like you might have some overlaps for solutions is why it might not be working.
Could you list the range of values the cells could have?
I need one equation.
One cell contain one value suppose 100 i need in next in following conditions.
25 percentage of 100 plus 4 percentage of coming answer of 25 percentage.
=(A1*25%)+(A1*25%*4%)
You can also have the percentages in their own columns that way if you want to adjust, you can just change the values in the reference columns.
=(A1*$B$1)+(A1*$B$1*$C$1)
ExtMatlGrp Frm Lot to lot Scrap %
100-34 0 2 250
100-34 2.001 5 65
100-34 5.001 10 25
100-34 10.001 50 9.75
100-34 50.001 100 9.75
100-34 100.001 200 8.75
100-34 200.001 999999 6.25
100-48 200.001 999999 5.75
100-48 100.001 200 6.75
100-48 50.001 100 9
100-48 10.001 50 13.5
100-48 5.001 10 25
100-48 2.001 5 65
100-48 0 2 250
101-120 0 2 250
101-120 2.001 5 65
101-120 5.001 10 25
101-120 10.001 50 14.5
101-120 50.001 100 12
101-120 100.001 200 9
101-120 200.001 999999 8.25
102-104 200.001 999999 11.25
102-104 100.001 200 13.75
102-104 50.001 100 14
dear sir my coloum is fix it is not vary but my 2nd coloun and 3rd coloum lot size is varying example 0-2 is 250 suppose if i put excel arrangemnt like 1 then automatically comes out 250%
Range, greater than and less than in the formula are showing as O. So, I am adding the statements to clarify.
IF(AND((G3+2*$I$1) less than range C3 to C6, V3 greater than 0, IF(V3="NP","NF1",1))
IF(AND((G3+2*$I$1)0,0,IF(V3="NP", "NF1",1))
The below formula is trying to fill a range instead of just one cell. What is the error?
=IF(AND((G3+2*$I$1)0,0,IF(V3="NP","NF1,1))
I have to get output value based on the below scenario
Target qty(T) Achived qty(A) % Achived (A.P) Output value (O.V)
200 200 100%
Conditions : If achieved % is >90% and <100% then outvalue should be such that for every decrement of achieved % 2% should be deducted (eg: if % Ach is 98% then Output value should be 96%, if Ach % is 90% and <100% 2% deduction and from <90%, 1.5% deduction.(eg: If achi. 85% then output value should be 72.5% (upto 90%,2% reduction, 100%, then for every increment 2% increase.(Eg: Ach% is 102% the output value should be 104%)
I have tried building formula using if & netsed if& and formula but I didn't got the right formula. please help to build the formula in excel to get output in the mentioned conditions
I have to get an output based on the below scenario.
Target Achieved %Ach
i have some data where i found some value i have return header with concatenate the header value.
Mismatch - Recipient GSTIN Mismatch - GSTIN of the Supplier Mismatch - Invoice/Debit Note/ Credit Note (No) Mismatch - Invoice/Debit Note/ Credit Note (Date) Mismatch - Original Invoice No Mismatch - Original Invoice Date Mismatch - POS Mismatch - Supply attract reverse charge Mismatch - Total GST Rate Mismatch - Taxable Value Mismatch - IGST (Amt) Mismatch - CGST (Amt) Mismatch - SGST/UTGST (Amt) Mismatch - Cess(Amount)
No No No No No Yes Yes No No No Yes Yes Yes No
No No No yes No Yes Yes No No No Yes Yes Yes No
i have to use three conditions in a cell. for example if A1500, result will be TRUE , if A1<-500, result will be NEGATIVE.
NOTE: the third condition is negative value (- 500)
can you please help me to do this
i have to use three conditions in a cell. for example if A1500, result will be false, if A1<-500, result will be negative.
NOTE: the third condition is negative value (- 500)
can you please help me to do this
Huge help, thank you very much!
I have a formula issue, below need help;
If A3 shows “any value”, show D3 as “In Progress”
If A3 and E3 shows “any value”, show D3 as “Completed”
If A3 and I3 shows “any value”, show D3 as “Pending Approval” < Particular this one having trouble with as the rest work in below formula. I’m close the issue is that this shows “In Progress”
If A3 and E3 shows “blank” show D3 as “Blank”.
This is the formula in its current value, any chance you could take a look and see if there is something wrong?
=IF($A3"",IF($E3"","Completed","In Progress"),IF($A3"",IF($E3="",IF(AND($I3"","Pending Approval","In Progress"),"Completed")),""))
=IF(AND(A3"",E3="",I3=""),"IN PROGRESS",IF(AND(A3"",E3"",I3=""),"COMPLETED",IF(AND(A3"",E3="",I3""),"PENDING APPROVAL",IF(AND(A3="",E3=""),""))))
I am trying to include a formula in excel where if "EUROS" is entered in one cell the next cell will populate the € symbol but will also include the value that I enter.
What formula would I need for this?
Hi Sally,
Please try the formula below.
=IF(A1="EUROS","€"&B1)
I want a formula that will write 1/10/2019 - 2/10/2019 in a cell, if a particular condition lets say a1:a30 is WK1
This should mean that 1st is WKI, and 2nd is also WK1
Hi,
I have a list of 12 different countries in one column and 38 different statements in another column. I'm stuck trying to get the following results basded on all possible combinations (456!) sourcing from different spreadsheets.
IF Argentina + Football then Column X
IF Brazil + Tennis then Column Y
If Argentina + Tennis then Column Z....and so on.
I've been using this formula (=+IF(AND(EXACT(A3;TEST!A2);EXACT(C3;TEST!C2);TEST!E2<30%);TEST!D2;TEST!E2), which is close but every time I change the order of the rows, the results change so, actually, my formula is not working because, for instance, the result of Argentina + Football would appear as Z instead of X.
May you help me, please?
I have a spreadsheet with list of schools and states. Column G lists the state abbreviation and I have 13 analysts. I would like a blanks column B to pre-fill with an assigned analyst name based on their assigned states. For example I tried =IF(G6="NC", "Jay", IF(G6="MT", "Jay", IF(G6="FL", "Rami", IF(G6="VT", "Rami", IF(G6="TX", "Joe"))))...... but I get a warning there are two many arguments.
Any suggestion?
Thanks,
J
Hi Jay,
I would try the Excel IFS function instead:
=IFS(OR(G2="MT",G2="NC"),"Jay",G2="TX","Joe",OR(G2="FL",G2="VT"),"Rami")
I have this Data how to solve?
Grade>=17 And Sex =F And Location it is Bhandup, Mulund, 10% on Basic, Grade>19, 20% on basic , otherwise 8%
Hi Ravina,
Condition 1 and Condition 2 seem to be overlapping in your task. However, the following formula may serve as a starting point and can easily be modified if you decide to alter the conditions:
=IFS(B2>19,20,AND(B2>=17,C2="F",D2="Bhandup, Mulund"),10,TRUE,8)
PS If you wonder why there is ‘TRUE’ before 8 there, press ‘F1’ to search for the Excel IFS function, and you will find the answer. That’s where I learnt it myself.
Hi Everyone! I'm building an estimating spread sheet and I'm having "#Value!" issues with what should be a very simple process. In my case Columns A & B contain a drop down menu. In column A there is only one appropriate choice, let's say "Floor". In column 2 there can be up to three appropriate choices, "Bathroom", "Ensuite" & "PWD" from which, obviously, only one is chosen. In simple English the process is this:
IF cell A1 = "Floor" & cell B1 = "Ensuite", multiply cells F1*G1*O1, otherwise return "" blank.
Of course cell B1 might read "Bathroom, "Ensuite" or "PWD" so this needs to be included in the formula. My problem appears to be incorporating these 3 variables for that cell and I can't find a way around it. Any help that you can give would be greatly appreciated!
Many thanks,
Dick
=IF(AND(A1="FLOOR",OR(B1="BATHROOM",B1="ENSUITE",B1="PWD")),F1*G1*O1,"")
That one should work
=IF( orand(A2>89 , A2<199) , "a"; IF( A2=1, "2"; IF( A2=200, "ok" ; " no " )))
I have this data:
KEY A B C D E F G H I J
3323 6 66 86 64 20 89 68 42 16 31
3324 5 17 46 36 9 40 72 62 81 68
3325 62 8 44 18 80 52 6 55 3 66
3484 37 29 31 67 57 55 2 50 12 28
3485 32 33 49 80 29 77 30 18 68 78
3486 59 11 55 41 62 71 72 70 1 68
I WANT TO USE THE IF AND FORMULA TO CHECK IF I GET 2 NUMBER THAT ARE THE SAME THEN
I WANT TO THE KEY AND THAT NUMBERS. FOR EXAMPLE KEY 3323 HAS 6, 66 SO IS KEY 3325 SO THERE IS A MATCH. THE OUTPUT WILL BE 3323 6 66 86 64 20 89 68 42 16 31
3525 62 8 44 18 80 52 6 55 3 66
ANOTHER MATCH IS 3324 5 17 46 36 9 40 72 62 81 68
3486 59 11 55 41 62 71 72 70 1 68
WE HAVE 6-66 AS A MATCH IN 3323 AND 3525 AND ANOTHER MATCH IN 72-68 IN 3324 AND 3486
I ALSO WANT TO USE THE VLOOKUP OR ANY OTHER FORMULA A THAT WILL DO THE SEARCH AND A MATCH. I AM A BEGINNER, AND STILL LEARNING, ANY HELP OUT THERE?
Hi
I'm trying to achieve what I believe the be a IF AND OR formula please. This is what I have so far:
=IF((AND(B3="linux/Unix",D5="Run the following network scan:")),"nmap -A ","1..255 | % {echo ''192.168.X.$_''; ping -n 1 -w 100 192.168.X.$_} | Select-String ttl")
B3 is a drop box so if "Linux/Unix" is not selected, then Windows is the only other option which displays the second outcome.
However, I want to add an extra piece =if(D5="Next Question", " ", " "). Basically I want a black cell left if D5="Next Question" regardless of what B3 equals please.
I have tried adding this to the end with extra brackets, but this does not work and I'm not sure where else to nest it please?
=IF(D5="NEXT QUESTION","",IF(AND(B3="linux/Unix",D5="Run the following network scan:"),"nmap -A ","1..255 | % {echo ''192.168.X.$_''; ping -n 1 -w 100 192.168.X.$_} | Select-String ttl"))
That should work
Pls help me get a formulae for calculating the rate(%) for a particular deposit given the amount and period using the below tables:
Fixed deposit of 10 to 50M. Rate;
30 to 90 days - 6.50%
91 to 180 days - 7.00%
181 to 365 days - 7.25%
Fixed deposit of >50M. Rate;
30 to 90 days - 7.00%
91 to 180 days - 7.25%
181 to 365 days - 7.50%
what if im looking for an IF THEN in a range,
example
IF in this range "x" is found THEN do this
Can you please give a correct formula for the below
IF SHEET2 COLUMN B VALUE = SHEET1 COLUMN B VALUE AND (ITS) SHEET1 COLUMN D VALUE= POSTED OR HOLD THEN COLUMN C VALUE ADDED TO COLUMN H
IF SHEET2 COLUMN B VALUE = SHEET1 COLUMN B VALUE AND (ITS) SHEET1 COLUMN D VALUE= DELIVERED OR TRANSIT THEN COLUMN C VALUE ADDED TO I