Announcement

Collapse
No announcement yet.

VBA: Using Linest for a variable range

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA: Using Linest for a variable range

    Hello,

    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.

  • #2
    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:

    =SLOPE(IF($A$2:$A$10=$G2,C$2:C$10),IF($A$2:$A$10=$G2,$B$2:$B$10))

    Comment

    Working...
    X