How can I combine the two below statements like these to make one "Or Statement"? Sorry...I haven't done excel and/or's in forever?
=IF(('Proj Info'!L10="main")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)
OR
=IF(('Proj Info'!L10="serv")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)
I'm hoping someone can help me sort this out. I have a formula I need te determine if a TAT was 'met' or 'missed' and it has multiple criteria. Here is the formula I am trying to get to work:
=IF(AND(I2="Amendments",F2<=3,IF(AND(I2="Renewals",F2<=1,OR(D2<=G2)))),"Met",IF(F2="n/a","n/a","Missed"))
I've been able to get separate pieces of the formula to work correctly, example:
=IF(AND(F2<>"n/a",I2="Amendments",F2<=3,OR(D2<=G2)),"Met",IF(F2="n/a","n/a","Missed"))
but when I try to combine everything into one string I can not get it to work.
I cannot get the following nested IF statements to work and get the "Else without If" error for the first Else If statement. I had assumed the first "If" would apply to this
Private Sub ReconcileNow()
If txtTValue = "" Then
Exit Sub
End If
In the example below, could you use anything else other than "with"? Couldn't you use two if's?
[Code]....
I have ranges that are added (E11,E14,E16,E18) if no exemptions exist (my check boxes). I have IF statements with two check boxes. If one checkbox is selected a seperate calculation is performed and the result is placed in cell AH49. My other check box places it's formula in cell AH50. But if I check both boxes I want the value from cell AH52 to display. However, it doesn't display, it gives the value from cell AH49. Is the code wrong?
=IF(AD49=TRUE,AH49,IF(AD50=TRUE,AH50,IF(AND(AD49=TRUE,AD50=TRUE),AH52,SUM(E11,E14,E16,E18))))
I'm trying to nest the following:
=IF(VLOOKUP($F6,'Dept location'!$A:$B,2,FALSE)>0,VLOOKUP($F6,'Dept location'!$A:$B,2,FALSE),FALSE)
=IF(VLOOKUP($H6,'Project location'!$C:$K,9,FALSE)>0,VLOOKUP($H6,'Project location'!$C:$K,9,FALSE),FALSE)
=IF(VLOOKUP($V6,'Dept location'!$A:$B,2,FALSE)>0,VLOOKUP($V6,'Dept location'!$A:$B,2,FALSE),FALSE)
To start with, not sure I have the ">0" part correct in the above IF statements. That expression is meant to be asking it: Is the VLOOKUP finding valid data (result #N/A)?
Next, comes the nesting part. If the VLOOKUP isn't finding valid data (result = #N/A), then I want it to move on to the next IF statement.
I have 3 different columns I want it to look at; hence, the 3 IF statements. In the end, if none of the three IF statements results in any valid data, I want it to give the result "Research".
Income less than 10,000 490 flat fee
Between 10,000 100,000 9.00%
Next 100,001 400,000 7.00%
Max 400,001 800,000 4.25%
iF(F19midbandMAX,(LowerbandMAX*lowerbandPERCENT)+(midMAXAMOUNT*midbandPERCENT)+(F19-imidbandMAX)*maxbandPERCENT))))
And excel is kicking out the formula "You've entered too many arguments"
(the formula doesn't cater for the 800000 max yet either
I am trying to write one Excel formula that can accomplish the following: Review a Discount Sheet to determine whether a Class Name should be purchased based on its current discount compared to my Target Discount for that Class Name. The majority of the time, weekly discount changes occur in 5-10% increments, but occasionally they will occur in increments of 20% or more. If a discount percentage increases by 20% or more and surpasses my Target Discount, the current method I am using to automate this process does not work.
Example: Last week's discount in cell G2 is 25%, and this week's discount in cell H2 is 40%. The Target Discount for that class is 30%, so under my current system I would not request that Class Name for purchasing because the current discount does not exactly equal my Target Discount, even though the previous week's discount did not exceed my Target Discount and now I should purchase that Class Name because the discount has gone from not meeting to exceeding my Target Discount.
INDEX MATCH used to retrieve current discount:
=INDEX('Discount Sheet'!$H$2:$H$4910,MATCH(A3,'Discount Sheet'!$F$2:$F$4910,0))
Validity Test in Cell F2 to compare Current Discount to Target Discount:
=E2=C2
NOTE: The above validity test could include a >= to capture Current Discounts that have exceeded the Target Discount; however, over time all Current Discounts will exceed the Target Discount. It is only when last week's discount did not exceed the Target Discount and now the Current Discount does exceed the Target Discount should a class be purchased.
The final result of this formula should be "TRUE' or "FALSE" without having to cut and paste any data for additional validity comparisons.
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.
I have this nested if statement, but my last IF statement needs to incorporate another component.
View 2 Replies View RelatedTrying to make it even more complicated....probably again misplaced my parenthesis
=IF((AND('Input Page'!B43="-",OR('Input Page'!B13="New Lease In-House",'Input Page'!B13="New Lease w/Co-Broker")),'Analysis Report'!D57*'Input Page'!B32/”2”,'Analysis Report'!D57*'Input Page'!B32/"4",IF(AND('Input Page'!B43="-",OR('Input Page'!B13="Renewal In-House",'Input Page'!B13="Renewal w/Co-Broker",’Input Page’!B13=”Expansion In-House”,Input Page’!B13=”Expansion w/Co-Broker),'Analysis Report'!D57*'Input Page'!B32,'Analysis Report'!D57*'Input Page'!B32/"2"))
I am very new at in depth excel formulas, usually in my daily work I can get by with simple summations, if statements etc. This one has thrown me for a loop.
I'm making a spreadsheet on soil compressibility and I feel like I am just knocking my head against the wall. Here is what I am working with. I need to calculate values in a column, we'll call them "Rm" values.
There are a few basic things that dictate which "Rm" equation you use. I'm substituting V,W,X,Y,Z in for the equations to simplify the question.
I = 3 Rm= W*log(K)
.6
I have a workbook with two worksheets, Sheet1 and Sheet2. Sheet2 contains a table of values that need to be input into a cell on Sheet1, pending the results of comparing two other cells on Sheet1. I have 8 possible variations resulting from that comparison and I cannot make this work as the IF statement limits you to 7 deep.
Example:
Sheet1
A1 (text string value) = LOWER
B1 (text string value) = L1
C1 (currency with no decimals) = Sheet2!Somecell (decision of which cell to use depends on combination of A1 and B1)
A1 can be either the string "LOWER" or "MIDDLE". B1 can be the strings "L1", "L2", "L3", or "L4". The strings in B1 are not cell references, but simple text. This leads to four variations for a row that has "LOWER" in it's A column, and the same for "MIDDLE" - totaling 8 possible combinations.
Depending on the combination, I need to input a number from Sheet2 and that number is different for each unique combination of the eight possibilities. There is no mathematical calculation taking place on Sheet2 - just an "if x and y then z" decision on Sheet1. I will use the value of Sheet1!C1 in other math functions on Sheet1.
I know there is a maximum of 7 nested IF statements available but I cannot get the following formula to work, even though it only has 7 IF's
=SUM(IF(Department=J$88,IF(Progress_StatusCNP,IF(Progress_StatusCSO,IF(Progress_StatusNS,IF(Current_Phase=$B91,IF(MID(Progress_Status,1,7)="On Hold",IF(MID(Group_Procurement_Involved?,1,3)="Yes",In_Year_Opportunity__2007,0),0))))/1000)))
CNP, CSO and NS are named cells, all named ranges are the same size and the formula is entered with Ctrl & Shift to make an array formula.
I need to write IF statement with 4 criteria, or at least I think IF will do the trick. It would be difficult and not very visual to describe my question here, therefore I have attached a dummy workbook with the comments. Basically I have got 2 values in 2 columns. Next 2 columns will hold letter "x". There are 4 possible variations how "x" will appear in those two columns: first, second, both or none. So depending where the "x" is, I need to return one of the 2 values.
View 6 Replies View RelatedWhat I want that cell Q4 displays:
1) When the current date is larger than the date in O4 AND is smaller than the date in P4 than say "Update!"
- When the current date is larger dan P4, say "Expired!"
Every other scenario the cell must be blanc.
To do this I have made two seperate formulas:
1) =IF(AND(NOW()>=O4;NOW()P4;"Expired!";"")
How do I combine these into 1 cell? I have tried allmost every combination, I'm ready to throw my monitor through the windows
Is there a way to combine two if statements below.
IF(I2=0,J2*-1,J2)IF(I2
I am using VBA to put a formula into a cell. The formula is
VB: = IFERROR(VLOOKUP(LEFT(A5,11), 'New Quarterly Report'!A$11:E$37,3,FALSE), " - ")
But when I try to put this into a cell using the value option, I get a run time error 13: Type Mismatch. No problems while im compiling it but it comes up only during execution
VB: Sheets("Summation Table Template").range("B4").Value = "= IFERROR(VLOOKUP(LEFT(A5,11),'New Quarterly Report'!A$11:E$37,3,FALSE), " - ")"
When I try to put only the VLOOKUP, it works fine.
VB: Sheets("Summation Table Template").range("B4").Value = "= VLOOKUP(LEFT(A5,11),'New Quarterly Report'!A$11:E$37,3,FALSE)"
But I want the cell to not show #N/A when a value through vlookup is not found. Other formulas Iv tried are to use ISERROR with IF and also to store the formaula in a string and then put it into Range.Value but it still gives me a run time error 13.
I have been asked to go through some information which has over 200 000 lines, what I need to do is as follows:
where order number is unique, revenue source = "Unique"
where order number appears more than once, check division to see if it appears in more than one division, if it does revenue source = "Cross Selling", if it does not then revenue source = "Divisional Package"
I need all of the above in formula line which will be inserted in the revenue source column
I have attached the file with a sample of the information I am using.
I am trying to return a text statement using nested IF statements. In order to find the value in the IF statements, I have to use lookups.
Example: ....
creating Named Formulas to handle nested IF & And statements, to get around excel's limit of 7 nested IF statements.
First of all, is it possible to create a Named Formula with combined IF with AND statement such as: =IF(AND(F26>=54,F26<=77.99),"2x3"," ")--I am getting a periodic error message: "Cell with block IF function should not contain anything else."?
I am trying to: 1. Contstruct two separate Mega formulas using IF & AND to determine what range of sizes , located in Col "F", should fall into which SIZE_CAT, sample of ranges are as follows:
SIZE_CATSIZE IN TOTAL INCHES
2X3 54 thru 77.99
3X5 78 thru 108
4X6 108 thru 138
2. Name these two formulas, FirstNamed and SecondNamed
FirstNamed formula is:=IF(AND(F26>=54,F26<=77.99),"2x3",IF(AND(F26>=78,F26<=108),"3x5",IF(AND($F26>=108,$F26<=138),"4x6",IF(AND($F26>=138.01,$F26<=168),"5x8",""))))
SecondNamed formula is:=IF(AND($F30>=168.01,$F30<=198),"6x9",IF(AND($F30>=198.01,$F30<=234),"8x10",IF(AND($F30>=234.01,$F30<=270),"9x12",IF(AND($F30>=270.01,$F30<=320),"10x14",IF(AND($F30>=320.01,$F30<=500),"Larger","")))))
3. Combine two named formulas with a "Master Formula" such as:
=IF(FirstNamed,FirstNamed,SecondNamed)
I got the IF with AND formulas to work in a test columns, but cannot seem to get them to respond properly when trying to get them into a Named Formula(s).
I have attached an example of the workbook.
I have 1 original and 1 new IF statements, but need to combine into 1. Is this possible?
Here are my IF statements:
=IF(I15="WSP - DT",I29,IF(I29=0,IF(I30=0," ",+I29+I30+I39+IF(I25=0,0,(I25-1)*1900)),I29+I39+IF(I25=0,0,(I25-1)*1900)))
=IF(I13="Base Suite",IF(I15="Self Serve - Web",AK81),IF(I13="Base Suite + Key Account Advantage",IF(I15="Self Serve - Web", AK84),IF(I13="Key Account Advantage (Stand Alone)",IF(I15="Self Serve - Web", AK87))))
The names in " "s are set up in drop downs.
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 RelatedAt the moment I have 3 statements that i would like to streamline and combine into one. I would like to just press commandbutton1() and do the whole process rather than having 3 steps. Can I combine or I am better off doing it the way it is now?
Code:
Private Sub CommandButton1_Click()
'Prepare exports
'Delete cells and find and replace Y
Sheets("byemployee").Select
Rows("1:7").Select
Selection.Delete Shift:=xlUp
[Code] ........
=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 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 RelatedSheet one will contain the following:
DATE A B C D E
2/22/2008TRUEFALSEFALSEFALSERon
2/23/2008FALSETRUETRUETRUEPhill
2/24/2008FALSETRUEFALSEFALSETracy
2/25/2008FALSEFALSEFALSEFALSESharon
2/26/2008TRUETRUEFALSETRUEBill
On sheet two I need to list any date that has three or more true statements with the coresponding name.
EXAMPLE:
2/23/2008Phill
2/26/2008 Bill
I am trying to produce a report of supplier transactions sorted by area code. I have a spreadsheet of data consisting of Supplier Code, Supplier Name, Transaction Amount and Area Code.
I want to be able to firstly seperate the transactions by area, then also consolidate the data so it shows one row per supplier with total amount spent on that supplier and a transaction count on that supplier. I know how to sumif the transaction total and countif the transaction count.
However I have problems consolidating the suppliers in to one row per unique supplier and I also have problems nesting an AND statement in to the sumif/countif statements. I need additional criteria in the forumula to only count transactions in a specified area code.
I've attached an example spreadsheet to make it alot clearer. See results sheet in this workbook.
I am trying to use the following formula in Microsoft Excel 2007-
ROUND(IF(DataInput!$D$5 = "Black",0.81*E4*O4,(IF(DataInput!$D$5 = "Red",J4*0.025*E4*O4,(IF(DataInput!$D$5="Green",(57.00+IF(J4>25,(J4-50)*33.59,0))*O4))))),2
I want to use an IFERROR statement. If the results do not equal Black, Red or Green, I want the data to error so I can have the word FIX showing.
Using Excel 2010.
I extracted data from .pdf to Excel using Able2Extract. Now I need to scrub the output a bit.
I see commonalities in the data for the start and stop of each set of data that I can key in on.
Once I find the start and stop points for each set of data I would like to fill all rows in-between the points and then discard anything that remains outside of these boundaries.
I have something wrong in my logic, way too many rows are deleted.
Code:
Option Explicit
Sub GetLineSets()
'Purpose: Identify relevant line sets, delete all other rows
Dim wbBook As Workbook
Dim wsData As Worksheet
Dim strFormula As String
Dim lngRows As Long
Dim C As Range
Dim blnFlag As Boolean
[Code] ......