I have 4 lengths in four columns in a random order, and need to compare them to see whether they are equal lengths.
I Have figured out how to order them so I can compare them, but can't think of a formula to show whether they are equivilent (eg 1000m = 1km) True or False outcome is fine.
I have to sets of data, each in a sheet with the first column as identical for both sheets. Sheet2 contains two series of 6 rows, each for a specific first column (""B Code").
Now I want to find the values of "TCT-C" column in sheet1 (for each range of 6 rows that the "B Code" is match) which corresponds to the row number that "TCT" in sheet2 is maximum (again for 6 rows). The point is, I need to shift to another series of 6 rows in sheet2 once the "B Code" does not match.
I've got a long formula here. If the resulting expression is equal to "0-0" I want it to go blank as if it was an error, and if it isn't, I want it to show the resulting expression as normal.
View 11 Replies View RelatedI'm trying to account for the date and have it change if the original falls on a weekend. I wrote it using the Weekday function, which I believe is a worksheet function and not a VBA one, as I keep getting a run-time error 5 (invalid procedure, call, or argument). Either that or I have something programmed wrong in it.
View 8 Replies View Relatedif there is an eomonth() equivalent in VBA?
View 9 Replies View RelatedI need a loop function (i guess a do while or do until code) so whenever the word 'Non-Current' appears in colum A enter a 1 in colum E until the word 'Total Non-C' is reached at which point the loop must end.
Such as:
A B C D E
NON-CURRENT 6 4 5 1*
ABSA 4 5 2 1*
BARCLAYS 3 2 8 1*
NED 0 8 6 1*
TOTAL NON-C 4 6 7 0
I've been learnig VB applying it to automating some excel workbooks. What I'd like to know is if there's an equivalent funtion in VB to the IN function in Pascal. The In function in Pascal used to work like this:
If I used the KeyPress (Function in Pascal), I could evaluate which key was pressed and compare it to an array of options. For Example:
HTML Code:
if Chr(KeyPressed) IN ["0".."9",".","+","-","*","/"] then
Expression = Expression + Chr(KeyPressed)
Instead of Comparing each option separadtly. Now my question is whether or not there's an equivalent function in VB.
I have two worksheets: "Report" & "Hou Jobs". In Report worksheet, I need a code equivalent to following formula so that it returns value in cell D5.
If the values in cells B5 & B8 change, the value should update itself.
=VLOOKUP(B5,'HOU Jobs'!A4:B667,2,FALSE)
When I tried len(nYear) where nYear is a number like 2006, I don't get the number of characters in the number. I know that's confusing strings with numbers, but...
Is there a VBA function that returns the number of numbers deep in an integer; ex. if nYear = 2006, the function would return 4?
I could easily do a < or > line of code, but I'm just curious if there's another way to do it.
I have a given length of steel bar...Example(550 feet)
I have various cut lengths that I can pick from...
14'-10".....13'- 9".. 12'-8"........(up to 6 different lengths)
I need to know the best combination of lengths to cut the long bar into to have minimal scrap left when we get to the end of the 550 feet.....
Cut as many of the longest cut lengths possiable.....Cut as few of the shortest cut lengths possiable
Due to the fact that I will have to convert this to a PLC application when completed, I want to find a mathmatical way to do this inside excel and not use the "solver"....Assuming that I/we can come up with an equation to do this .....
i am trying to finish a spreadsheet off using cos functions. I want to convert 26 degrees into 0.8988. when i use my hand calculator i type 26 in then press cos and it converts it to 0.8988 which is correct, but when i try to use the excel cos function it gives 0.646919322 which is in-correct.
View 5 Replies View RelatedI have a worksheet (named Deduction Worksheet) that is a running record of all part removals. A removal is denoted by a row entry that contains removal particulars. The first column (Column A) in each row contains a unique number for each part. Column K shows how many part units were removed in that removal:
Column A
... Columns ... B to J ...
Column K
Unq001
various
[Code]....
In the code above 'crng' is the criteria range, 'sValue' the criteria and 'srng' the sum range (which follows the normal SUMIF function within a worksheet). Both the 'crng" and 'sValue' values contain text, / and numbers.
note that other elements on the userform code look at data on other worksheets (not sure if this will effect this).
I've searched the boards & haven't found anything as uncomplicated as what I'm looking for. I know I could use Sumproduct, but for something this simple is there anything I can do with Sumif? This in effect is my worksheet.
AB
1AD200=SUMIF('Sheet2’!B:B, A1, 'Sheet2’!C:C)
All I want to do is change the criteria to be the equivalent of A1 OR B2 (ie checking the range for either match. No way to do that in a sumif? I'm looking for the least complicated, easiest to adjust/edit formula possible.
When I need to return numerical data with multiple criteria, I use the SUMIFS and it works fine. But when I need to return a text string, the function is not called for. What function can I use to make this happen? I've tried SUMPRODUCT, which I've had success with in the past, but it's not working this time. Maybe I'm laying it out wrong, but this is how I currently have it:
=SUMPRODUCT(column of text to be returned,((column of criteria1)=criteria1)*((column of criteria2)=criteria2))
Do I need to fix this formula or use something else?
Does anyone know the VBA equivalent to using the Shift-Arrow keys on the keyboard? I am wanting a macro to do this for me. Specifically I am wanting to use Shift-Arrow Right.
View 9 Replies View RelatedI am in need of some excel advice relating to date calculations. Basically I need a function that is the equivalent of WORKDAY but for hours instead. I have a series of events that take a certain length of time to complete, most of them less than a day but some more than. By way of example see the screenshot below:
In reality the last three operations would have to take place on the 27th of April, with the Welding operation starting on the end of the 25th around 7pm. The plant is running a 24 hour day, and works 5 days a week. How can I calculate the times in hours offset rather than going day by day? I need to account for
* Weekends
* Fixed Holidays
* Operations running as seamlessly as possible
Any advice welcome. I have attempted to use WORKDAY with the number of days to deduct rounded to the nearest day and then subtracting the operation time but this results in errors where operations would cumulatively go over a working day. The objective is by knowing when the end product is needed and knowing how long each operation takes it is possible to discover when to start manufacture. VBA or Formula code is fine as this will be integrated into a VBA project.
I have three columns, one column is time every two seconds with data associated that time, and one is time every minute with an associated tidal height.
I want to sort my data so that for every 2 seconds I have an associated tidal height for that minute.
eg:
what I have:
Time Time Tidal Height
(2 sec intervals) (min) (meters)
1:15:00 1:15 1.342
1:15:02 1:16 1.221
1:15:04 1:17 1.115
...
1:15:58 1:43 0.024
1:15:60 1:44 0.012
1:16:00 1:45 0.008 ....................
I have a performance extract from a system which has performance for multiple accounts but the data is off different lengths and shown vertically. I wish to move the data onto a new sheet but rotated(transposed) horizontally
Attached is a snapshot of the report that is usually about 20,000 lines. See input and output tabs.
Is there any way that a macro could be created to do this.
Performance.xlsx
My data is as follows, all in one cell:
10015200 US Government Bonds 18,369,423.52
10012301 US Government Bonds 10,232.21
There are many varying lengths of digits. I am trying split off the numbers
to the right of US Government Bonds into another column. Here is my
formula:
=RIGHT(A7,IF(ISERROR(FIND(" ",A7)),LEN(A7),FIND(" ",A7)))
My result is 9,423.52. Its not pulling the whole number.
What do I need to change in the formula?
Say I am downloading a column of stock prices and it cannot be predicted precisely how many rows will be contained in each downloaded column.
The column is, however, always Column E.
Two formulas must be computed.
1) Last row with a price in column E / average (all rows, beginning with row 2 with prices in column E)
2) Last row with a price in column E / average (last 50 rows with prices in column E)
Can Excel manage the variable number of rows required for these formula, and if so, can somebody recommend how to write these formulae?
Is there a formula out there that can check if a cell begins with 1 of a range of numbers of different lengths?
Example does A1 begin with A2, A3, A4, A5 where A2 = 123, A3 = 4567, C3 = 89.
I need the equivalent of a double conditional in the Function SUMIF.
In my example (a test file is attached), I have used hypothetical stock trades as a test case.
Column B has the type of order (Buy or Sell).
Column C has the stock trading symbol.
Column F has the net amount of the transaction on that line.
I want to populate Columns I & J, where I & J are the total Buy and Sell cost for each stock listed in Column H.
So, for example I need to calculate the total "Net Amount" of "Buy" transactions for Stock ABC, and put it in Cell I2. I also need to put the the total "Net Amount" of "Sell" transactions for Stock ABC, and put it in Cell J2.
I'm sure that looking at the attached sample EXCEL workbook, will make it clearer than all my verbiage.
So, conceptually, I need to evaluate Columns B, C and F and put the sum of transactions in Cell I2 for those cases with "Buy" in Column B AND "ABC" in Column C.
If there is no syntax for a double conditional in SUMIF, then I'd be happy with any formula for Cell I2 that accomplished the tax.
I'm not terribly familiar with array formulas, but have used them on occasion if that's what's required to emulate a double conditional in SUMIF.
There is a way to use SQL sentences to do queries in a table in Sheet1 (A to F) to put the output in Sheet2 the values in columns A, B and E from table in Sheet1 when values in column B are equal to 5?
I mean, is possible to do this within the same workbook? or a equivalent VBA code?
I am new to Excel but not programming and I am looking for a recommendation for the following. I have a spreadsheet that simply takes the length and width of an area and computes the square feet and yardage and other sundry items. I am entering the feet/inches as follows:
Example: 11.3 (equals 11/ft 3/inches)
The correct decimal conversion should be 11.25 but, obviously, it does not know that the number to the right of the decimal point is an indicator of inches. (ex.: .5=.42, .7=.58, .9=.75, .11=.92)
I have approached this from the stand point of an IF condition, finding the position of the "." and grabbing everything to the right (+1) but I understand that the limitation is 7 nested IFs.
Can someone get me kick-started on what the best approach would be to get my entry to convert to the true decimal equivalent? Currently, I am simply doing the conversion from memory but I would rather automate this sometimes errant approach.
I have used ROW function to determine the facets lengths and the number of internal angles and also the radius's
the offset figure is the distance the track or pole is set from the frame this can be adjustable by us or the end user
the radius measurement is a minimum and a maximum that the aluminium can be bent, will probably
be set parametrs to work with
Now bay widows come in all sorts of shapes and sizes
90 degree angle bays
2 bend bays
3 bend bays this is why I am using the row function, once the measurments are entered can excel generate
I have attached an excel file. What I would like to find is the average of column D (number) with respect to column A (Genotype ) and also different wave length (Column C). i.e.:
Genotype 1 to its corresponding value in column D for wave length 450
Genotype 1 to its corresponding value in column D and for Wave length 470 to and so on.
Attached File : test2.xlsx‎
I'm trying to compare two columns of names that are different lengths. I need a formula that will return the differences. I've tried everything my nonanyltical mind can think of
View 5 Replies View RelatedI am trying to do a VLOOKUP using a list based on our Part numbers in Excel 2007. When I do a normal sort it groups the numbers 1 thru 9 by the number of digits, giving me multiple list of 1 thru 9. When I do my lookup it will not find the shorter numbers. Is there some way to sort these Part numbers in order regardless of the number of digits?
View 2 Replies View RelatedI am half way through writing a bigger macro, but am stuck at this stage.
I need to copy from a block of data as shown:
However, this column varies in length each time, and I only want to copy down to where the entries stop.
I also only want to copy W1:X6, and columns Y, AA, AB, AD (but only down to the end of the column)
I've tried using the End(xlDown)).Copy function, but obviously this doesn't work with the block up the top and the gap between the block and the columns
I have a column of data that contains suburb, state and postcode (zip code). I can extract the postcode quite easily using:
=RIGHT(H2,4)
However, sometimes there is one word for the suburb, and other times there are two or more words. Also, the state abbreviation contains either two or three letters:
MARYBOROUGH QLD 4650
BRAY PARK QLD 4500
TEA TREE GULLY SA 5091
Does anyone know of any code I could use to extract the three pieces of information into three new columns? (I.e. Suburb, State, Postcode)
I imagine the formula would contain some method of counting the number of paces; however, the number of spaces between each word may be more than one space.