Creating Taylor Diagrams in Excel

[davidhall]

I'm trying to create a Taylor Diagram using excel and I'm looking to see if someone knows where I can obtain a template and/or instructions on how to do it.

Any help is much appreciated.

 

[davidhall]

Hi Rick,

I've followed your instructions and added the Process Form to a new group. After clicking on the icon, I get a "run time error 438 - object doesn't support this property or method". When I click the Debug option, references the a_ProcessForm module/ Sub ProcessForm and the line that says .drwChart. See below.


Sub ProcessForm()


Dim r As Range
Dim TD1 As c_TaylorDiagram
Dim arr As Variant
'On Error GoTo Cancel
'Set r = Application.InputBox(Prompt:="Select ""Chart Title"" at the top left of Data Table", Title:="Chart Parameter Entry", Type:=8)
'On Error GoTo 0
Set r = Range("L1")
If Not r Is Nothing Then
r.Resize(10) = Application.Transpose(Array("Chart Title", "Axis Title", "Rays", "Arc1", "Arc2", "Std Max", "StdRef", "Labels", "StdDev", "Correlation"))
Set TD1 = New c_TaylorDiagram
With TD1
.ChartTitle = r.Cells(1, 2).Value
.AxisTitle = r.Cells(2, 2).Value
.Rays = Range(r.Cells(3, 2), r.Cells(3, 2).End(xlToRight)).Value
.Arc1 = Range(r.Cells(4, 2), r.Cells(4, 2).End(xlToRight)).Value
.Arc2 = Range(r.Cells(5, 2), r.Cells(5, 2).End(xlToRight)).Value
.StdMax = r.Cells(6, 2).Value
.StdRef = r.Cells(7, 2).Value
.Labels = Range(r.Cells(8, 2), r.Cells(8, 2).End(xlToRight)).Value
.stddev = Range(r.Cells(9, 2), r.Cells(9, 2).End(xlToRight)).Value
.correlation = Range(r.Cells(10, 2), r.Cells(10, 2).End(xlToRight)).Value
.drwChart
End With
End If
Cancel:
End Sub


Do you know how to fix this error?

 

[RickXL]

Hi,

Unfortunately, the error message tells you only that there is a fault somewhere inside that method. You can single-step it through until it fails then it returns to that point.

However, there is one bug that I know about. If you have only one item selected in one of the lists then it crashes. I have placed a revised version at the same Dropbox link as before.

Annoyingly, I can't seem to make the macro work from that button. It hardcodes the file path as well as the macro name. So every time it changes folders it breaks the link and it won't work. I can't edit it in situ in Dropbox either. I think I should be able to create a custom group that may work better.

If you have any comments about how you would like it to work please let me know and I will try to incorporate the changes.

 

[davidhall]

Figured out one of the issues with the code.

Within the class module c_TaylorDiagram/ Publice Sub drwChart(), the following line needs to be corrected to show this:

Set newChart = ThisWorkbook.ActiveSheet.Shapes.AddChart(xlXYScatterLinesNoMarkers, Left:=0, Top:=0, Width:=500, Height:=500)


There still needs to be another arc created to show the "observed" value.

Also, the standard deviation match up with the horizontal axis but don't match with the vertical axis. When looking at any single point, it should represent the same number when looking at both axis.

 

[RickXL]

the following line needs to be corrected to show this:

Set newChart = ThisWorkbook.ActiveSheet.Shapes.AddChart(xlXYScatterLinesNoMarkers, Left:=0, Top:=0, Width:=500, Height:=500)

Sorry, the line needs to be corrected to show what?

There still needs to be another arc created to show the "observed" value.

Sorry, I am not following. Which one of the two arc types would you like to see and where would you like to see it?

Also, the standard deviation match up with the horizontal axis but don't match with the vertical axis. When looking at any single point, it should represent the same number when looking at both axis.

If you plot the same SD on both axes doesn't that put all the points on a straight line at 45°? What have I missed?

 

[RickXL]

OK, You were showing me your suggested line - got it!

The original works OK for me but it was introduced in Excel 2013 so I guess you are using an earlier version.
No problem. I can make that change.

 

[RickXL]

Hi,

I think I have your third point clear in my mind now.

You are confirming that the plot is of CC (correlation coefficient) v SD (standard deviation).
The CC coordinate is along the relevant ray - as now.
The SD coordinate is round the SD contour lines and not against the x-axis as they are now.
So the data points should be plotted at the point where the CC ray crosses the SD contour.

OK, no problem, I can make that change.

 

[RickXL]

Hi,

I may have found your second point now by reading http://www-pcmdi.llnl.gov/about/staff/Taylor/CV/Taylor_diagram_primer.pdf

You don't like to make things too easy do you?

The document above shows a dashed arc that starts from a point marked "observed". I presume that is what you would like.
Am I correct to assume that the point I called "StdRef" in my form would be better labeled "Observed"?

 

[RickXL]

Hi,

Latest updates now available in DropBox.

 

[biknpl]

RickXL hi
i wan to plot three variables StDEV,Correlation,and RMSE.how to do it

 

[RickXL]

I don't know - you tell me.

Can you post a link to a Taylor Diagram that shows RMSE? If so I will take a look and see what I can do.