[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel 2007 Better Way To Write Correlation Code

Posted on 2011-05-04
9
Medium Priority
?
185 Views
Last Modified: 2012-05-11
Hello,

Can someone recommend a better way to write the following code that will decrease the amount of time it would take to generate the same resuls?  I've provided a sample set showing 4 strings of code but my actual file contains 17-20 strings.  Just curious if there is a better way of setting this up.  Note that the display/events/screen updates are turned off/on already.

Thanks
Range("CM3").Select
ActiveCell.FormulaR1C1 = _
"=CORREL('FILTERED RAW DATA 1'!R[-1]C[72]:R[4997]C[72],'FILTERED RAW DATA 1'!R[-1]C[75]:R[4997]C[75])
RANGE("CM4").SELECT
ActiveCell.FormulaR1C1 = 
"=CORREL('FILTERED RAW DATA 1'!R[-2]C[72]:R[4996]C[72],'FILTERED RAW DATA 1'!R[-2]C[73]:R[4996]C[73])
RANGE("CM5").SELECT
ActiveCell.FormulaR1C1 = 
"=CORREL('FILTERED RAW DATA 1'!R[-3]C[72]:R[4995]C[72],'FILTERED RAW DATA 1'!R[-3]C[76]:R[4995]C[76])
RANGE("CM6").SELECT
ActiveCell.FormulaR1C1 = 
"=CORREL('FILTERED RAW DATA 1'!R[-4]C[72]:R[4994]C[72],'FILTERED RAW DATA 1'!R[-4]C[87]:R[4994]C[87])

Open in new window

0
Comment
Question by:Escanaba
  • 6
  • 3
9 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35690820
UNTESTED

Please Try this

Range("CM3:CM6").FormulaR1C1 = _
"=CORREL('FILTERED RAW DATA 1'!R[-1]C[72]:R[4997]C[72],'FILTERED RAW DATA 1'!R[-1]C[75]:R[4997]C[75])"

Sid
0
 
LVL 1

Author Comment

by:Escanaba
ID: 35690878
Sid - Thanks for the quick response.  Your option would duplicate the 1st string for the entire range.  Unfortunately that wouldnt work.  Each range is different.  I can see where my code got cut off when I posted the question so maybe this will show everything:
Range("CM3").Select  
ActiveCell.FormulaR1C1 = _  
"=CORREL('FILTERED RAW DATA 1'!R[-1]C[72]:R[4997]C[72],'FILTERED RAW DATA 1'!R[-1]C[75]:R[4997]C[75])  
RANGE("CM4").SELECT  
ActiveCell.FormulaR1C1 =  
"=CORREL('FILTERED RAW DATA 1'!R[-2]C[72]:R[4996]C[72],'FILTERED RAW DATA 1'!R[-2]C[73]:R[4996]C[73])  
RANGE("CM5").SELECT  
ActiveCell.FormulaR1C1 =  
"=CORREL('FILTERED RAW DATA 1'!R[-3]C[72]:R[4995]C[72],'FILTERED RAW DATA 1'!R[-3]C[76]:R[4995]C[76])  
RANGE("CM6").SELECT  
ActiveCell.FormulaR1C1 =  
"=CORREL('FILTERED RAW DATA 1'!R[-4]C[72]:R[4994]C[72],'FILTERED RAW DATA 1'
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35690885
If you can copy the formula from the cell directly and paste it here from Cell CM3 and CM4 then I can give you an exact code.

Sid
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35690894
Seems like we both posted at the same time :)

Let me go through it again :)

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35690917
>>> Note that the display/events/screen updates are turned off/on already.

Also Set the calculation to manual before you run the code and set it to automatic after you have finished. Let me create a sample code for you...

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35690958
Since the range is different every where my suggestion would be to use this.

1) Like I mentioned setting calculation to manual
2) Avoid .SELECT. Directly perform the operation.

Something like

Sub Test()
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
    End With

    Range("CM3").FormulaR1C1 = _
    "=CORREL('FILTERED RAW DATA 1'!R[-1]C[72]:R[4997]C[72],'FILTERED RAW DATA 1'!R[-1]C[75]:R[4997]C[75])"
    
    Range("CM4").FormulaR1C1 = _
    "=CORREL('FILTERED RAW DATA 1'!R[-2]C[72]:R[4996]C[72],'FILTERED RAW DATA 1'!R[-2]C[73]:R[4996]C[73])"
    
    Range("CM5").FormulaR1C1 = _
    "=CORREL('FILTERED RAW DATA 1'!R[-3]C[72]:R[4995]C[72],'FILTERED RAW DATA 1'!R[-3]C[76]:R[4995]C[76])"
    
    Range("CM6").FormulaR1C1 = _
    "=CORREL('FILTERED RAW DATA 1'!R[-4]C[72]:R[4994]C[72],'FILTERED RAW DATA 1'!R[-4]C[87]:R[4994]C[87])"

    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
    End With
End Sub

Open in new window


Sid
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 total points
ID: 35690999
Another interesting way to do it is this

Sub Test()
    Dim i As Long, j As Long
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
    End With
    
    j = 4997
    
    For i = 3 To 6
        Range("CM" & i).FormulaR1C1 = "=CORREL('FILTERED RAW DATA 1'!R[-" & _
                                      i - 2 & _
                                     "]C[72]:R[" & _
                                     j & _
                                     "]C[72],'FILTERED RAW DATA 1'!R[-" & _
                                     i - 2 & _
                                     "]C[75]:R[" & j & "]C[75])"
        
        j = j - 1
    Next i
    
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
    End With
End Sub

Open in new window


Sid
0
 
LVL 1

Author Comment

by:Escanaba
ID: 35691010
Ill test t out and get back with you shortly.  Thanks!
0
 
LVL 1

Author Closing Comment

by:Escanaba
ID: 35709036
Thanks, Sid.  Worked out perfect.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question