Testing For Values That Vary Within Specific Tolerance?
Apr 22, 2014
I'm trying to identify where a set of values 'agree' within a specified tolerance.
I have a list of scores from different sources, and wish to both average them (easy to do) but also to identify how well they 'agree' by flagging those which have a difference above a specified tolerance. The numbers are all integers, and the tolerance will be an integer.
To give an example, I might have cells with values (2,3,2,2) and these agree within a tolerance of 1. If I had cells of (2,4,2,2) these wouldn't agree. I'd like to flag (by the creation of an 'X' within a separate column) where these cells do not agree.
I've done this previously by using chained If and Or statements (e.g. if A1 = D1 or A1 = D1+1 etc etc) but this is laborious and means I have to change logic every time I add a new sample.
Is there a way of specifying a range of cells (e.g. A1,D1,H1, J1) and seeing if the values of these cells agree to within a stated tolerance? I'm happy to define named cells to specify and argument/attribute.
View 2 Replies
ADVERTISEMENT
Jul 25, 2007
I am trying to replicate a method , it reads: 'any data point that changes by more than or less than 10% from the previous value, and does not remain at the new level for more than 6 days, should be removed.'
Thats a bit vague. The process is going to involve if statements, etc. I've attached an excel spreadsheet with some solutions I've tried. Neither of which work. The aim is to get the same answers as the highlighted column. Columns B and C replicate the method answer, but do not state the process. The letters L represent values less than 25. Its the letters D I'm trying to eradicate.
View 2 Replies
View Related
Dec 25, 2011
I am running excel 2010. I have set up a variable " testvalue" type variant
I would like the prog to step through a row of data which can contain text or numbers. If any number is greater than 3,000,000 I want exit the do. However, if no number is greater than 3,000,000 I want to record this as a "bad file". I have the follow code which trips at the first "case".
Code:
testcol = 1
testrow = myTextRows - 1 'penultimate row
Do While Len(wbText.Sheets(1).Cells(testrow, testcol)) > 0
testvalue = wbText.Sheets(1).Cells(testrow, testcol).Value
Select Case testvalue
[Code] ........
View 3 Replies
View Related
Aug 8, 2009
I use this formula to calculate if a move is out of tolerance =IF(ABS(B1)>=1,"Y","N") This will work on most of the stuff we load. I would like to modify it to if b1 = "Crude" then use =IF(ABS(B1)>=.5,"Y","N"), or if B1 = "Resid" then use IF(ABS(B1)>=2.0,"Y","N"). Is it possible to include the 3 tolerance bands in one formula?
View 9 Replies
View Related
Mar 3, 2007
I am successfully using VBA to check a cell on one sheet against a cell on another sheet.
How easy would it be to check if this is the same OR less than 2 or more than 3? I have done part of this using nested formulas but cant find anything to help me with the syntax in VBA
The code I am using is
If W1.Cells(a, 3).Value = W2.Cells(b, 3).Value Then
View 4 Replies
View Related
Jul 6, 2007
I have two numerical columns of different length. One presents the signal and another one the background. Most of the background numbers are found in the signal column but they are not exactly the same and not located in the same rows. The numerical values are the same within some tolerance that I can specify. What I am trying to do it to remove all the background values from the signal column.
View 8 Replies
View Related
Jul 17, 2007
I have a large amount of data pertaining to many different runs in which I am given the mass to charge ratio of a compound. My goal is to identify these compounds, and to ease this process I am trying to find a formula that would “line up” near identical masses while providing a blank if no mass was found in that run. For example, if I have:
Run1
185.041
196.073
200.454
667.175
Run2
185.040
196.074
398.452
667.175
Run 3…etc
I would like to have it arranged:
Run1Run2
185.41185.040
196.73169.074
200.4540.00000
0.00000398.452
667.175667.175
It would be important to have some sort of tolerance setting, as the readings are not always exactly the same. Also, I’d normally have about six runs of data to line up.
View 8 Replies
View Related
Feb 8, 2011
If I have a cell with this info: [6126]BOB SMITH
What formula can I use to get BOB SMITH. The length of the name will vary. The number will change, but will always be 4 digits and will have the brackets. [XXXX]
View 8 Replies
View Related
Oct 26, 2008
Below is a short segment of my excel spreadsheet:
A B
1020.00242-89.84
1020 -88.11
1021 -85.3
1021.494-80.41
1021.49 -86.98
1030.04 -89.4
1030.042 -88.26
1030.94 -79.98
1030.933-81.5
1030.961-85.87
1040.0418-88.77
1040.391-87.3
1040.291-82.94
1040.016-84.12
1049.82 -84.7
What I need to do is write a macro that will find duplicates in Column A, within a changeable tolerance, say 0.1 (10%).
After finding all duplicates within a tolerance in A, I need to make another "Master" worksheet with the Duplicates from A, and their counterpart in B. So if A1 and A4 where within 10% of each other, the "Master" worksheet would contain:
A1 B1
A4 B4
using the values, giving:
1020.00242-89.84
1021.494-80.41
I tried using SUMPRODUCT and some other functions but just can't seem to put my finger on this one. I'm sure it's not hard and am overlooking something.
View 9 Replies
View Related
Sep 13, 2007
I am trying to apply conditional formatting to multiple columns based on a percentage varience of the value in the first cell of each column. I'm sure this is not a new concept however I have been unable to find any advice when searching. I have attached an example of my problem as a picture speaks a thousand lines of code.
View 8 Replies
View Related
Apr 17, 2014
Is it possible to vary the size of data markers (eg spots) in a chart by their value?
View 2 Replies
View Related
Feb 5, 2009
You can always add numbers together. however, how can you vary columns based on a number?
for example, i can always do things like the following to change the number of rows selected in a column based on the variable numRows.
View 2 Replies
View Related
Feb 23, 2007
I'm trying to amend a chart via combo box. Cell C15 returns a dynamic named range, based on a vlookup.
My problem is in having the code pick up the value that C15 returns, rather than the formula in it.
Set rng = Sheets("Chart lookup").Range("C15")
ActiveChart.SetSourceData Source:=rng, _
PlotBy:=xlColumns
View 7 Replies
View Related
Aug 7, 2009
I need to be able to get a true/false from a tolerance percent.
Here is an example of what I am trying to do
cell a2 is Nitrogen
cell b2 is (Known gas%) 2.4800%
cell c2 is (unknown gas%) 2.4963%
cell d2 is =b2-c2 and I get the answer no trouble there. what I need is to take the answer in cell d2 and set a plus/minus 2% tolerance in cell f2 and get a true/false comparison.
View 4 Replies
View Related
Sep 4, 2008
a lookup and match of two values.
For example, in Sheet1 I have a unique transaction reference number and a £ value.
eg. 60231568 £38.05
In the next sheet (Sheet 2), I have a summary of data, and I need to return the company name...
60231568 £38.04 Company X
60231568 £15.12 Company Y
60231568 £10.11 Company Z
60245871 £105.11 Company Q
The look up needs to match trans no, amount (within 3 pence threshold) and return in this example company X.
View 9 Replies
View Related
Apr 8, 2008
I have an unknown number of rows that represent parts and 6 columns that represent part dimensions. I need to find and output matching pairs of parts that are within certain dimensional tolerance ranges.
For example:
Dimension 1 Dimension 2 to Dimension 6
Part 1
Part 2
to Part X
-I would need the code to check if Dimension 1 of Parts 1 & 2 were within a certain range and if Dimension 2 of Parts 1 & 2 were within a range etc. then (if all of them were true) output them as a pair. Otherwise, it would continue to search for paired parts. Once a pair is found, those parts can't be used again. How can I do this?
View 6 Replies
View Related
Aug 6, 2008
I have a DDE linked cell that is constantly updating (cell A1), I want then to have a cell (cell B1) that changes only when cell A1 differs from value currently in B1 by a certain percentage, then check B1 for the same tolerance level against A1, and repeat. I can do this in a number of ways in VB, however when VB code is running causes the DDE link not to update and since the update is constant I cannot use selectionChange and Change VB Worksheet function result in the same error.
View 6 Replies
View Related
Mar 12, 2014
I am trying to use HLOOKUP to find a location in another sheet and display all the names in that specific location. Also can change location at will and will display the names for that location.
I have attached a spreadsheet that kinda has what I want. It does not have to be HLOOKUP but that is all I know, Hlookup and vlookup.
View 4 Replies
View Related
Feb 4, 2014
I'm trying to write code that will open files within a specific folder. However, the end of each filename is going to vary over time to the point that I'll never be able to predict the exact filename.
Is it possible to write something like:
Code:
Set wb = Workbooks.Open "C:\folderfile_filenumberxxxx.xlsx"
Where the "xxxx" signifies the variable element of the file name that could be any four characters? Failing that, is there a similar possible alternative?
View 1 Replies
View Related
May 29, 2007
i have data that will vary from item to item in the $ of rows associated
and the # of columns to be dealt with.
item1 Line 1
Line 2
Here i want to have a formula put and copied across X columns
item2 Line 1
Line 2 other data going across in the columns
Line 3
Line 4
Here i want to have a formula put and copied across X columns
item3 Line 1
item4 Line 1
get my meaning??
columns involved will be known before i get to this bit of code !!
as an example lets say it's 8 columns
using the record macro i get this bit of code
Range("E14:M14").Select
Selection.FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
This is fine but i don't know what row or the columns are before i run the macro so the Range
("e14:M14").select wont really work.
I've sent sometime going through MRexcell searching for tips but found nothing also googled for dynamically pasting across unknown columns
this is close to my idea in my mind conceptually
Range("C2:C" & LastRow)
but it doesn't make the columns dynamic eg column 8 being = H and then P = 8th column on and the last with data.
View 9 Replies
View Related
Aug 20, 2012
It's a worksheet for testing milk tanks. The issue is as follows:
Column F = Chart Error/This can be either a positive or negative whole number
Column G = Tolerance/This is always a whole positive number
Problem is I want Column H = Status to say "Pass" or "Fail" based on meeting the Column G number. But it's only the number itself (not whether it's Positive or Negative) that matters in determining the P/F status.
Examples:
Column F= -20
Column G= 15
Column H= Fails
or
F= 20
G= 15
H= Fails
The formulas I've been coming up with will show one or the other as Failing but not both + or - numbers if they fail to meet the G Column tolerance number.
View 6 Replies
View Related
Jun 16, 2009
I created a macro on an Excel spreadsheet:
Sub Macro1()
Range("C11:D19").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
Range("E5:E20").Select
Selection.NumberFormat = "0.00%"
Range("D24:E43").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
ActiveWindow.SmallScroll Down:=24
Range("D58:E339").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
ActiveWindow.SmallScroll Down:=57
End Sub
When the macro is run, it works with the exact amount of data that I've entered.
However, because I have specified a specific range, if I add a row or column of data to the spreadsheet and run the macro again, it throws the whole thing off (certain cells get formatted when they shouldn't and others aren't formatted)
How do I tell the macro to look in the spreadsheet for varying ranges of data and format those cells?
View 9 Replies
View Related
Oct 30, 2006
I am attempting to create simple charts to show survey results 2005 v 2006. I have a single series in a bar chart - chart type. The wizard initially makes both bars the same fill color. I right-clicked one of the bars to start the Format Data Series Wizard. On the 5th tab, "Options", I ticked "Vary Colors By Point". A new color was assigned to the top or first bar. So far so good. But instead of these colors, I would like to use a different fill effect for each data point in the series
So I walk through the wizard and desiqn a fill effect and click ok. The fill effect is assigned to both data points. I checked where I had ticked "Vary Colors By Point"
It is still ticked. Can I override this somehow so that each data point can have a unique fill effect?
View 3 Replies
View Related
Nov 2, 2008
I've currently written the following code to make sure exactly 2 single cell selections are made. But now, I want to extend this code to make sure only cells from rows 5 & 7 are selected and the cells are in the same column.
View 2 Replies
View Related
Sep 23, 2013
I am using MS Excel 2007 and I am trying to create a macro which will copy column E and paste the data in column E to the bottom of column D. However each week the amount of rows in these column with vary, they will always be the same amount of rows in column E as in column D but there may be 20 rows one week and a 100 rows the next.
The formula I currently have is below but this will only work for a specified number of rows. How I could change this to work for any number of rows?
Code:
Sub IPT()
'
' IPT Macro
'
'
Range("E1").Select
[Code] .....
View 2 Replies
View Related
Oct 18, 2013
I have a form that information is entered in. At the end the user can then save the information in a spreadsheet by pressing a command button. The updating to the spreadsheet works, it is the part where the last entry is tested against the info on the form to prevent a duplicate entry that do not work.
I have tested that the program reads the info from the spreadsheet (see the commented lines).
I can attach the whole spreadsheet, but some of the entries on the form are linked to another spreadsheet that make it a bit cumbersome to attach two files.
VB:
Private Sub cmdUpdate_Click()
Dim lRow As Long
Dim ws As Worksheet
Set ws = Worksheets("TonnageInput")
[Code] .....
View 2 Replies
View Related
Oct 1, 2009
I am trying to write an Excel formula to test if there is data in pairs of cells and if there is I want to copy the entire record to another Excel spreadsheet. Example: if there is data in row 1, column A and data in row 1, Column C or row 1 column D, then copy this record to another spreadsheet; if there is data in row 1 column B and data in column C or column, then copy this record to another spreadsheet. Since there is no data in row 4, column C or column D, this row would not be copied. Row 1, row 2, row 3, and row 7 would be copied to another spreadsheet. The data can be numeric or alpha numeric.
I tried using the ISBLANK statement and can't seem to get this to work properly.
View 14 Replies
View Related
Feb 18, 2010
This code is functional but very redundant and I figured that has to be an easier way to go about this.
View 5 Replies
View Related
Jun 30, 2013
I have a program that relies on a remote drive being open and ready. My current tactic (which works perfectly well) is to have a file in the destination drive and ask the date that file was created. If an error occurs I know the destination drive is not ready.
Code:
On Error Resume Next
temp = FileDateTime(destDrive & "xDeletedStuff" & "testFile.txt")
If Err Then
MsgBox "Destination Drive / Folder Not Ready"
Exit Sub
End If
On Error GoTo 0
Idealy I would rather not use the "testFile" I would prefer to just test to see if the folder existed, "destDrive & "xDeletedStuff"
View 1 Replies
View Related
Jan 29, 2008
I working on a loop that would find blank cell. These cells have a formula in excel, but if certain conditions are met, the cell is left blank. What property in VBA checks for blank cell? I don't thin it's the "empty" property?
View 9 Replies
View Related
