Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: Data Processing Question

  1. #1

    Data Processing Question



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello Everyone,

    I want to process 3 variables to give me 1 output. These 3 variables will be processed several times to generate that particular output.

    Input data x, y , z gets processed by Process 1 and 2 to generate output F. How could I process the x,y,z data without having to reenter each x,y,z data set to calculate F ?

    This is just an example , the actual problem has over 8000 data sets and about 12 elaborate processes and I just dont have the time to enter so many data points. What would be the quickest and most efficient of processing the data without rearranging the actual process setup?

    I have attached the excel file to the thread. Any help would be appreciated.
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	functiontestpic.jpg 
Views:	9 
Size:	92.1 KB 
ID:	3041  
    Attached Files Attached Files

  2. #2
    Code:
    Public Sub GetOutput()Dim lastrow As Long
    Dim i As Long
    
    
        Application.ScreenUpdating = False
        
        With ActiveSheet.Range("G3")
        
            lastrow = .End(xlDown).Row
            For i = 2 To lastrow - 2
            
                ActiveSheet.Range("D3").Value = .Cells(i, 1).Value
                ActiveSheet.Range("D4").Value = .Cells(i, 2).Value
                ActiveSheet.Range("D5").Value = .Cells(i, 3).Value
                .Cells(i, 4).Value = ActiveSheet.Range("D8").Value
            Next i
        End With
        
        Application.ScreenUpdating = True
    End Sub

  3. #3
    Quote Originally Posted by Bob Phillips View Post
    Code:
    Public Sub GetOutput()Dim lastrow As Long
    Dim i As Long
    
    
        Application.ScreenUpdating = False
        
        With ActiveSheet.Range("G3")
        
            lastrow = .End(xlDown).Row
            For i = 2 To lastrow - 2
            
                ActiveSheet.Range("D3").Value = .Cells(i, 1).Value
                ActiveSheet.Range("D4").Value = .Cells(i, 2).Value
                ActiveSheet.Range("D5").Value = .Cells(i, 3).Value
                .Cells(i, 4).Value = ActiveSheet.Range("D8").Value
            Next i
        End With
        
        Application.ScreenUpdating = True
    End Sub
    Thank you very much , this worked like a charm! I tried to adapt it to the actual, more complex problem but i could not make it work. There the output is found through a "goal seek" and another subroutine in this case.

  4. #4
    Hi StoffStoff, if you just wanted a simple formula to put in the Output column you could use:-
    Code:
    =((G4+H4)*2*I4+1)*2
    *Formula relates to J4 of your example sheet.

    Could you list the 12 processes?
    I just combined your 3 sample processes into one, maybe we could do the same with the others.
    Last edited by Beamer; 2015-01-12 at 12:42 PM.

  5. #5

    Data Processing Problem

    I attached the file in question:

    Gh (D67 -> B15 ), Temp (E67-> B17) , Ambient Pressure (F67-> B16) are the inputs and Electric Power (B59-> G67) is the output. The electric Power Output has to be calculated for each of the 8760 (hourly) data sets. The processes are all listed. As you can see , the process is rather involved. I wished i could just drag the data down but what Bob Phillips sent, will most likely be what has to be done here. Any further assistance would be greatly appreciated. Thanks again !
    Attached Files Attached Files
    Last edited by StoffStoff; 2015-01-12 at 01:20 PM.

  6. #6
    Agreed, Bob Phillips is the man
    I've haven't done very much with macros and I don't usually like to modify other peoples macros or formulas and resubmit them, but I wanted to see if I could make this work.
    Really hoping not to offend Bob by jumping on this before he had a chance (obviously he'd have no problem doing this himself and a hell of a lot quicker then it took me).
    But here is my "fix".........hopefully

    Code:
    Public Sub GetOutput()
    Dim lastrow As Long
    Dim i As Long
    
    Application.ScreenUpdating = False
        
        With ActiveSheet.Range("D66")
        
            lastrow = .End(xlDown).Row
            For i = 2 To lastrow - 65
            
                ActiveSheet.Range("B15").Value = .Cells(i, 1).Value
                ActiveSheet.Range("B16").Value = .Cells(i, 3).Value
                ActiveSheet.Range("B17").Value = .Cells(i, 2).Value
                .Cells(i, 4).Value = ActiveSheet.Range("B59").Value
            Next i
        End With
        
        Application.ScreenUpdating = True
    End Sub

  7. #7
    Hello Beamer and hello Bob,


    The script generated an output but the calculation result was not correct. The electrical power output should be fairly proportional to the Gh values. It should more or less represent a bell curve. Day, Month and Hour are not being used by the program anywhere. the Only inputs are Gh, Temp and Ambient Pressure. The issue that most likely that does not make it work properly is the "goal seek" process to obtain the Electrical power output. It is setup so the goal seek has to be used at every step.

  8. #8
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    673
    Articles
    0
    Excel Version
    Excel 2010 64bit
    It is setup so the goal seek has to be used at every step.
    If you were doing this manually, you would enter the required values into B15, B16 and B17 and then what before moving on to the next set of values ?

  9. #9
    Quote Originally Posted by NoS View Post
    If you were doing this manually, you would enter the required values into B15, B16 and B17 and then what before moving on to the next set of values ?
    I would click on calculate (goal seek) to process B15, B16, B17 and get the electric power Pel. I am totally stuck at this point. I could just enter all those values manually and process each set one by one but that would take way too long.

  10. #10
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    673
    Articles
    0
    Excel Version
    Excel 2010 64bit
    I would click on calculate (goal seek) to process B15, B16, B17
    that's the part that's missing, try adding
    Code:
    Call CalculateChimney
    right above
    Code:
    .Cells(i, 4).Value = ActiveSheet.Range("B59").Value

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

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