I would like to incorporate a series of if statements in column D of my macro that looks at the labels in column C. If the label is 10 characters long, it returns the full 10 characters. If the label is 11 characters long, it returns the leftmost 10 characters.
- insert LR if AB5 is less than 20; - insert C if AB5 is less than 30 but more than 25; - if neither of the above is true, insert CLEAR if T5 contains something and if not insert B-T1-F10 if AB5 is between 240 and 40 and AA5 = 1.
The problem I have is that the B-T1-F10 is not being inserted where the other criteria are not met so I think I have not integrated the statements correctly?
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?
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.
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 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 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.
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.
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.
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 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 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
I have got a table with data from 2005 to date, (for example) For the purpose of what I am doing I need a column which shows date ranges between September 2005 - August 2006 to show as 2005/2006, then September 2006 - August 2007 to show as 2006/2007. I have done some research and seen that, Potentially, a nested 'IF' can be used but it can only be used 7 times which would cause a problem going forward....
Is this the only way or is there a better way (without using VB)?
1) What's the maximum limit of IF statements in a formula for MS Excel 2010?
2) I have 8 nested IF statements, but I am having trouble with the False part of the argument. I was able to get the False part to work for the 4th argument (because I simply put a comma and closed it's respective colored parenthesis) but the following 3 I can't seem to get and the following message pops when I hit enter "The formula you typed contains an error."
For the False part of the remaining 3 nested statement which is at the end of the formula I just type:
And close it's respective colored IF statement argument. I tried putting a comma at the end of each False part for the arguments but it pops with the message "You've entered too many arguments for this function."
Excel spreadsheet will contain a form that will consist of drop down boxes (data validation). Each drop down box will define the data that can be selected in the next drop down box.
The way I initially went about it, is creating nested IF statements. However, we all know there is a limit of 7 nested IF statements. I have 10! So below formula does not work: =if(B5=1,F5:I5, if(B5=2, F6:I6, if(B5=3, F7:H7, if(B5=4, F8:J8, if(B5=5, F9:H9, if(B5=6,F10:G10, if(B5=7,F11:H11, if(B5=8,F12:H12, if(B5=9, F13:I13, if(B5=10, F14:K14))))))))))
I tried other workarounds such as CONCATENATE, or & signs. No luck. VLOOKUP does not work also, because there are multiple columns in col_index_num. Anything else I try gives me this message: “The List Source must be a delimited list, or a reference to a single row or column”
What should really happen is this: User selects value in first list box. Second list box shows values associated wih the value from the first drop down only.
I have the following worksheet ,named "Calendar", to make an annual calendar and I'm wondering if there's a way to use it to make a calendar in worksheet named "NovemberDaily". On the worksheet named "NovemberDaily" I want to put in cells C14, G14, K14, O14, S14, W14, and AA14 "11/1/2007" if the weekday of the week matches the date"11/1/2007". Is this possible or is there an easier way of accomplishing this. I'm needing the date (e.g. 11/1/2007) in the cells mentioned because it ties into another formula that I'm using later in the worksheet.
I keep getting this error message: Error 429: ActiveX Component Cannot Create Object
I fond a site that explained the problem: "If Microsoft Excel 97 is installed after an application created with Microsoft Visual Basic 5.0 that uses DAO is installed, Excel does not add the DAO design-time licensing key. This licensing key is required by Excel, but is not required by applications created with Visual Basic." But I can not figure out how to fix it. I changed the registry and added the licensing key, but I still got the error.