using the slope function with non contiguous cells

[fallowfz]

Is there a way to use non contiguous cells with the slope function?
Specifically, if y's and x's contain non contiguous values.

SLOPE(known_y's,known_x's)

A simple example...

x's y's
1 10
2 12
3 30
4 32
5 60
6 68

Where...
desired x's = 1, 3, 5
desired y's = 10, 30, 60


Thanks,

-Zack

 

[Joel]

use this UDF

=skipslope(A1:A10,B1:B10,3)

where the last number is the step size you want. In you example it will be 2


Function skipslope(ByRef x_values, ByRef y_values, slope_step)
Dim X_range() As Variant
Dim Y_range() As Variant

ReDim X_range(x_values.Count)
ReDim Y_range(y_values.Count)

Index = 0
For i = 1 To x_values.Count Step slope_step
a = x_values(i)

X_range(Index) = x_values(i)
Y_range(Index) = y_values(i)
Index = Index + 1
Next i
skipslope = WorksheetFunction.Slope(X_range, Y_range)


End Function

 

[fallowfz]

use this UDF

=skipslope(A1:A10,B1:B10,3)

where the last number is the step size you want.  In you example it willbe 2

Function skipslope(ByRef x_values, ByRef y_values, slope_step)
Dim X_range() As Variant
Dim Y_range() As Variant

ReDim X_range(x_values.Count)
ReDim Y_range(y_values.Count)

Index = 0
For i = 1 To x_values.Count Step slope_step
a = x_values(i)

   X_range(Index) = x_values(i)
   Y_range(Index) = y_values(i)
   Index = Index + 1
Next i
skipslope = WorksheetFunction.Slope(X_range, Y_range)

End Function







- Show quoted text -

Thanks Joel...this looks like it will work when there is an ordered
spacing between the cells. Any ideas for when the order would be
random?

-Zack

 

[Rick Rothstein \(MVP - VB\)]

What decides that they are the desired x's and y's? That their row numbers
are odd numbers? That the x's are evenly divisible by 10? That you like the
way they looked? What is the rule you are using to decide on their
desirability?

Rick

 

[Jon Peltier]

The ranges must be contiguous.

- Jon

 

[Joel]

The only place I would think that random spacing would be required if there
was a timer stamp on each row where data would be placed in the spreadsheet
everytime the data changed, but you wanted to plot the data at specifc
timestamps.

I would use the tiomestamp to help select the desire rows.

The best way to randomly select data is to add three additional columns to
your data. One is an index which is to number the rows from 1 to last row in
order. The reason for this is to return the data to the original order. the
add random numbers to a second new column. Sort by random number. Then mark
the number of rows you want to sample in the third new column. If you have
100 rows of data and you want to sample 15 random pieces of data then add a 1
to the third new column. then use the index column to return the data to the
original order. then you can modify original UDF code to pass the third new
column to use to select which rows of data to use.

If you need help let me know.

 

[PBezucha]

Yes, you are proposing the simplest way to the selection. If you add an
auxiliary column with any variable determining the validity of each
individual data, you can sort the whole range by these variables, and use the
Slope easily. For each odd row, for example, you can fix the pair 0 and 1 at
the top of the auxiliary, select it, and copy at once down to the all length
of data range. After sorting, the desired data would be accumulated in the
upper part of the treated range. This can be best performed on a copied range
of data to avoid re-sorting. To do the same thing without changing the
original dataset you must have a macro that selects the relevant values,
quite according to another advice in this thread. For many reasons it pays
also here to work with such an auxiliary column. If you have routine need to
do such regressions, I am able to provide a code.