Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / May 2008

Tip: Looking for answers? Try searching our database.

using the slope function with non contiguous cells

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
fallowfz - 28 May 2008 17:51 GMT
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 - 28 May 2008 19:36 GMT
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

> Is there a way to use non contiguous cells with the slope function?
> Specifically, if y's and x's contain non contiguous values.
[quoted text clipped - 18 lines]
>
> -Zack
fallowfz - 28 May 2008 19:51 GMT
> use this UDF
>
[quoted text clipped - 45 lines]
>
> - 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
Joel - 28 May 2008 23:48 GMT
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.

> > use this UDF
> >
[quoted text clipped - 51 lines]
>
> -Zack
Rick Rothstein (MVP - VB) - 28 May 2008 19:53 GMT
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

> Is there a way to use non contiguous cells with the slope function?
> Specifically, if y's and x's contain non contiguous values.
[quoted text clipped - 18 lines]
>
> -Zack
Jon Peltier - 28 May 2008 21:57 GMT
The ranges must be contiguous.

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

> Is there a way to use non contiguous cells with the slope function?
> Specifically, if y's and x's contain non contiguous values.
[quoted text clipped - 18 lines]
>
> -Zack
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.