Multiple IF Statements With Multiple Results?

Jun 20, 2014

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

Multiple IF Statements (skip The Next Two Statements Or The Result Will Be Changed Again)

Nov 22, 2008

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.

Multiple If Statements With Multiple True And One False

Aug 6, 2014

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

Return Multiple Results In Multiple Cells Based On Criteria In Yet Another Cell?

Feb 1, 2012

Worksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)

e.g.
AAA blue
BBB orange
AAA round
CCC smelly
AAA elongated

Worksheet 2 I want to show:
A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.

e.g.
AAA blue round elongated
BBB orange
CCC smelly

Multiple Criteria - Multiple Results

Mar 26, 2008

I need to look up a value from a worksheet using multiple criteria, and return MULTIPLE results.

I have a table with comments written by different people about specific items. I would like to be able to type in that item name and return all those comments. BUT - it's not JUST the item name, but also on a specific date or some other criterion.

Currently I have managed to do this successfully with a single criterion, as in this array formula:

=IF(ISERR(INDEX(F\$1:F\$1001,SMALL(IF(A\$1:A\$1001="ABC",ROW(F\$1:F\$1001)),ROW(1:1)),1)),"-",INDEX(F\$1:F\$1001,SMALL(IF(A\$1:A\$1001="ABC",ROW(F\$1:F\$1001)),ROW(1:1)),1))

I then copy that formula to a few more rows and I indeed get a list of different names (F1:F1001) which match my criterion "ABC". But - I have at least one more, or even two more criteria. I've tried a whole lot of different things and I just get errors.

Has anyone tried something like this? If you have - how should I modify the above formula to allow for additional criteria? For example, not only A1:A1001 equal to "ABC," but also B1:B1001 equal to "XYZ", and so on.

Multiple IF Statements

Nov 23, 2008

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")

Multiple If Statements....

Aug 31, 2009

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)))))))))))))))

Multiple IF Statements ..

Apr 28, 2007

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

Combining Multiple IF Statements

Nov 11, 2009

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))

Enter In Multiple Vba If Statements

May 17, 2008

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.

Executing Multiple SQL Statements At Once

Oct 4, 2008

if it is possible to use a batch query to execute more than one SQL statement at once from Excel. Something like this:

Multiple IF Statements Working Together

Aug 28, 2009

Can 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.

Combine Multiple IF/AND/OR Statements

Oct 16, 2009

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")

Multiple If Statements In One Cell?

Oct 14, 2013

I 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?

Multiple IF Statements For Months

Feb 21, 2014

I'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.

Multiple IF(AND Statements In One Formula

Feb 28, 2014

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] ...........

Combine Multiple If Statements With OR

Jun 15, 2009

=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

Multiple Checkbox If Statements

Jan 18, 2010

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.

Multiple If Statements In One Cell

Feb 16, 2010

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.

Alternative To Multiple If/Then/Else Statements

Aug 23, 2006

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?

Simplify Multiple If Else Statements

Aug 24, 2007

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"

Multiply Outputs Of Multiple If Statements?

Apr 8, 2014

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))))

Using Multiple IF Statements Then Coming Up With Average

Jun 22, 2014

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.).

Multiple If Statements In Open Office?

Feb 12, 2014

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.

Multiple Statements For Hiding Columns

Jan 20, 2009

I'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".

Application That Requires Multiple Statements

Feb 1, 2009

I 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)

Combination Of Multiple Statements Into IF Statement?

Jan 31, 2013

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

Multiple Condition IF Statements (combine Them Into One)

Feb 1, 2009

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�))

Multiple Conditions In Nested IF Statements

Apr 9, 2009

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.

Loop Involving Multiple IF Statements

Feb 8, 2012

I want to loop through a range of cells changing the value of each cell by increments of 0.01 until a value of 10 is reached.

If the loop encounters a blank cell I want it to start the loop at M110 again

If the starting value of cell M117 is greater than 10 then I want the loop to use -0.01 increments to reach 10

If the starting value of cell M117 is less than 10 then I want the loop to use +0.01 increments to reach 10

This is what I have written so far...

Sub Test()
Dim i As Single
i = 0.01

Range("M110").Select
Do Until Range("M117").Value = 10

[Code] .......