There seem to be a lot of tutorials on the web about D3 (www.d3js.org). However I've yet to find one that explains,simply, how to create a line chart. They all seem to be overly complicated, don't explain exactly what is happening, or call functions, classes and variables by the same name.
So here's my attempt. It took me a good few hours to get this working, which is pretty poor given that what I achieved is extremely simple.
Sunday, 22 December 2013
Wednesday, 27 November 2013
Interactive Excel Maps - part 2
Last time I detailed the classes needed for the maps. This time I'll show the code I used to create the maps.
The first thing we need to do is create a new module and set up some global variables. A dictionary object to hold a reference to the map object and ensure that the memory used by it doesn't get collected. A variable to hold the count of maps and a MapEvent object that will switch on and off events when switching sheets and books etc.
The next step is to create the subroutines that will enable and disable the map events. These subroutines are called from the MapEvent class.
Some things need explaining in the above code. Firstly this is all run from an add-in I've called surveyscience.xlam (available here, if you're interested). It contains a sheet called MapSheet that holds details of the maps that I've defined. This is used to update the properties of the map chart. Also, this will only work for embedded charts. Adding code for this should be fairly simple though. I didn't bother as I was using the maps for a dashboard.
The second part creates the chart object and draws the map over the chart. The macro is fairly long unfortunately.
The first thing we need to do is create a new module and set up some global variables. A dictionary object to hold a reference to the map object and ensure that the memory used by it doesn't get collected. A variable to hold the count of maps and a MapEvent object that will switch on and off events when switching sheets and books etc.
Public mcd As New Dictionary Public mcCount As Integer Dim clsMapEvent As New MapEvent
The next step is to create the subroutines that will enable and disable the map events. These subroutines are called from the MapEvent class.
Sub Set_All_Maps()
Dim mc1 As New MapChart
Dim cs As New VBA.Collection
Dim chtNumber As Integer
' Will not work or enable events for active sheet if sheet is a chart sheet'
' Enable events for all charts embedded on a sheet'
' Works for embedded charts on a worksheet or chart sheet'
If ActiveSheet.ChartObjects.Count > 0 Then
Dim chtObj As ChartObject
Dim chtnum As Integer
Dim sp As Shape
chtnum = 1
For Each chtObj In ActiveSheet.ChartObjects
If Left(chtObj.name, 3) = "Map" Then
Set mc1.MapChart = chtObj.Chart
Set mc1.srcDataRange = range(Cells(2, 1), Cells(2, 2)).CurrentRegion
For Each sp In chtObj.Chart.Shapes
cs.Add sp
Next
Set mc1.reg_shapes = cs
If Len(chtObj.name) > 3 Then
chtNumber = CInt(Mid(chtObj.name, 5, Len(chtObj.name) - 4))
mc1.SetScaleLow Workbooks("surveyScience.xlam").Worksheets("MapSheet").Cells(chtNumber, 4).Value, Workbooks("surveyScience.xlam").Worksheets("MapSheet").Cells(chtNumber, 5).Value, Workbooks("surveyScience.xlam").Worksheets("MapSheet").Cells(chtNumber, 6).Value
mc1.SetScaleHigh Workbooks("surveyScience.xlam").Worksheets("MapSheet").Cells(chtNumber, 7).Value, Workbooks("surveyScience.xlam").Worksheets("MapSheet").Cells(chtNumber, 8).Value, Workbooks("surveyScience.xlam").Worksheets("MapSheet").Cells(chtNumber, 9).Value
End If
mcd.Add mcd.Count + 1, mc1
End If
Next
End If
End Sub
Sub Reset_All_Maps()
' Disable events for all charts previously enabled'
Dim chtnum As Integer
On Error Resume Next
For chtnum = 1 To mcd.Count
Set mcd.Item(chtnum).MapChart = Nothing
Next
mcd.RemoveAll
End Sub
Some things need explaining in the above code. Firstly this is all run from an add-in I've called surveyscience.xlam (available here, if you're interested). It contains a sheet called MapSheet that holds details of the maps that I've defined. This is used to update the properties of the map chart. Also, this will only work for embedded charts. Adding code for this should be fairly simple though. I didn't bother as I was using the maps for a dashboard.
The Map Routine
Finally, we need to define the subroutine to create the actual map. This happens in two stages. The first stage loads the map definition to memory, creating the region and region_bit objects along the way. The map created by this macro is based on the details held in the MapSheet worksheet. The variable mapRow passed to the subroutine is used to look up the details from MapSheet.The second part creates the chart object and draws the map over the chart. The macro is fairly long unfortunately.
Private Sub CreateMap(mapRow As Integer)
'Maps'
Dim sdr As range 'Source data range'
Dim mc1 As New MapChart 'New map chart'
Dim co As Shape 'Base for map'
'Maxs and mins of map data'
Dim minX As Double
Dim minY As Double
Dim maxX As Double
Dim maxY As Double
minX = 1E+200
minY = 1E+200
maxX = -1E+200
maxY = -1E+200
'Load file'
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim fileNum As Integer
Dim mapFSO As FileSystemObject
Dim mapFile
Dim LineText As String
Dim arr
Dim header
Dim regName As String
Dim typ As String
Dim mapRegs As New VBA.Collection
Dim temp_reg As Region
Dim temp_bit As Region_Bit
Dim temp_point As MapPoint2D
Dim temp_coord
Dim defStr1 As String
Dim wk As Worksheet
Dim mapRange As range
'Add workbook if none exists'
If Workbooks.Count = 0 Then
Workbooks.Add
End If
'Add worksheet'
Set wk = Worksheets.Add
'Get path of map'
Set mapRange = Workbooks("surveyScience.xlam").Worksheets("MapSheet").Cells(1, 1).CurrentRegion
Set mapRange = range(mapRange(mapRow, 1), mapRange(mapRow, 9))
defStr1 = Workbooks("surveyScience.xlam").Worksheets("MapSheet").Cells(mapRow, 2).Text
Set mapFSO = CreateObject("Scripting.FileSystemObject")
Set mapFile = mapFSO.OpenTextFile(defStr1, 1, False, -1)
'Extract data from file'
Do While mapFile.AtEndOfStream <> True
LineText = mapFile.ReadLine
arr = Split(LineText, ";")
header = Split(arr(0), ",")
regName = header(5)
wk.Cells(mapRegs.Count + 2, 1) = regName
Set temp_reg = New Region
temp_reg.setName (regName)
temp_reg.setColour CInt(header(1)), CInt(header(2)), CInt(header(3))
If header(0) = "f" Then
temp_reg.setFill (True)
Else
temp_reg.setFill (False)
End If
arr = Split(arr(1), " ")
For i = 0 To UBound(arr)
If arr(i) = "M" Then
Set temp_bit = New Region_Bit
typ = "M"
ElseIf arr(i) = "L" Then
typ = "L"
ElseIf arr(i) = "z" Then
typ = "z"
temp_reg.addBit temp_bit
Set temp_bit = Nothing
Else
'Split point into x,y'
temp_coord = Split(arr(i), ",")
Set temp_point = New MapPoint2D
temp_point.SetX (temp_coord(0))
temp_point.SetY (temp_coord(1))
If typ = "M" Then
temp_bit.setStartPoint temp_point
ElseIf typ = "L" Then
temp_bit.addPoint temp_point
End If
'Set mins and maxs'
If temp_coord(0) > maxX Then maxX = temp_coord(0)
If temp_coord(1) > maxY Then maxY = temp_coord(1)
If temp_coord(0) < minX Then minX = temp_coord(0)
If temp_coord(1) < minY Then minY = temp_coord(1)
Set temp_point = Nothing
End If
Next
mapRegs.Add temp_reg
Set temp_reg = Nothing
Loop
mapFile.Close
'Create chart'
Set co = wk.Shapes.AddChart
co.Select
'Increase size of chart by 20%'
co.width = co.width * 1.2
co.Height = co.Height * 1.2
'Change name so that we can identify chart as a map'
ActiveChart.Parent.name = "Map_" & mapRow
'Assign active charts as map chart'
Set mc1.MapChart = ActiveChart
Set sdr = wk.range(Cells(2, 1), Cells(mapRegs.Count + 1, 2))
Set mc1.srcDataRange = sdr
Call mc1.SetScaleLow(mapRange.Cells(1, 4).Value, mapRange.Cells(1, 5).Value, mapRange.Cells(1, 6).Value)
Call mc1.SetScaleHigh(mapRange.Cells(1, 7).Value, mapRange.Cells(1, 8).Value, mapRange.Cells(1, 9).Value)
'Set properties of chart'
ActiveChart.SetSourceData Source:=sdr
ActiveChart.ChartType = xlColumnClustered
ActiveChart.PlotBy = xlColumns
ActiveChart.Legend.Delete
ActiveChart.HasTitle = False
ActiveChart.Axes(xlValue).Delete
ActiveChart.Axes(xlCategory).Delete
ActiveChart.Axes(xlValue).MajorGridlines.Delete
'Maxs and mins of plot area'
Dim x1 As Double
Dim y1 As Double
Dim x2 As Double
Dim y2 As Double
x1 = ActiveChart.PlotArea.Left
y1 = ActiveChart.PlotArea.Top
x2 = ActiveChart.PlotArea.Left + ActiveChart.PlotArea.width
y2 = ActiveChart.PlotArea.Top + ActiveChart.PlotArea.Height
'Background'
'Chart Area background'
With ActiveChart.Shapes.BuildFreeform(msoEditingAuto, -6, -6)
.AddNodes msoSegmentLine, msoEditingAuto, -6, ActiveChart.ChartArea.Height
.AddNodes msoSegmentLine, msoEditingAuto, ActiveChart.ChartArea.width, ActiveChart.ChartArea.Height
.AddNodes msoSegmentLine, msoEditingAuto, ActiveChart.ChartArea.width, -6
.AddNodes msoSegmentLine, msoEditingAuto, -6, -6
.ConvertToShape.name = "Background"
End With
'Calculate scaling factors'
Dim mx As Double
Dim cx As Double
Dim my As Double
Dim cy As Double
Dim dA As Double
Dim rA As Double
Dim ex As Double
mx = (x2 - x1) / (maxX - minX)
my = (y2 - y1) / (maxY - minY)
cx = x1 - mx * minX
cy = y1 - my * minY
'Keep aspect the same'
dA = Abs((y2 - y1) / (x2 - x1))
rA = Abs((maxY - minY) / (maxX - minX))
If (dA > rA) Then
ex = (maxY - minY) * (dA / rA - 1)
mx = (x2 - x1) / (maxX - minX)
my = (y2 - y1) / ((maxY + ex / 2) - (minY - ex / 2))
cx = x1 - mx * minX
cy = y1 - my * (minY - ex / 2)
ElseIf (dA < rA) Then
ex = (maxX - minX) * (rA / dA - 1)
mx = (x2 - x1) / ((maxX + ex / 2) - (minX - ex / 2))
my = (y2 - y1) / (maxY - minY)
cx = x1 - mx * (minX - ex / 2)
cy = y1 - my * minY
Else
mx = 1
my = 1
cx = 0
cy = 0
End If
'Draw parts of map'
Dim reg_shape
Dim t As Shape
Dim rs As New VBA.Collection
'Cycle through regions'
For i = 1 To mapRegs.Count
'Cycle through bits'
For j = 1 To mapRegs.Item(i).getNumBits
Set temp_bit = mapRegs.Item(i).getBit(j)
'Create new shape'
Set reg_shape = ActiveChart.Shapes.BuildFreeform(msoEditingAuto, temp_bit.getStartPoint().GetX() * mx + cx, temp_bit.getStartPoint().GetY() * my + cy)
'Cycle through points'
For k = 1 To mapRegs.Item(i).getBit(j).getNumPoints
'Add point to shape'
reg_shape.AddNodes msoSegmentLine, msoEditingAuto, temp_bit.getPoint(k).GetX() * mx + cx, temp_bit.getPoint(k).GetY() * my + cy
Next
'Convert to shape'
If mapRegs.Item(i).getFill() = True Then
Set t = reg_shape.ConvertToShape
With t
.name = mapRegs.Item(i).getName
.fill.ForeColor.RGB = mapRegs.Item(i).getColour2()
.Line.ForeColor.RGB = RGB(0, 0, 0)
.Line.Weight = 0.25
End With
rs.Add t
End If
Set reg_shape = Nothing
Next
Next
Set mc1.reg_shapes = rs
mcCount = mcCount + 1
mcd.Add mcCount, mc1
End Sub
Conclusion
And there it is, a way to create reasonable and extensible maps in Excel.
Presumably you could combine the two parts of the macro and create the shapes directly from the file. It might be easier. However, splitting the code makes it easier to change the subroutine to load in different map formats.
One limitation that I've found in Excel 2013 is that the shapes seem to default to a mitre join. This makes the maps look horrendous. There is no way to programmatically change the joins to round. You have to manually create a shape, change the join type, set the shape as the default type and then run the macro. Odd.
As usual, comments are welcome.
Tuesday, 26 November 2013
Interactive Excel Maps - part 1
Introduction
I've recently been trying to get some interactive maps working in Excel for a dashboard we were using at work.
What follows is the first part of an explanation of how I did it and an example of what it looks like.
Of course I could have created the maps much more simply in R or Quantum GIS but that wouldn't have helped for the dashboard.
Thursday, 21 November 2013
Market Research and Statistics Excel add-in Part 2
I've removed the password protection from my add-in and it's now on version 0.6.
It's in the same place on www.surveyscience.co.uk
It's in the same place on www.surveyscience.co.uk
Wednesday, 23 October 2013
Excel Box Plot Macro
Introduction
A boxplot is a way of plotting the median of a set of data points alongside the quartile ranges and any outliers. Basically it's a good way of showing the range and distribution of a univariate data set.
The boxplot consists of a line showing the median, a box encompassing the first and third quartiles, and whiskers showing lines at 1.5 times the inter-quartile range above the third and below the first quartile. The inter-quartile range is just the third quartile minus the first quartile.
The image below is an example of what the macro produces:
The Macro
The macro takes the raw data, a univariate set of points, calculates the quartiles and plots them on an open-high-low-close stock chart. Lines are added for the median and the inter-quartile boundaries. The outliers are added as a XY scatter plot series.
A form is used to get the range holding the data but you can adjust the macro to used any old range. The form also has a box to select whether you want the outliers plotted. Again, you can change the macro for this.
There are two parts to the macro. The first takes the data, calculates the various values and plots them. The second adjusts the chart to update it when it is resized or recalculated.
Here's the first part:
-------------------------------------------------------------------------------
'Define global variables
Dim bpd As New Dictionary
Dim boxPlotCount As Integer
Sub Boxplot()
Dim q1 As Double
Dim q2 As Double
Dim q3 As Double
Dim iqr As Double
Dim Data As range
Dim counter As Long
Dim i As Long
Dim outliers As range
Dim outliersArray() As Integer
Dim a1 As Shape
Dim a2 As Shape
Dim a3 As Shape
Dim m As Double
Dim c As Double
Dim bpt As New BoxPlotChart
'Show form to get location of data
BoxPlotForm.Show
If BoxPlotForm.cancelB = True Then
Unload BoxPlotForm
Exit Sub
End If
Set Data = range(BoxPlotForm.RefEdit1.Text)
'Calculate median, first quartile, third quartile and inter-quartile range
q1 = WorksheetFunction.Quartile_Inc(Data, 1)
q2 = WorksheetFunction.Quartile_Inc(Data, 2)
q3 = WorksheetFunction.Quartile_Inc(Data, 3)
iqr = 1.5 * (q3 - q1)
'Create new sheet
Worksheets.Add
Cells(1, 2).Value = "Data"
Cells(1, 3).Value = "Chart Data"
Cells(2, 1).Value = "Inter quartile range"
Cells(3, 1).Value = "Quartile 1"
Cells(4, 1).Value = "Median"
Cells(5, 1).Value = "Quartile 3"
Cells(6, 1).Value = "Inter quartile range"
Cells(2, 2).Value = iqr
Cells(3, 2).Value = q1
Cells(4, 2).Value = q2
Cells(5, 2).Value = q3
Cells(6, 2).Value = iqr
'Filter list of points to those outside IQR
Cells(1, 5).Value = "Extremes"
counter = 2
For i = 1 To Data.Rows.Count
If Data(i, 1).Value > (q3 + iqr) Or Data(i, 1).Value < (q1 - iqr) Then
Cells(counter, 5).Value = Data(i, 1).Value
counter = counter + 1
End If
Next
Set outliers = range(Cells(2, 5), Cells(counter - 1, 5))
ReDim outliersArray(counter - 2)
For i = 0 To counter - 2
outliersArray(i) = 1
Next
Cells(2, 3).Value = iqr
Cells(3, 3).Value = q1
Cells(4, 3).Value = q3 + iqr
Cells(5, 3).Value = q1 - iqr
Cells(6, 3).Value = q3
For i = 9 To 12
Cells(3, i).Value = q1
Cells(4, i).Value = q3 + iqr
Cells(5, i).Value = q1 - iqr
Cells(6, i).Value = q3
Next
'Plot a ohlc chart chart
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=range(Cells(3, 9), Cells(6, 12))
ActiveChart.ChartType = xlStockOHLC
ActiveChart.PlotBy = xlRows
ActiveChart.SetSourceData Source:=range(Cells(3, 3), Cells(6, 3))
Set bpt.BoxChart = ActiveChart
'Delete temporary data
range(Cells(3, 9), Cells(6, 12)).ClearContents
'Plot outliers
If BoxPlotForm.PlotCheckBox.Value = True And counter > 2 Then
'Plot these as a scatterplot with x of 1
For i = 1 To counter - 2
With ActiveChart.SeriesCollection.NewSeries
.ChartType = xlXYScatter
.Values = outliers(i, 1)
.Name = "Outliers" & i
.XValues = outliersArray(i - 1)
.MarkerStyle = -4168
.MarkerSize = 3
.MarkerForegroundColor = RGB(0, 0, 0)
End With
Next
'Need to make sure that both axes line up
ActiveChart.Axes(xlValue, xlSecondary).MinimumScale = ActiveChart.Axes(xlValue).MinimumScale
ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = ActiveChart.Axes(xlValue).MaximumScale
ActiveChart.Axes(xlValue).MinimumScale = ActiveChart.Axes(xlValue, xlSecondary).MinimumScale
ActiveChart.Axes(xlValue).MaximumScale = ActiveChart.Axes(xlValue, xlSecondary).MaximumScale
End If
'Get rid of horizontal grid lines, x axis labels and ticks
With ActiveChart.Axes(xlCategory)
.MajorTickMark = xlNone
.TickLabelPosition = xlNone
End With
ActiveChart.Legend.Delete
ActiveChart.Axes(xlValue).MajorGridlines.Delete
'Plot line on chart
m = (ActiveChart.PlotArea.InsideTop - (ActiveChart.PlotArea.InsideTop + ActiveChart.PlotArea.InsideHeight)) / (ActiveChart.Axes(xlValue).MaximumScale - ActiveChart.Axes(xlValue).MinimumScale)
c = ActiveChart.PlotArea.InsideTop - m * ActiveChart.Axes(xlValue).MaximumScale
Set bpt.s1 = ActiveChart.Shapes.AddLine((ActiveChart.PlotArea.InsideLeft + 0.3 * ActiveChart.PlotArea.InsideWidth), m * q2 + c, (ActiveChart.PlotArea.InsideLeft + 0.7 * ActiveChart.PlotArea.InsideWidth), m * q2 + c)
bpt.s1.Line.ForeColor.ObjectThemeColor = msoThemeColorAccent2
'Plot lines at high and low markers
Set bpt.s2 = ActiveChart.Shapes.AddLine((ActiveChart.PlotArea.InsideLeft + 0.33 * ActiveChart.PlotArea.InsideWidth), m * (q3 + iqr) + c, (ActiveChart.PlotArea.InsideLeft + 0.66 * ActiveChart.PlotArea.InsideWidth), m * (q3 + iqr) + c)
bpt.s2.Line.ForeColor.ObjectThemeColor = msoThemeColorText1
Set bpt.s3 = ActiveChart.Shapes.AddLine((ActiveChart.PlotArea.InsideLeft + 0.33 * ActiveChart.PlotArea.InsideWidth), m * (q1 - iqr) + c, (ActiveChart.PlotArea.InsideLeft + 0.66 * ActiveChart.PlotArea.InsideWidth), m * (q1 - iqr) + c)
bpt.s3.Line.ForeColor.ObjectThemeColor = msoThemeColorText1
bpt.tq = q3 + iqr
bpt.lq = q1 - iqr
bpt.q2 = q2
boxPlotCount = boxPlotCount + 1
'Add chart to dictionary
bpd.Add boxPlotCount, bpt
End Sub
-------------------------------------------------------------------------------
And here is the boxplot object that replots the lines when it is adjusted. You'll need to create a class module called 'BoxPlotChart' to hold the code:
-------------------------------------------------------------------------------
Option Explicit
Public WithEvents BoxChart As Chart
Public posY As Integer
Public s1 As Shape
Public s2 As Shape
Public s3 As Shape
Public tq As Double
Public lq As Double
Public q2 As Double
Private Sub BoxChart_Calculate()
'Redraw the median line
'Need to know the position of the top and bottom in both
'screen coordinates and scale
Dim m As Double
Dim c As Double
m = (ActiveChart.PlotArea.InsideTop - (ActiveChart.PlotArea.InsideTop + ActiveChart.PlotArea.InsideHeight)) / (ActiveChart.Axes(xlValue).MaximumScale - ActiveChart.Axes(xlValue).MinimumScale)
c = ActiveChart.PlotArea.InsideTop - m * ActiveChart.Axes(xlValue).MaximumScale
If Not (s1 Is Nothing) Then
s1.Left = (ActiveChart.PlotArea.InsideLeft + 0.3 * ActiveChart.PlotArea.InsideWidth)
s1.Top = m * q2 + c
s1.width = (0.4 * ActiveChart.PlotArea.InsideWidth)
End If
If Not (s2 Is Nothing) Then
s2.Left = (ActiveChart.PlotArea.InsideLeft + 0.33 * ActiveChart.PlotArea.InsideWidth)
s2.Top = m * tq + c
s2.width = (0.33 * ActiveChart.PlotArea.InsideWidth)
End If
If Not (s3 Is Nothing) Then
s3.Left = (ActiveChart.PlotArea.InsideLeft + 0.33 * ActiveChart.PlotArea.InsideWidth)
s3.Top = m * lq + c
s3.width = (0.33 * ActiveChart.PlotArea.InsideWidth)
End If
End Sub
Private Sub BoxChart_Resize()
'Redraw the median line
'Need to know the position of the top and bottom in both
'screen coordinates and scale
Dim m As Double
Dim c As Double
m = (ActiveChart.PlotArea.InsideTop - (ActiveChart.PlotArea.InsideTop + ActiveChart.PlotArea.InsideHeight)) / (ActiveChart.Axes(xlValue).MaximumScale - ActiveChart.Axes(xlValue).MinimumScale)
c = ActiveChart.PlotArea.InsideTop - m * ActiveChart.Axes(xlValue).MaximumScale
s1.Left = (ActiveChart.PlotArea.InsideLeft + 0.3 * ActiveChart.PlotArea.InsideWidth)
s1.Top = m * q2 + c
s1.width = (0.4 * ActiveChart.PlotArea.InsideWidth)
s2.Left = (ActiveChart.PlotArea.InsideLeft + 0.33 * ActiveChart.PlotArea.InsideWidth)
s2.Top = m * tq + c
s2.width = (0.33 * ActiveChart.PlotArea.InsideWidth)
s3.Left = (ActiveChart.PlotArea.InsideLeft + 0.33 * ActiveChart.PlotArea.InsideWidth)
s3.Top = m * lq + c
s3.width = (0.33 * ActiveChart.PlotArea.InsideWidth)
End Sub
-------------------------------------------------------------------------------
Limitations
Although the macro works fine when you run it, if you save the workbook, close it and then reopen it, the charts are just charts. They no longer resize properly.
Presumably you'd need to cycle through the charts in the workbook and re-assign any that were boxplots to BoxPlotCharts.
Limitation number two is that it only creates a boxplot for one set of data. It would be fairly trivial to extend this to more than one set of data.
Limitation number three is that the function used to calculate the quartiles only appeared in Excel 2010. I believe that there is a 'quartile' function in previous versions though so it can be changed for this.
Conclusion
Hopefully this macro is useful. As ever, if you have any comments, improvements or find any bugs, etc. then please comment.
Saturday, 19 October 2013
Spline Interpolation Excel Macro
Introduction
This is my attempt at producing an Excel function to interpolate data using splines. A good introduction to spline interpolation is, once again, given by wikipedia.
The algorithm is taken from the book 'Numerical Analysis, 7th Edition, Burden and Faires'. The macro seems to give the same answers as the example in the book so I'm fairly confident that it mostly works.
The macro takes:
- The x coordinates of a set of points
- The y coordinates of a set of points
- The x coordinates of the points that you want to interpolate
- An optional parameter of the gradient at point zero
- An optional parameter of the gradient at point n
If the optional parameters are missing then free interpolation is performed, otherwise a clamped interpolation is done. The macro outputs the interpolated Y coordinates.
Monday, 30 September 2013
Market Research and Statistics Excel add-in
Just a quick post to say that I've collected the various macros detailed on this blog, with others, in one Excel add in.
I've put this add in on to:
I've put this add in on to:
Hopefully people will find it useful. One word of caution - it's about version 0.1 at the moment. You'll need to be wary of bugs etc.
But please give it a go and tell me what you think.
Subscribe to:
Posts (Atom)