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

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" wrote:

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

On May 28, 2:36*pm, Joel wrote:
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" wrote:
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- Hide quoted text -

- 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

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.

"fallowfz" wrote:

On May 28, 2:36 pm, Joel wrote:
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" wrote:
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- Hide quoted text -

- 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

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


"fallowfz" wrote in message
...
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

The ranges must be contiguous.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"fallowfz" wrote in message
...
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