Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 2 of 2

Thread: VBA: Using Linest for a variable range

  1. #1
    Join Date
    28th April 2012

    VBA: Using Linest for a variable range


    I have a large dataset that is split into several columns. However, this is an aggregated dataset for many sub-datasets. It is set up in the following way:

    Site Name x y1 y2 y3
    Site 1 2 45 65 24
    Site 1 4 36 69 45
    Site 1 3 55 89 32
    Site 2 5 34 23 15
    Site 2 9 135 64 76
    Site 2 8 53 234 265
    Site 2 2 37 756 456
    Site 3 5 435 34 567
    Site 3 1 65 24 36

    The sub-datasets being each of the different sites. I am trying to find the slope of the log-log graph for y1,2,3 vs. x for each site. Therefore there will be 3 slopes that I need for each site (sub-set). The lengths of the site subsets are variable (i.e. not all subsets are the same length).

    Being a newbie, I cannot seem to wrap my head around a VBA program(s) that can (1) determine the length of each subset, and (2) find the corresponding log-log slopes for y1,2,3 vs. x for that subset, and (3) write these slopes to a new worksheet.

    Any help would be greatly appreciated.

    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    4th July 2004

    Re: VBA: Using Linest for a variable range

    I'm not a mathematician, but maybe something like this? Assuming that A1:E10 contains the source data, let G2 contain "Site 1", G3 contain "Site 2", G4 contain "Site 3", H1 contain "y1 vs x", I1 contain "y2 vs x", and J1 contain "y3 vs x". Then try...

    H2, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

    Domenic Tamburino
    Microsoft MVP - Excel
    xl-central.com - "For Your Microsoft Excel Solutions"

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Finding variable in range, then copy variable elsewhere
    By dantheinfoman in forum Excel General
    Replies: 3
    Last Post: February 12th, 2012, 09:39
  2. String Variable To Call Defined Range Variable
    By New_to_VBA in forum Excel General
    Replies: 5
    Last Post: November 13th, 2006, 19:50
  3. Using VBA to Determine Data Range for LINEST Formula
    By moneyshot1 in forum Excel General
    Replies: 2
    Last Post: May 11th, 2006, 07:15
  4. LINEST with empty cells
    By novakj in forum Excel General
    Replies: 6
    Last Post: October 21st, 2005, 23:17
  5. LINEST Function
    By mrsm in forum Excel General
    Replies: 2
    Last Post: September 16th, 2004, 02:03


Posting Permissions

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