using the slope function with non contiguous cells
 

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

using the slope function with non contiguous cells
 

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

using the slope function with non contiguous cells
 

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

using the slope function with non contiguous cells
 

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

using the slope function with non contiguous cells
 

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

using the slope function with non contiguous cells
 

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

Question: How to use SLOPE fcn on non contiguous (non adjacent) ce
 

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.
--
Petr Bezucha


"Beth Aultsonian" wrote:

3/3/2010
Several people have asked the question.
I still haven't seen any simple answers simply working on the spreadsheet. One person replied with an algorithm and code to write a macro.

But a macro assumes there is also some given order to the cells being selected.
What if from job to job, the cells must be chosen based on the users criteria which changes from job to job. And there is logic to keep the data in the current order in order to review with others.

Having to reorder the data just to execute a SLOPE fcn and then sort it back again to put it in the logical order is a pain.

From what I gather after searching for 20 minutes on the net, Excel does not have a way to use the SLOPE fcn on non contiguous cells.

I would love to hear otherwise, if I've missed a review on the net that does show a way to accomplish this.

Thanks,
Beth





fallowfz wrote:

using the slope function with non contiguous cells
30-May-08

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

Previous Posts In This Thread:

On Wednesday, May 28, 2008 2:36 PM
Joe wrote:

using the slope function with non contiguous cells
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:

On Wednesday, May 28, 2008 2:53 PM
Rick Rothstein \(MVP - VB\) wrote:

What decides that they are the desired x's and y's?
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
...

On Wednesday, May 28, 2008 4:57 PM
Jon Peltier wrote:

The ranges must be contiguous.
The ranges must be contiguous.

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

On Wednesday, May 28, 2008 6:48 PM
Joe wrote:

The only place I would think that random spacing would be required if there
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 Friday, May 30, 2008 6:13 AM
fallowfz wrote:

using the slope function with non contiguous cells
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 Friday, May 30, 2008 6:13 AM
fallowfz wrote:

using the slope function with non contiguous cells
be 2

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


Submitted via EggHeadCafe - Software Developer Portal of Choice
Creating a WPF Custom Control
http://www.eggheadcafe.com/tutorials...ustom-con.aspx
.