A line graph requires two axes in order to function. On a Mac, Excel may just open to a blank workbook automatically depending on your settings. ' value using a cubic spline interpolation. Doing so will open a new spreadsheet for your data. ' smoothly interpolates resulting output (y) value from a given input (x) ' Given a data set consisting of a list of x and y values, this function Y = a * yin(klo) + b * yin(khi) + ((a ^ 3 - a) * yt(klo) + _ĭydx = (yin(khi) - yin(klo)) / h - ((3 * a ^ 3 - 1) / 6#) * h * yt(klo) + _įunction cubic_spline(xin As Variant, yin As Variant, yt As Variant) As Double Icol = 4 'column for dumping interpolated data intoĭo While ActiveSheet.Cells(irow, icol) "" Yin(c) = Cells(c + FirstRow - 1, 2).Value Xin(c) = Cells(c + FirstRow - 1, 1).Value ' Columns A and B have the data you are fitting ' Find last row of data, helps you figure out how many data points to read. (in words.the data you know is in columns A and B, column C is blank, Column D contains the x values to interpolate to find the y values in Column E, columns F and G yield the dy/dx and the d2y/dx2)ĭim xin() As Double, yin() As Double, cspline As Double, xinterp As Doubleĭim yt() As Double, u() As Double, y As Double, dydx As Double, dydx2 As Doubleĭim c As Long, Ctr As Long, FirstRow As Long, FinalRow As Long, irow As Longĭim n As Long, i As Long, k As Long 'these are loop counting integers (I tried this with y=x*x points, dy/dx and d2y/dx2 looked great away from the end points). To make a long story short, to do the cubic spline you have to more or less arbitrarily assign boundary conditions at the end points, there are at least 3 ways I know to 'guess' those boundary conditions, and these derivatives are very sensitive to those boundary conditions. The chart can be modified with a variety of options that are available with the design menu as shown below. Chart elements like data labels, titles can be added to customize the chart further. They are Chart Elements, Chart styles, and Chart Filters. On the top right corner, there are three options. Warning! Don't trust the derivatives near the end points (that is, first and last points in the Col A). Step 4: Customize the Chart or the Graph. The source data for that series, if it comes from the same worksheet, is highlighted in the worksheet. This formula is only valid in a chart, not in any worksheet cell, but it can be edited just like any other Excel formula. How about something more sophisticated? Here's a cubic spline routine I've adapted to give you first (dy/dx) andĭata in Excel worksheet looks like this. Data in an Excel chart is governed by the SERIES formula.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |