Results 1 to 6 of 6

Thread: Excel: how to change CORREL ref cells without manual updating?

  1. #1
    unapologetic apologist
    Join Date
    Nov 2005
    Location
    UK
    Posts
    1,954
    Thanks
    363
    Thanked
    275 times in 146 posts

    Question Excel: how to change CORREL ref cells without manual updating?

    what is the quickest way to multiply the rows by coloums. Without manually having to change the formula in each row.

    e.g I first typed the formula =CORREL($B$2:$B$9,B2:B9) in cell C15 then dragged it across.

    Then for cell C16 I had to type the formula =CORREL($C$2:$C$9,B2:B9) then dragged it across.

    Can you please advise of a better and quicker way. Thanks.



    Thanks

    One can never stop saying Thank You

  2. #2
    Seething Cauldron of Hatred TheAnimus's Avatar
    Join Date
    Aug 2005
    Posts
    17,164
    Thanks
    803
    Thanked
    2,152 times in 1,408 posts

    Re: Excel: how to change CORREL ref cells without manual updating?

    Do you mean the OFFSET function?

    Let you take a RANGE, and specify the number of rows/columns to offset?
    throw new ArgumentException (String, String, Exception)

  3. #3
    unapologetic apologist
    Join Date
    Nov 2005
    Location
    UK
    Posts
    1,954
    Thanks
    363
    Thanked
    275 times in 146 posts

    Re: Excel: how to change CORREL ref cells without manual updating?

    nope, tried that already

    you can see the results in the first table under Correlation Analysis, and the formulae used in the table below. Dragging the formula across is no problem, but dragging it down doesn't work, so want a better method than editing each row separately

    One can never stop saying Thank You

  4. #4
    Senior Member
    Join Date
    Oct 2009
    Posts
    269
    Thanks
    45
    Thanked
    30 times in 26 posts
    • cookie365's system
      • Motherboard:
      • Asus H87M Plus
      • CPU:
      • i3 4340
      • Memory:
      • 2x Kingston HyperX 4Gb
      • Storage:
      • 250Gb Samsung SSD 840 EVO + Seagate 1TB + WD Green 2TB
      • Graphics card(s):
      • Whatever comes with the i3
      • PSU:
      • bequiet StraightPower 600
      • Case:
      • Aquacool Dead Silence
      • Operating System:
      • W10
      • Monitor(s):
      • Rectangular
      • Internet:
      • Cable

    Re: Excel: how to change CORREL ref cells without manual updating?

    Have you tried using INDIRECT ?

  5. #5
    SiM
    SiM is offline
    Senior Member
    Join Date
    Apr 2006
    Location
    London
    Posts
    7,787
    Thanks
    300
    Thanked
    633 times in 422 posts
    • SiM's system
      • Motherboard:
      • P5K Premium
      • CPU:
      • Q6600
      • Memory:
      • 8GB PC2-6400 OCZ ReaperX + Platinum
      • Storage:
      • 3 x 320gb HD322HJ single platter in Raid 0
      • Graphics card(s):
      • PNY GTX285
      • PSU:
      • Corsair TX650W
      • Case:
      • Antec 1200
      • Monitor(s):
      • 2407-HC

    Re: Excel: how to change CORREL ref cells without manual updating?

    You want to know how to build a correlation matrix with one function?

    You can do this with offset, I do it all the time. Like this

    =CORREL(OFFSET($B2:$B9,0,match(C$14,$B$1:$E$1,0)-1),OFFSET($B2:$B9,0,match($B15,$B$1:$E$1,0)-1))

    Put that formula in (1,1) of the matrix, then autofill the table. The names above and on the side of the correlation matrix have to exactly match the names of the columns.

    You can use COUNTA instead of MATCH, but I prefer match so you can reorder or leave some out easily if you want to.

    If you know VBA you could always spend 5 minutes writing a VBA function to do it... or if you search google, you probably will find one.
    Last edited by SiM; 06-10-2009 at 08:05 PM.

  6. Received thanks from:

    fuddam (06-10-2009)

  7. #6
    unapologetic apologist
    Join Date
    Nov 2005
    Location
    UK
    Posts
    1,954
    Thanks
    363
    Thanked
    275 times in 146 posts

    Re: Excel: how to change CORREL ref cells without manual updating?

    thanks muchly

    tried it with OFFSET this afternoon before posting but my knowledge was woefully inadequate



    EDIT: I had to edit your ref to absolute: $B2:$B9 to $B$2:$B$9 - now all goodly
    Last edited by fuddam; 06-10-2009 at 11:24 PM. Reason: enlightenment
    One can never stop saying Thank You

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel Sign Change
    By Stu165 in forum Software
    Replies: 4
    Last Post: 23-01-2007, 09:22 PM
  2. A4 TDi v 320d
    By J4MES in forum Automotive
    Replies: 48
    Last Post: 07-01-2007, 12:21 AM
  3. Excel cells
    By joshwa in forum Software
    Replies: 4
    Last Post: 08-02-2005, 04:18 PM

Posting Permissions

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