I have a data set where a1 is the starting cell and it needs to equal cells b9 or b6 and if the cells in the a column match the data in b9 or b6 it should be assigned an 1 if the cell data in column a does not match the data in cells b9 or b6 it should be assigned a 0. I've tried multiple formulas and have not been able to find the right one
View 1 RepliesI am trying to do a lookup from the price list based on the below data file, my problem is I need the price that falls within the qty range from the price list, the price list is not consistent and changes with every item. I would like one formula that is sophisticated enough to work no matter where the qty range lies.
Item #_____Qty Ordered________Price List
20800______1,500
20800______4,000
20800______6,500
20800______8,000
20800______9,050
20800______11,000
20800______14,000
Price List
Item #____Order Qty________List Price
20800_____2,000___________$3.00
20800_____4,000___________$2.95
20800_____6,000___________$2.90
20800_____8,000___________$2.85
20800_____10,000__________$2.80
20800_____12,000__________$2.75
20800_____14,000__________$2.70
I have three IF statements as below. the problem is if the first statement is true I want it to skip the next two statements or the result will be changed again.
View 2 Replies View RelatedI have developed an evaluation algorithm for residential mortgages, and am having difficulty testing. I am trying to ensure that my scoring model makes sense from every possible scenario. The grading model takes into account multiple factors that most financial institutions would find important when assessing a potential borrower for a loan. Examples of these factors could be an individual's credit score (ranging from 0 - 800), the type of dwelling (multiple options to choose from), etc. In total there are about 20 different factors, each with a minimum of 5 possible options to choose from.
I am trying to create a spreadsheet that lists out all possible combinations of the multiple factors, and trying to input it by hand . To give an example:
Credit Score
Property Dwelling Type
Property Value
100
Townhouse
$350,000
[Code] .....
The goal of this is to find the best combination of a captain (out of 5 possible choices), 5 crewmates (out of 25, no repeats), deck items (2 items, can be repeated, 20 total options), ram (1, out of 13), hull (1, 13 choices), and rudder (1, 7 choices)
Each choice in every category has 4 attributes - fighting, morale, seafaring, and speed. The ship parts also have a true/false field called "unlocked" - the false fields obviously can't be chosen.
A mission gives numbers saying what total attributes are needed for a 100% chance of success. If the mission has 2 attributes that are required (say seafaring and fighting, for instance) then it takes whichever chance of success is lowest and counts that as the mission success chance.
Example: A mission requires 2000 fighting and 2000 seafaring - I can reach 2000 fighting but 1500 seafaring, chance of success is 75%. It would be better for me to get a combination that gave me an 87% success rate in both fields.
How do I have excel go through every combination (and is that even plausable? I would imagine that being in the billions of combination choices) and highlight (turn a cell green or something) the combination that has the highest minimum chance of success in all fields?
I am trying to get a function in a sheet where it has two possible truth outcomes and one false outcome. Both statements false statement goes back to
'Production Metrics 2'!E11/12
Here are the two If statements as they appear in the formula bar now. I need to combine them so they both work and don't cancel each out out.
=IF('Production Metrics 2'!E11/12>2500,2500,'Production Metrics 2'!E11/12) If c10+b10>=4500,0,'Production Metrics 2'!E11/12
I'm having trouble joining the followinf IF Statements. They work independently, however, I cannot get them to work in the same Cell.
Any help would be greatly appreciated.
=IF(A9="YES", SUM(A15+A21),"0")
=IF(B9="YES", SUM(B15+B21), "0")
Trying the following multiple if formula, however it only works for my first if statement and not the rest.
=(IF(G21="onl",WORKDAY(E21,L27,K26:K43),(IF(G21="telb",WORKDAY(G21,L27,K26:K43),(IF(G21="mrt",WORKDA Y(G21,L27,K26:K43),(IF(G21="tlr",WORKDAY(G21,L26,K26:K43),(IF(G21="atm",WORKDAY(G21,L26,K26:K43),(IF (G21="chq",WORKDAY(G21,L26,K26:K43),(IF(G21="crcdwu",WORKDAY(G21,L28,K26:K43)))))))))))))))
Is there a way to write a forumula that read, if it's January, add column A1:A10, if it's February, add column A1:B10, if it's March, add column A1:C10 and it's April, add column A1:D10
I have attached a sample spreadsheet for your review. I think I need to combine multiple IF statements into one single statement. See column I:
There is currently a formula in Column I that looks at Column H and computes a ROUND formula based on this information. Unfortunately, this formula only works if Column J says "MB". If Column J says "Minutes" then I need Column I to compute with this formula: =IF(H2<30,0.5,ROUNDUP(H2/60,1))
How can I combine this with the current formula to get what I am looking for? Here is the current formula (for MB): =IF(H2<0.125,0.125,ROUND(H2,4))
I have attached a copy of my monthly work schedule. Sorry if it looks too noobish but I am.
The question I have is on the schedule right now, it uses to of the same schedule, one is called request off and the other the final. The request off right now is where all changes are made, and they reflect on the final. I did it this way because I could not figure out how to make the cells autofill according to the day of the week, like if it was friday i needed it to fill in the shifts and normal days off automatically.
What I am trying to do now is eliminate the need for the second schedule, the request off one. I think I have figured out how to have the cells autofilled according to the day of the week, and also let the user enter in other stuff such as sick, vacation or whatever, and have it autofill if the cell is left blank, using vba. this is what i have so far, and it is working I was just wondering if it was possible to set it up for a range of cells instead of having to enter each one in manually.
if it is possible to use a batch query to execute more than one SQL statement at once from Excel. Something like this:
View 9 Replies View RelatedCan anyone tell me what I'm trying to accomplish with the following formula:
=(IF(E3="CORP",F14*0.9,IF(E3="GOLD/SLVR",F14*0.9,IF(IF(E3="ON",IF(Q33>=192,F14*0.9,IF(Q33>=96,F14*0.95, F14))), IF(E3="OFF",IF(Q33>=288,F14*0.9,IF(Q33>=192,F14*0.95,F14)))))))
It doesn't work as written but I don't what edits I have to make to make it work.
If my input page B13 is either New Lease In-House or New Lease w/Co-Borker andmy input page B43 is - then take cell D57 in my Analysis report, mulitply it by cell B32 from Input page and divide it by 2, otherwise, take cell D57 on the analysis report, mulitply it by B42 on Input page and divide it by 4. I think I have a mess up with my brackets. This is what I have: =IF((OR('Input Page'!B13="New Lease In-House",'Input Page'!B13="New Lease w/Co-Broker"))AND('Input Page'!B43="-",'Analysis Report'!D57*'Input Page'!B32/2,'Analysis Report'!D57*'Input Page'!B32/"4")
View 4 Replies View RelatedI want to create a formula that will turn B2 in floors if A2 has /1 with its formula, Ceilings if it has /2, walls if it has /3, and M&E if it has /4. For example 456-401/2-1569 in cell A2 will populate ceilings in B2. I know I can create If statements but I can only figure out how to do this to search for one text and populate one word. I know Vlookup is possible but this spread sheet will be blank and as people populate there information I need the cells to automatically populate the data into the B column. how I can create the right formula?
View 4 Replies View RelatedI'm trying to create a statement that will add certain fields based on the month all in one formula. IE
=if(A1="January",sum(a2),if(A1="February",sum(a2:A3)......if(a1="December",sum(a2:a13).
I've tried a normal "IF" statement, but that does not seem to work after 4 months.
I am trying to tell the spreadsheet to look at each separate month and return different criteria. Current formula for single month search is:
=IF(VLOOKUP($E5,'PO Info'!$A:$AW,2,0)"","B",IF(VLOOKUP($E5,'PO Info'!$A:$AW,3,0)"","C",IF(VLOOKUP($E5,'PO Info'!$A:$AW,4,0)"","D",IF(VLOOKUP($E5,'PO Info'!$A:$AW,5,0)"","E",IF(VLOOKUP($E5,'PO Info'!$A:$AW,6,0)"","F","")))))
I would like to add an AND function so that the IF statement also looks at the month which is represented in this case as $E$7, the formula I have been trying is:
=IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,2,0)"","B",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,3,0)"","C",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,4,0)"","D",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,5,0)"","E",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,6,0)"","F")
Then carry this forward for other months, for example, a later look at this formula would be:
=IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,2,0)"","B",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,3,0)"","C",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO Info'!$A:$AW,4,0)"","D",IF(AND($E$7=WeekNos.!$A$2,VLOOKUP($E5,'PO
[Coe] ...........
=IF(VLOOKUP(B3,'Rep List'!$E$1:$I$10000,5,FALSE)=OR("BH","AC","#N/A",0),VLOOKUP(C3,'Rep List'!$A$1:$C$1000,3,FALSE),VLOOKUP(B3,'Rep List'!$E$1:$I$10000,5,FALSE))
I'm getting the "#value" error - the vlookups are working independently
I have a userform with 14 independant checkboxes on and would like it when the operator presses the 'go' button for the code to look at each box and if ticked put a "Y" in the column on a spreadsheet associated with that checkbox.
I have this portion of code set for each checkbox within the sub function, but it's a lot of code for something relatively simple:
If GroupFinder.firstbox.Value = True Then
Worksheets("Number one").Range("C" & grouprow).Value = "Y"
Worksheets("Number one").Range("C" & grouprow).ClearComments
Worksheets("Number one").Range("C" & grouprow).AddComment
Worksheets("Number one").Range("C" & grouprow).Comment.Text Text:="Added by:" & Operator & Chr(10) & "On:" & Date
Else
Is there an easy and quicker way of doing this without all this code for each of the 14 checkboxes? I'm sure there is, but I can't figure it out.
I'm trying to keep the code simplified for others who follow after and might not follow VBA so well.
Can you have multiple IF statements in one cell. I know nested IF's are out there, but they will not work for what I need.
I just need to have two (or more) complete IF statements.
I have are two listboxes set up, one with all the states, the other with 7 possible variable selections. So in my case I have 371 (DC, Virgin Islands and Puerto Rico are included as states in this) possible combinations here. With each combo the user needs to be able to enter up to 2 years of quarterly data (8 quarters, so 2968 pieces of data possible) and then press a button to save that data to a specified location which I will need to access at run of the overall macro.
Is there an easier way to do this then to simply assign each combination a cell? Maybe an If State = AK and Variable = TUR then find column = AK & row = TUR (per quarter) type solution?
I'm using one spreadsheet "Take Off", to pull data from a cell which dictates an objects shape, based on this shape I have a very large IF statement that pulls data from an adjacent cell where an objects dimensions are entered, the statement than proceeds to use these dimensions to calculate the weight of the object.
I've run into the problem with only 7 IF statements, I have gone online and tried to figure out how to avoid this since it is a common problem, but I can't figure out how to do it, most of the examples show very simple and basic formulas. I am unable to figure out how to translate this into my IF statement where I make reference to many cells as well as us differant formulas within the IF statement.
My latest solution was to breakdown the IF statements and try to my my own functions but I think I am too green to have done this properly. Any guidance would be greatly appreciated.
The following is the code I'm working with followed by how I tried to remedy the situation:
=If(Or(C21="BPLT",C21="L"),(((Get_Word(D21,"First")+Get_Word(D21,3))/1000)*(Get_Word(D21,"Last")/1000)*(E21/1000)*7850),If(And(C21="PL",L21<4),(Get_Word(D21,"First")/1000*Get_Word(D21,"Last")/1000*E21/1000*7850), If(Or(C21="BP",C21="PL",C21="BAR"),(Get_Word(D21,"First")/1000)*(Get_Word(D21,3)/1000)*(Get_Word(D21,"Last")/1000)*7850, If(And(C21="HSS",L21>4),(Get_Word(D21,"First
I tried to make my own functions:
aCalc: =If(Or( 'Take-Off'!C13="BPLT",'Take-Off'!C13="L"),(((Get_Word('Take-Off'!D13,"First")+Get_Word('Take-Off'!D13,3))/1000)*(Get_Word('Take-Off'!D13,"Last")/1000)*('Take-Off'!E13/1000)*7850),"")
bCalc:
=If(And( 'Take-Off'!C13="HSS",'Take-Off'!L13>4),(Get_Word('Take-Off'!D13,"First")*2+Get_Word('Take-Off'!D13,3)*2)/1000*Get_Word('Take-Off'!D13,"Last")/1000*'Take-Off'!E13/1000*7850,"")
and so on until hCalc, but when I put them together in a cell like this:
=aCalc()+bCalc()+cCalc()+dCalc()+eCalc()....
I get an error #REF.
I realize a problem might be that I'm referencing a specific cell within my formulas, and many examples online show cells referenced in the form =A$1$, is there a differant way for referencing a cell, because currently I rely on Excel to automatically do this by way of relative reference.
ADDED: Ok I scrapped making the functions because I'm not sure and I did some research on VBA so I tried this one:
Function Weight(Shape As String, Amount As Double, Dimension As String, Optional Length As Variant)
If (Shape = "BPLT" Or Shape = "L") Then
If (Amount > 5) Then
Weight = Get_Word(Dimension, "First") / 1000 + Get_Word(Dimension, 3) / 1000 + Get_Word(Dimension, 5) / 1000 * Get_Word(Dimension, "Last") * 7850
Else
Weight = Get_Word(Dimension, "First") / 1000 + Get_Word(Dimension, 3) / 1000 * Get_Word(Dimension, "Last") * 7850
End If
But when I try to put Weight(C6,D6,F6,E6) I run into the error "Sub or Function Not Defined"
I have a worksheet and in one of my columns I can have multiple values, this could either be a valid numeric value, .e.g "Trace" or a value such as >1.5 or it could be blank.
Depending on the value I then want to return a different result in another cell.
In the example I have tried my data is in cell E8
I have tried the following formula and failed miserably, I'm not sure whether IF or IF OR can cope with this.
=IF(E8>=0,"1",IF(E8<0,"Invalid",IF(E8="Trace","2",IF(ISBLANK(E8),"4",IF(LEFT(E8,1=">"),"5")))))
If E8 contains a numeric value equal or greater than 0 then I want it to return a value of 1, this works
If E8 contains a value that is negative then I want my formula to then display "Invalid"
If E8 contains the word "Trace" to return a value of 2
If E8 is blank then to return a value of 4
If the first character in E8 is ">" then return a value of 5
I need to multiply the result of the 3 if statements as shown below in one cell Each of these 3 if statements output an integer and I need to multiply the value of each of these 3 output integers to get the final output integer. syntax of multiplying if statements outputting an integer?
IF(ISNUMBER(SEARCH("Gold",B20)),C11,IF(ISNUMBER(SEARCH("Silver",B20)),B11,
IF(ISNUMBER(SEARCH("Platinum",B20)),D11,IF(ISNUMBER(SEARCH("Customized",B20)),E11))))
IF(ISNUMBER(SEARCH("Gold",B20)),C2,IF(ISNUMBER(SEARCH("Silver",B20)),B2,
IF(ISNUMBER(SEARCH("Platinum",B20)),D2,IF(ISNUMBER(SEARCH("Custom",B20)),E2))))
IF(ISNUMBER(SEARCH("Gold",B20)),C12,IF(ISNUMBER(SEARCH("Silver",B20)),B12,
IF(ISNUMBER(SEARCH("Platinum",B20)))),)),D12,IF(ISNUMBER(SEARCH("Customized",B20)),E12))))
Here is the first required formula, which will go in cell P7:
If N7>=75 a value of "Y" should be returned. If not, a value of "N" should be returned. BUT if N7 is blank, a blank should be returned.
Here is the second required formula, which is more complicated, and will go in cell Q7:
(1) If Cell P7="Y", then a "Y" needs to be returned in the cell.
(2) If Cell O7>=2, then a "Y" needs to be returned in the cell. If not, then a "N" needs to be returned.
(3) If Cell P7 is blank, then the cell needs to remain blank.
Here is the final formula, which will go at the bottom of the page.
I need the Q column to return an average of the number of "Y" values, not counting the blanks as numbers. (For instance, if there were 18 "Y"s, 2 "N"s, and one blank, the value returned would be 0.90.).
Im looking to make a formula that states if b3 =10 then a. if b3=12 then b, if b3= 14, then c, if b3=16 then d. A, B, C, D are referring to links to another sheet.
View 8 Replies View RelatedI'm running a macro with multiple statements for hiding columns, and it has been running well for years, now today i'm getting a error message in the macro while debugging that states "Unable to set the Hidden property of the Range class", and when I reset the macro, and try to manually hide the range, I get the error message "Cannot shift objects off sheet".
View 2 Replies View RelatedI want to have cell C8 in Worksheet1 to equal cells A25-A29 in Worksheet3 and also when cell F33 in Worksheet 1equal the Cells A35-A37 in worksheet3 a calculation is done based on the info in cell D18 in worksheet 1 and if it returns a limit under a set value the set value is returned…... Example;
When cell C8 in worksheet 1 = A25-A29 in worksheet 3, and Cell F33 in worksheet 1 = A35 in worksheet 3, I want it to do an equation where it takes the amount from cell D18 in worksheet 1 and multiples it by 2 then divides it by 1000, but if the answer is less then 550 I want it to return 550.
When cell C8 in worksheet 1 = A25-A29 in worksheet 3, and Cell F33 in worksheet 1 = A36 in worksheet 3 I want it to do an equation where it takes the amount from cell D18 in worksheet 1 and multiples it by 2.42 then divides it by 1000, but if the answer is less then 600 I want it to return 600. **** Only exception in this one is when C8 in worksheet 1 = A29, the calculation of D18 is still 2.00 not 2.42)
When cell C8 in worksheet 1 = A25-A29 in worksheet 3, and Cell F33 in worksheet 1 = A37 in worksheet 3 I want it to do an equation where it takes the amount from cell D18 in worksheet 1 and multiples it by 2.70 then divides it by 1000, but if the answer is less then 650 I want it to return 650. **** Only exception in this one is when C8 in worksheet 1 = A29, the calculation of D18 is still 2.00 not 2.42)
I have 4 IF(AND) conditions that work fine separately but I can't seem to combine them into one.
=IF(AND(H56="New",I56="RE",Q56<=5),”YES”,”NO”))
=IF(AND(H56="New",I56="NON",Q56<=1),”YES”,”NO”))
=IF(AND(H56="Renew",I56="RE",Q56<=3),”YES”,”NO”))
=IF(AND(H56="Renew",I56="NON",Q56<=2),”YES”,”NO”))
I've been using nested IF statements, with success for a while, but now I'm into my fourth nested statement I'm starting to get some problems.
=IF($P15>0,SUM(IF(APR09!$M$2:$M$500=Department!$M$1,IF(APR09!$B$2:$B$500=$Q$6,IF((AND(APR09!$G$2:$G$ 500<>Department!$N$29,APR09!$G$2:$G$500<>Department!$N$30)),1,0),$N$2))))
Is what I've come up with so far. However, this returns 0, even though there are 3 items which should resolve the criteria. The main problem is here: IF((AND(APR09!$G$2:$G$500<>Department!$N$29,APR09!$G$2:$G$500<>Department!$N$30))
Where I am trying to make the sum increase by 1 if the cell from the row in question, having made it through the first 3 IF statements is not equal to either of those two cells (a range would be better) then the Sum needs to be increased by 1.