locked
Logic error when analyzing 3 variables. RRS feed

  • Question

  • OK so I am spinning my wheels on what should be an easy problem but there is a flaw some were in my logic.

    I have three variables to analize, unit, reading and source. The list is in unit, reading, source order.

    Units have a variable number of readings.

    Reading should be at defined intervals ± 1 but two sources are not on the same intervals.

    There are only two sources for the whole list but a unit may only have the first of the two sources.

    I need to find any missing readings and readings out intervals outside the window.

    As I have it set up right now I have 5 columns set up more or less like this.

    Unit reading source source 1 interval source 2 interval
    1 2 1 2 1
    1 3 1 3 4
    1 1 2    
    1 4 2    
    2 2 1    
    2 3 1    
    3 4 1    
    3 4 1    
    3 4 2    
    4 3 1    
    4 1 2    
    4 4 2    

    My problem is as the macro run I either ending up comparing readings to the wrong source and getting stuck in a loop or assigning a reading as outside the window when it should be missing and throwing off the rest of the data set.
    If someone could help me out with the logic it would be great.

    Thursday, September 1, 2011 9:10 PM

All replies

  • I think you need to supply more information in order to resolve this.

    1) What is a unit?

    2) What would an example of a missing reading be? You state that there are a variable number of readings, so how can you tell if a reading is missing or not?

    3) What is Source 1 interval? How is it calculated? What is source 2 interval, how is it calculated?

    4) What are readings out intervals outside the window?

    5) What is the window?

    6) How do you know if a reading is outside the window?

    If you answer these, it shouldn't be hard to help with the logic, but you have not provided enough information yet.


    Jack Dahlgren blogs at:
    Project and Retrovention
    and rarely Twitter
    Saturday, September 3, 2011 12:04 AM
  • I think may have over simplified that data sorry, also I didn’t see there was a cut and paste error. I also switch some numbers around so it a little more clear of an example.

    Tank time of reading source source 1 interval source 2 interval
    1 2 1 2 1
    1 6 1 6 4
    1 1 2    
    1 4 2    
    2 2 1    
    2 6 1    
    3 4 1    
    3 5 1    
    3 4 2    
    4 6 1    
    4 1 2    
    4 4 2    

    Here is the better table.

    1) What is a unit? The unit is a storage tank for this example it has with one or two manual sampling stations.

    2) What would an example of a missing reading be? You state that there are a variable number of readings, so how can you tell if a reading is missing or not?  Each tank need to have a reading taken by a technician at all given time interval from a sampling station (source) and each sampling station has different times they need to be read at. So I know that for this example tank 1 will need to have 4 readings two from each sampling station. If I only have 1 reading from source 1 and 2 from source 2 then I know I am missing one for source 1.

    3) What is Source 1 interval? How is it calculated? What is source 2 interval, how is it calculated? Again I am sorry I think this is confusing because of my bad cut and paste job. Sources 1 is sampling stations 1 and what time points a reading needs to be taken from that station, so they are not calculated, they are set. The number of samples and intervals between the readings are taken are dictated by what is in the tank, but for this macro all the tanks will have the same intervals for each sampling station.

    4) What are readings out intervals outside the window? 5) What is the window? 6) How do you know if a reading is outside the window?

    Reading need to be taken from sampling stations at given time intervals with a window of ± 1 hour. For the example each tank should have two readings from source 1 and if it has a second sampling station two from source 2.  Source 1 needs a reading at 2 and 6 hours. The first reading is acceptable if taken between 1-3 hours and so on. This represents the time the reading was taken at not what the readings are, that is unimportant for this part of my macro. Unfortunately the way the data is stored if a readings is not taken there is not a blank.

    The problem I have been running into is getting the macro to distinguish between a missing reading and a reading out side of the window.

    The way I first had that macro set up was just a quick set IF, ELSEIF, ELSE statements in a do loop.

    Set up like this. This is only a sketch of the code so I know some of these calls won’t work.

        Set s1 = Sheets("sheet1") 'hold sheet 1  "data"
        
        Dim DP As Long 'holds row of datapoint
        
        Dim STP As Long 'holds row for what source time point should be there
        
        Dim SC As Long 'holds source column for what source its looking at
        
        DP = 2
        
        STP = 2
        
        SC = 4
            
        Do
            If s1.Cells(DP, 2) = s1.Cells(STP, SC) Then 'checks time point is equal and start of ifelse #1
            
                DP = DP + 1 'moves to next data point
                    
                STP = STP + 1 'moves to next source timepoint
                                    
            ElseIf s1.Cells(DP, 2) = s1.Cells(STP, SC) - 1 Then 'checks time point is one hour before
            
                DP = DP + 1 'moves to next data point
                    
                STP = STP + 1 'moves to next source timepoint
               
            ElseIf s1.Cells(DP, 2) = s1.Cells(STP, SC) + 1 Then 'checks time point is one hour after
                
                DP = DP + 1 'moves to next data point
                    
                STP = STP + 1 'moves to next source timepoint
                        
            ElseIf s1.Cells(DP, 2) <> s1.Cells(DP - 1, 2) Then 'checks to see if time point is from a diffrent source then the previous line
            
                SC = 5 'moves to next source
            
            ElseIf s1.Cells(DP, 1) <> s1.Cells(datapoint - 1, 1) Then 'checks to see if data point is from a new tank
            
                SC = 4 'resets to first soucrc
                
                STP = 2 'resets to first time point
            
            Else 'else for missing data points
            
                If s1.Cells(DP, 2) = s1.Cells(STP + 1, SC) Then 'start ifelse #2
                
                    STP = STP + 1 'moves to next data point
                    'code to set point as missed
                    
                ElseIf s1.Cells(DP, 2) = s1.Cells(STP + 1, SC) - 1 Then
                
                    STP = STP + 1 'moves to next time point
                    'code to set point as missed
                    
                ElseIf s1.Cells(DP, 2) = s1.Cells(STP + 1, SC) + 1 Then
                
                    STP = STP + 1 'moves to next time point
                    'code to set point as missed
                
                Else
                
                    'code to set point as out of the window
                    
                End If 'end ifelse #2
                    
            End If 'end ofelse #1
            
         
         Loop While Not IsEmpty(s1.Cells(datapoint, 1)) 'end do while loop to run through all data 
    


    From here it just got messy as I tried to add or move code around to get an out for the endless loops or fix miss marked data.

    Right now the data is sorted by unit but I can sort it by source if need be. 

    Is dose this answer all your questions Jack?

    Also thanks for the help.

     

     

     

     

    Sunday, September 4, 2011 6:16 PM