Correct Approach? (Multiple MID Functions)
May 29, 2007
* I have a complex Excel workbook with several worksheets.
* The first worksheet is for raw data, which consists of a single string of data, separated by spaces, to be uploaded to a mainframe. There is no consistent pattern of length of data or strings in between pieces of data, which can vary widely. An example of the data would be something like this:
XXXXX YYYYY NN XXXXXXX
* For testing purposes, I need to come up with a way to parse the data for easier viewing. The current approach is this:
* Take the string and divide it up into individual cells by using the MID function. In the above approach, this would split the data into four (4) different cells.
This is going to be a very complex workbook with data strings that can have 20+ elements in them, and have lots of separate records, etc. While this way should work (if everything is entered perfectly and no data structures ever change, etc.),
View 11 Replies
ADVERTISEMENT
Feb 14, 2012
I have several sources of information for client files. the number format includes a reference number and a month, eg 123/11, 456/02. In some cases, the numbers may be presented including the company initials, eg ABC/00123/11. I want to make sure all numbers on all sources are the same as I am using Vlookup and this reference no to copy information from one source to another. Sometimes when I change the format Excel sees this as a date, eg 11/11 is 11-Nov, and in some cases Excel sees a fraction, eg 123/11 is 123 11ths or 123 divided by 11.
I can easily take out the reference no with a find and replace FIND ABC/ replace with . But this often results in a number with leading zeroes, eg ABC/0123/06 will leave 0123/06.
I am using Excel 2003.
View 1 Replies
View Related
Jan 27, 2009
I trying to populate cells with a correlation function using VBA, I have set up dynamic references/arguments for the fuction to take on however I just can't get it to work. Currently instead of populating the formula into the cell, it's just populates it as a string based on what's entered below =correl(Ystart &":"& yend, xstart&'":"'& xend").
FYI - The correl function takes on two ranges,. I've predefined these below.
Sub testing()
lastcell = ActiveSheet.Range("c575").End(xlUp).Row
firstcell = ActiveSheet.Range("c1").End(xlDown).Row
Ystart = ("b" & firstcell)
yend = ("b" & lastcell)
xstart = ("c" & firstcell)
xend = ("c" & lastcell)
Range("c575:c580").Formula = "=correl(Ystart &":"& yend, xstart&'":"'& xend")"
'this works
'Range(xstart, xend).Select
End Sub
View 9 Replies
View Related
Feb 8, 2008
Disclaimer: I do not know the correct "lingo" to describe Excel functions and formulas, and I'm not really sure if Excel is capable of doing what I'm proposing, so please bear with me.
I'm not an avid Excel user, but I've got a question on my current problem. I've got a spreadsheet with several columns on it for my retail sales business. The spreadsheet is used to calculate my selling price based on my dealer cost of a unit plus a percent markup.
Currently, the spreadsheet is set up so that the "percent markup" column can be changed so that I can enter, say, 20%, and it will automatically spit out my selling price based on my cost divided by .80(for example).
This works fine, and I like it. However, now I have over 70 different items on the list, and the higher my cost is on an individual item, the lower percent markup I am able to make. So currently, I'm going in manually and entering a different "percent markup" for each item, depending on my cost of the item.
I'd like to learn how to define parameters for the "cost" field that will directly input to the "percent markup" field. For example, I'd like all items that cost me between $2,000 and $5,000 to have a "20" inputted into the "percent markup" field. For items that cost me between $5,001 and $10,000 I'd like to have a "15" automatically inputted into the "percent markup" field, and so on. I'm envisioning a small chart off to the side of the spreadsheet that would look something like this:
Price range percent markup
$2,000-$5,000 20
$5,001-$10,000 15
$10,001-$15,000 14
$15,001-$20,000 13
and so on and so on....
The chart (such as above) would allow me to simply change the "percent markup" inputs and the "price range" inputs at will to allow me to affect my spreadsheet and eliminate the tedious line-by-line insertion of each "percent markup" number by hand.
View 9 Replies
View Related
Oct 10, 2008
I'm using a on error goto approach, heres the code.
View 2 Replies
View Related
Apr 22, 2014
I finally found a program that can out put the amounts from a schedule k-1 form. Now comes the hard part, adding logical formulas.
Some of the line items either have an amount to or not. What the pdf2xl program does is output where if the line item has an amount to it, it would add the amount right below the line item. If it doesn't have an amount, it would just add the next line item underneath. Question is what is a good approach to get this to automate, so if line items have an amount, then that amount is known to be line item cell above. Here comes the tricky part as well, some line items have alphabets to them, so a particular line item say 11 would have A-F or line 13 would have A-W. What is a good way to have the line item output the line item + alphabet?
Column A & C = Line Item
Column B & D = Alphabet
Link to the IRS form [URL] .........
Excel 2010
A
B
C
D
1
1
Ordinary business income (loss)
15
Credits
[Code]....
View 9 Replies
View Related
Mar 18, 2013
how to calculate debt repayments using this approach.
As shown on the attached file repay issue.xlsx I have an example where a loan is taken out regularly (in the example every 6 years) but repaid over a shorter period (in this example every 3 years).
Using "IF(MOD(ROW" the formula identifies correctly when the loan has to be renewed, and calculates correctly how much needs to be borrowed.
C14 =IF(MOD((ROW(A14)-ROW($A$8)),B$3)=0,B9*C$5
Because Row 14 is six rows below the start (Row 8) which is the interval set by B3, it enters the loan percentage
(C5) of the asset value (B9) into C14.
Problem comes in the following rows. I need the formula to establish how many rows the "active" row is below the last "renewal", then deduct one fraction of the payback period for each row. By sheer coincidence the formula below works on the second row of the datsabase and deducts one third of the loan value because C8 WAS the previous "renewal", and row 9 is one below it:
=IF(MOD((ROW(A9)-ROW($A$8)),B$3)=0,B9*C$5,IF(MOD((ROW(A9)-ROW($A$8)),B$3)=1,C8-(C8*(COUNT(A9-A$8)/C6))))
I cannot "hard wire" the cell references into the formula because the renewal intervals and number of paybacks range from 25 year mortgages paid every year without being renewed to a one year loan on an item renewed every two years
View 2 Replies
View Related
Jan 8, 2013
I want my IF function to populate two cells with two values if argument for IF function is found TRUE. Is it possible?
IF(logical_test, [value_if_true], [value_if_false])
value_if_true = return multiple values in mutiple cells (for example put number 8 in cell A2 and number 10 in cell A3 if function is TRUE)
How would I do that? I tried putting IF(logical_test, (A2="8",A3="10"), [value_if_false]) but it is not possbile...
View 3 Replies
View Related
Feb 15, 2010
I'm trying to create a formula that will added the correct amount in the correct cells, I have create a dunny sheet in trying to achieve this. If Cell B8:B11 = ABS or Dum that any points won should be added to Cell L8:L11 right now its adding it into K8:K11. If Cell B8:B11 = is Blank any player points should be added to cells K8:K11. I'm using this formula throughout cells K8:K28 =IF(J8>J25,1,IF(AND(J8<>0,J8=J25),0.5,0)) Any thing in red is incorrect anything in blue is what I'm trying to achieve.
View 4 Replies
View Related
Jan 26, 2014
We receive about 20 sales files of several hundred lines of data each day from various agencies. I want to create a macro / VBA code which checks that the data submitted is correct so that we can upload it into our database without import errors and / or having to manually check each line of data.
I envisage something like an output report:
#####################
149 entries
Column A - Date - OK
Column B - Customer_Phone - Errors (Should be 11 digits)
Row 21 - Customer_Phone - Error (Not 11 digits)
Row 108 - Customer_Phone - Error (Contains letters)
Column C - Outcome - OK
Please correct and re-check.
#####################
View 4 Replies
View Related
Jul 3, 2008
I have a table with 3 columns of dates and then a column with Set # that I
feel in the box #.
I need to see how many items processed for each set per day.
Example:
[url]
The problem is that it counts the correct amount but not with the correct
dates.
The formula that I use is:
=SUMPRODUCT(--($I$3:$I$8<>"")*(($C$3:$C$8=39601)+AND($E$3:$E$8=39601)+AND($G$3:$G$8=39601)))
View 14 Replies
View Related
Jul 1, 2008
I have over 60 sheets within a workbook. There are some sheets that I want to hide or unhide depending upon the macro. I have the MR and searched in several areas but keep coming up blank with how to either select or hide these sheets.
This is from the MR:
Sheets(Array("Process", "Utilities", "CodeRef", "DataRef (3)", "DataRef (2)", "DataRef", "Dept Summary New", "Summary_Dept", Summary_ Monthly")).Select
When I try to use this in the code it errors out.
View 9 Replies
View Related
Jan 25, 2009
For the odds range, i have already two cells(the upper(F9) and the lower(E9)) and i have a cell named the current odd(G9). What are the formula together for the attribut cell(H9) for the following data:
1.0 : The current price is outside and below the odds range spread. For example, if the range is between 2,00 and 2,20, the range’s attribute will be 1.0 if the current price is below 2,00
NOTE: Obviously the formula for this one is =IF(G9
1.1 : The current price is on the bottom edge of the “odds
range” figure. For example, if the range is between 2,00
and 2,20, the range’s attribute will be 1.1 if the current
price is 2,00
NOTE: The formula for this one would be =IF(G9=E9,1.1)
1.2 : The current price is inside the “odds range” spread
and is placed one or two clicks from the bottom edge. For
example if the range is between 2,00 and 2,20, the range’s
attribute will be 1.2 if the current price is 2,02 or 2,04
NOTE: I have a hard time creating a formula with this one.
2.0 : The current price is outside and above the “odds
range” spread. For example, if the range is between 2,00
and 2,20, the range’s attribute will be 2.0 if the current
price is above 2,20
NOTE: The formula would be =IF(G9>F9,2.0)
View 9 Replies
View Related
Jan 27, 2014
Script Example.png (Picture)
Script Example.xlsx (Example Workbook)
I have a report that I pull that I pull fairly often that is in this format and shows which footage of products each store is getting (out of over 1800 stores)!
Above is the format that it comes back as. And here is a spreadsheet that shows what steps I take to find correct values in detail.
So each FTG has an ID# and Desc. The ID is in text format and each ID is seperated with a comma, no space. Description also. Date is seperate by a space and comma. Stores change footages a lot so I want to find out which footage is effective today (1/27/2014) For ex: Store 63 would have the 5ft effective right now because we are between 5/23/08 and 5/22/14. So each ID# and Ftg Description is in the same order as Date.
When I have hundreds of stores, it is difficult to go through and get each one (even with the way I've been doing it.) But my ultimate goal is to create a macro to put only the current footage ID#, Desc, and Date in Columns C,D,and E. I'm not sure even where to start with doing a macro.
I usually just find the store with most ftgs, count them, insert that many rows after ID and Description columns, then do a text to columns (comma, delimited), sort by 2nd date column (so they all come up top) and then manually go through them and delete unneeded columns once I have them all.
View 7 Replies
View Related
Feb 27, 2014
I'm essentially trying to place a column that is largely based on another....a function that will calculate the following information
Carriers with 1 Vehicle - 25.00
Carriers with 2-4 Vehicles - 100.00
Carriers with 5-9 Vehicles - 200.00
Carriers with 10-24 vehicles - 400.00
Carriers with 25-49 vehicles - 750.00
Carriers with 50+ vehicles - 1500.00
In other words.
One column in my spreadsheet has number of vehicles. I want another column that will provide the according application fee as found in the information above.
View 13 Replies
View Related
Jul 9, 2009
If I want to create a function or sub that can be used by any userform or macro that I create for a workbook:
Where would I define that function? Within the Module?
Do I define it as Public?
View 4 Replies
View Related
Sep 25, 2008
I've tried many variations but I can't seem to find a solution - I hope you can help. I'd like a formula that will ideally achieve the following:
IF B5:F5 all contain the letter y then return "Pass"
IF B5:F5 AND G5:K5 all contain the letter y then return "Merit"
IF B5:F5 AND G5:K5 AND L5:K5 all contain the letter y then return "Distinction"
IF B5:F5 is blank return blank
IF there are Y's in B5:F5 and the total < 5 return "Fail"
I did a sample sheet to show the desired output but don't know if this forum supports attachments.
View 9 Replies
View Related
Aug 6, 2014
I've attached a sample of the data I'm using.
I have two spreadsheets (the samples for which I have shown side by side in Sheet 1 of the attached file).
Spreadsheet 1 is about 30,000 rows and too large for me to change the formatting and structure.
Spreadsheet 2 is the output I need and the format is required by other stakeholders.
In spreadsheet 1 I want to sum quantity in stock for Type 1, Type 2 and Type 3 for each product and allocate it to spreadsheet 2 according to the month in which the product expires. For example, there will be a total of 92 units of product 413302 which will expire in Nov, 2014. Therefore I want 92 to be placed in cell N6 of Spreadsheet 2.
Unfortunately the product number is not unique - there are multiple sub products in spreadsheet 1 but they all have the same quantities of stock. The sub products are referenced in other parts of the report so I can't consolidate by Product Number. This also prevents me from using the SUMIFS function as it will duplicate the number found in the sub products.
What I need, perhaps in a combination of functions, is to find the first instance of product 413302 in Spreadsheet 1 that is expiring in Nov 14, sum the product types and give the result in cell N6 of Spreadsheet 2.
View 3 Replies
View Related
Jul 31, 2009
Is there a way to SET a RANGE that can be used for further editing without the need to specify the range each time for each function to be perfomed?
I have a pile of Data, with a Calendar date and 4 columns of data for each date.
I want to make a summary for each year separately of one column of data (Total PL). How can I set a condtion that it will output a summary of data for a given year, e.g. 1990?
Even more useful might be setting up a table for each year, where I will be able to perform easy function like SUM, MAX, MIN but it will use data only from the specific Year in each row.
- Is there a way to make it simpler, but not having to specify the RANGE for each Year for each Function?
View 9 Replies
View Related
May 9, 2009
I am trying to somehow use multiple IF functions. I was able to use one IF function to disply '--' in a cell if a particular formula = 0. However I ran into a problem when negative values were displayed. I wanted the value to be a positive number. I thought I could use another IF function to multiply the formulas contents by -1 if it were <0, but I keep getting a #VALUE error in my cells. Is there a way I can have multiple conditions?
View 2 Replies
View Related
Dec 5, 2008
I am trying to create a custom function to act much like the database capabilities of excel in summing a column based upon multiple criteria of other columns. First, before I get too far, does anyone have an already built custom VBA function that will accomplish this? I find that using the built-in database functionality in excel is VERY slow!
Anyways, I am trying to pass an argument that contains multiple ranges (much like you can do with the SUM() function):
=MultipleSUMIF("E11:E14, F11:F15", "A1, E1", G11:G14)
The first set of arguments is the ranges that I need to look through. The second set of arguments is the values that I need to search within the first two ranges. The third argument is the column that I need to perform a SUM function on.
Instead of doing this, how can I create a function that will allow me to say, "Add another criteria range/value" and it will add another criteria to the list. I am currently passing a string, and that is not ideal, obviously.
View 9 Replies
View Related
Jun 23, 2009
I have a spreadsheet using Data Validation to offer a list of text values for cells in the range E6 - E100. I have a worksheet change funtion to change the colour of the entire row based on the text chosen from the list.
What I would like to add is an input box that will appear when the value in the cell = "COMPLETE" asking for a completion date and adding the inputted date to the cell in column I in the same row.
Existing Worksheet Change Code;
Private Sub Worksheet_Change(ByVal Target As Range)
'Colour code rows based on order status
Dim rng As Range, i As Long
Dim cell As Range, Answers As Variant
Dim Colors As Variant
Colors = Array(24, 15, 38, 44, 42, 20, 36)
Answers = Array("CLOSED", "SUSPENDED", _
"COMPLETE - Awaiting Inspection", "COMPLETE", "WORKING", _
"SCHEDULED", "READY")
Set rng = Range("E6:E100")
rng.EntireRow.Interior.ColorIndex = xlNone
For Each cell In rng
For i = LBound(Answers) To UBound(Answers)
If LCase(cell) = LCase(Answers(i)) Then
cell.EntireRow.Interior.ColorIndex = Colors(i)
Exit For
End If
Next i
Next cell
End Sub
View 9 Replies
View Related
Nov 11, 2006
I need to create a formula that counts the number of times that an age range appears within a column. In column G, there is a list of ages based on a demographic collection. The ages range from 13 to 50+. I want to designate another cell to count the number of times the characters between 13 and 18 occur within that column. I have =COUNTIF(G8:G20,"13") How do I add "14", "15", "16", "17", and "18"?
View 2 Replies
View Related
Jul 19, 2007
I have this formula
= COUNTIF(AT6:AY6,"F")+COUNTIF(AT6:AY6,"P")+COUNTIF(AT6:AY6,"M")+COUNTIF(AT6:AY6,"D")
that returns the number 2 (which is correct). However, if I precede it with
AZ6/COUNTIF(AT6:AY6,"F")+COUNTIF(AT6:AY6,"P")+COUNTIF(AT6:AY6,"M")+COUNTIF(AT6:AY6,"D")
it returns a DIV/0 error, even though AZ6 has a value of 24.
Surely 24/2 would return a value of 12? NB AZ6 cell value is derived from the result of a formula.
View 3 Replies
View Related
Oct 16, 2007
I have multiple IF functions in a formula and found out that the maximum allowed is 7 and should use Lookup instead. The formula is to calculate the Present Value of an amount with the corresponding interest rates and number of days left.
View 3 Replies
View Related
Oct 26, 2007
When using watch window to watch a formula that has multiple formula's in it, is there a way to see the result of 1 formula within the big formula?
E.g. say you have:
= SUMIF(A1:A11,"207",B1:B11)/COUNTIF(A1:A11,"207")
And you put a watch on that, but in watch window it shows the result of the whole formula, say if you want to watch only the SUMIF part?
Im using Excel 2003 by the way.
View 5 Replies
View Related
May 23, 2008
I am currently having trouble with what I thought was a simple IF statement. As you will see from the formula I want to complete a statement for every month of the year but Excel will not let me go past July. Is there a limit to the number of arguments in an IF statement and how do I overcome this? =IF($A$3="Jan'08", SUM(C7),IF($A$3="Feb'08",SUM(C7:D7),IF($A$3="Mar'08",SUM(C7:E7),IF($A$3="Apr'08",SUM(C7:F7),IF($A$3="May'08",SUM(C7:G7),IF($A$3="Jun'08",SUM(C7:H7),IF($A$3="Jul'08",SUM(C7:I7),"n/a")))))))
View 5 Replies
View Related
Mar 23, 2014
I have a set of 4 multiple choice answers B,C,D,E column "F" lists a # 1-4 which lets you know which column letter (b,c,d,e) the correct answer is in. I want to set up a formula that will copy the contents of the correct answer (based on that # identifier) and copy it into column "A" which is currently blank.
View 7 Replies
View Related
Feb 17, 2014
I am having trouble evaluating two conditions with nested logical operators to produce the output I need.
For example Condition 1 has 5 choices (A,B,C,D,E) and Condition 2 has 3 choices (a,b,c).
Breaking it down,
[Condition 1] option "A" always applies and should give "Yes" (No matter [condition 2], "A" always gives "Yes")
[Condition 2] option "c" always applies and should give "Yes" (No matter [condition 1], "c" always gives "Yes")
[Condition 1] option "D" never applies and should give "No" (No matter [condition 2], "D" always gives "No")
[Condition 1] options "C" and "B" only apply if [condition 2] is "b" (if so, the answer is "Yes")
What I need is the equivalent of:
if ((([condition 1] == A) || ([Condition 2] == c)) || (([condition 1] == B || C) && ([condition 2] == b)), "Yes", "No")
View 4 Replies
View Related
Jan 1, 1970
I want to put a formula in a spread sheet that if block f5 is equal to or higher than 20% of block b4 then block d3 * .0 if less than then * by .0078
View 14 Replies
View Related
