Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Brighton, Michigan
    Posts
    184
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Hi To All,

    I've been searching for a formula that will check for data that fails a test for example data that is 0.5< and >20.5, such that the data that fails the test or is out of spec is copied, not moved to an adjacent column on the same worksheet. Thanks to the helpful folks here I get the first part, evaluating the data. But I cannot solve the copy to another column half of the formula.

    any suggestions?

    Thank you...

    Marty
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Try this

    =IF(OR(A1<0.5,A1>20.5)=TRUE,A1,"")
    Jerry

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Let's say you have data in column A, starting in cell A2.
    In another column, in Row 2, enter this formula:

    =IF(OR(A2<0.5,A2>20.5),A2,"")

    Fill down as far as needed.
    Does that do what you want?

  4. #4
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Brighton, Michigan
    Posts
    184
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Hi,

    I should have been a little more detailed, I've attached a section of the worksheet problem. From the sample i need to evaluate the data in column "d" ex. d4<0.5,d4>20.5, any data that do not meet this test must be copied to an adjacent column such as column f or g. Column f or g would contain in the (same row number) only the actual data that failed the above test. Hope this is a little clearer.

    Thank you..

    Marty
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I don't see an attachment?

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Reading your text without an attachment try this code

    Select the column/row of data you need changing
    Code:
    Sub MoveMe()
    Dim intCount
    intCount = Selection.Rows.Count
    
    For i = 2 To intCount + 1
    
     	Select Case Cells(i, 4).Value
    
     	Case Is < 0.5
     Cells(i, 6).Value = Cells(i, 4).Value
     	Case Is > 20.5
     	Cells(i, 6).Value = Cells(i, 4).Value
    	
    End Select
    
    Next i
    End Sub
    Jerry

  7. #7
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    If Jerry's code provides the reverse of what you want, try the following
    Code:
    Sub MoveMe()
    Dim intCount
    
        intCount = Selection.Rows.Count
        For i = 2 To intCount + 1
        
            If Cells(i, 4).Value >= 0.5 _
                And Cells(i, 4).Value <= 20.5 _
                Then Cells(i, 6).Value = Cells(i, 4).Value
        Next i
    End Sub
    Regards
    Don

  8. #8
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Brighton, Michigan
    Posts
    184
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Well funny how it doesn't work unless one hits the "upload file" icon...sorry. Here it is.

    Thank you for your help...

    Regards,
    Marty
    Attached Files Attached Files
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

  9. #9
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by mojave1 View Post
    Well funny how it doesn't work unless one hits the "upload file" icon...sorry. Here it is.

    Thank you for your help...

    Regards,
    Marty
    Hi Marty

    =IF(OR(D2<0.5,D2>20.5),K2,"") the formula you have in F2 should have been =IF(OR(D2<0.5,D2>20.5),D2,"") and of course filled down to the other cells in column F.
    Regards
    Don

  10. #10
    New Lounger
    Join Date
    Dec 2009
    Location
    Lexington, South Carolina, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The formula =IF(OR(D3<0.5,D3>20.5),D3,"") will work properly if you put the formula in Column F or column G.

    This will put only the values that are not within the range specified in the column containing the formula.

    Bob
    You can&#39;t get something for nothing
    You can&#39;t have freedom for free
    You won&#39;t get wise with the sleep still in your eyes
    no matter what your dreams might be

  11. #11
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    [attachment=87217:Wopr Sample.xls]
    Try this.
    You'll notice I've entered your minimum/maximum values in the spreadsheet and then referenced the cells containing those values.
    This is often a more useful way to construct these kinds of formulas, since then you can test against new values without having to rebuild your formulas.

    I also added some conditional formatting to highlight the rows that failed the test.
    Attached Files Attached Files
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  12. #12
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Brighton, Michigan
    Posts
    184
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Thank you for your responses, Catharine what does the ),D23,"") portion do? How is the copying of the data to the adjacent column performed, and how would this formula be different if the data where copied to say cloumn M?

    Great suugestion regarding the test criteria in one location ..

    Thanks,
    Marty
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

  13. #13
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    If I break the formula down (and I know that you might be familiar with some of this stuff - but the detail is for the benefit of anyone else following this thread):
    =IF(OR(D2<$I$2,D2>$J$2),D2,"")
    The IF statement has the following syntax IF(some test here, what to do if true, what to do if false)
    You notice the commas - they separate the clauses of the IF statement.

    In this case, if the test value falls outside of the ranges stored in I2 and J2, then display the test value, otherwise do nothing.
    The formula will work just fine if copied to column M - as long as it is referring to the values in Column D (that is what you want to test) and that the Minimum/Maximum values remain in I2 and J2. If you decide to relocate them - then obviously the formula will have to be updated too.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  14. #14
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Brighton, Michigan
    Posts
    184
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Hi Catharine,

    Thanks for the detail.....I got it, and the absolute cell references make it easier to work with.

    Thanks again.

    Regards,
    Marty
    Regards,
    Marty

    "Aerodynamics Is For Those Who Cannot Build Engines" - Enzo Ferrari

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •