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.

 

[RickXL]

Hi MilesS and welcome to the MrExcel Message Board.

Thank you for answering the question - but which question were you answering, please?


Regards,

 

[MilesS]

Sorry was having a really blond moment, post 7 back in 2015. Not logged in I only saw the first page.

Thanks for a really great worksheet. Saved me heaps of time. Worked perfectly "out the box".

I crashed it once. Draw plot, add data, change settings -> crash. Need to add data, redraw plot then change settings.

Suggestions
1:Can we have a setting for label size. I hacked c_TaylorDiagram addpoint .fontsize = iLabelSize
2: Any chance to have a setting for series colour. I've got 55 points so random doesn't make sense.

 

[RickXL]

Sorry was having a really blond moment, post 7 back in 2015. Not logged in I only saw the first page.

No problem. I no longer have blond moments, I have Alzheimer's weeks.

Thanks for a really great worksheet. Saved me heaps of time. Worked perfectly "out the box".

Thanks for the feedback and the appreciation.

I crashed it once. Draw plot, add data, change settings -> crash. Need to add data, redraw plot then change settings.

OK, I will see if I can replicate that. Thanks for pointing it out.

Suggestions
1:Can we have a setting for label size. I hacked c_TaylorDiagram addpoint .fontsize = iLabelSize
2: Any chance to have a setting for series colour. I've got 55 points so random doesn't make sense.

I have created a version that has a total of 7 settings on a UserForm but I don't like it so I never published it. That has different settings for Axis Labels, Marker Labels RMSErr Labels and the Correlation Labels.

I am not sure whether all charts should have the same settings, whether they should be saved between workbooks and how they should be entered. Because I am not a user of the charts I have no way of telling what would be best.

Can you give me some help there? For instance, how would you like to specify the colors?

One idea would be to have another data row, under the existing points, that had the colors set as the background in those cells.

I am coming round to the idea of just adding some more data entry rows for each worksheet that could then be copied and pasted into other worksheets.

All suggestions welcome.


Thanks,

 

[MilesS]

No problem. I no longer have blond moments, I have Alzheimer's weeks.

I think I'm getting that way too. It's this stupid time clock that's ticking away.

I have created a version that has a total of 7 settings on a UserForm but I don't like it so I never published it. That has different settings for Axis Labels, Marker Labels RMSErr Labels and the Correlation Labels.

Most are easy to change if you know Excel. It's the labels that you have to click each one and change that is a problem.
BTW being pedantic the X and Y labels should be different. X is model standard deviation and Y is observed standard deviation

Can you give me some help there? For instance, how would you like to specify the colors?
One idea would be to have another data row, under the existing points, that had the colors set as the background in those cells.

Yes I like that idea, have a comment telling the user to set the background colour and then if it's set use that colour else use automatic shading.
I'm comparing two sets of models (A & B) each (N=10) therefore I have (N+1)xN series. I would like the colour to be set by model B (10 colours)

I am coming round to the idea of just adding some more data entry rows for each worksheet that could then be copied and pasted into other worksheets.

Nope I suggest just adding new sheets. Also the user can save the template as a new name rather than you coding to save the values. Keep it simple, don't program what are a simple Excel functions

 

[MilesS]

Hi Rick,
An chance that you can post the revised version with all the user settings. I'm testing using ver 1_2 right now
Thanks
Miles

 

[EAWT4]

@RickXL thank you so much for sharing your file. I'm wondering if you can help? Your file works great for plotting the standard deviation and correlation, but the root mean square error value is then off.

Here is an example using a model output and observed measurements for minimum temperature in Malawi from 1990-2008:
data from model :
standard deviation:

observed data :

 

[EAWT4]

Apologies, didn't mean to post it so soon. Here is the example:

data from model : 18.407790088925115 18.44762345205904 18.4597030832462 18.360995778757925, 18.515915831120605 18.74921119654731 18.579003351919216 19.10187314465647, 18.939485932181274 18.302944242739716 18.523080265081624, 18.675458496756278 18.548265393909382 18.87877739857164 19.091166795323524, 19.063097365622763 19.021308771221953 19.060954668761667 19.05951169001614
standard deviation: 0.2782772857
RMSE: 0.87789744
Pearson's correlation coefficient: 0.810840658

observed data : 16.674455374979765 16.50238843493613 16.920688508795916 16.83653380063905, 16.80002055931839 17.1536863008199 16.741343068154787 17.011087488810798, 17.13915136650847 16.80960410031736 16.92260392250867 17.156146418231792, 17.007458499264462 17.134257775378767 16.955364014430202, 17.507028801956164 17.203462038159312 16.91186921444716 16.62443612437913
standard deviation: 0.348033406
RMSE: 0
Pearson's correlation coefficient: 1

Here is the link to the file:https://www.dropbox.com/s/mqhukq7l5l8z83c/Taylor Diagram V1_2_test.xlsm?dl=0

When I plot the standard deviation and correlation data into your file (Taylor Diagram V1_2 it plots nicely, but the RMSE for the model is indicated to be about 0.14, not 0.877... any ideas why this might be? Have I done something wrong? or have I misunderstood?

Thanks!

 

[RKJHA2]

Hi,
I am also having some problem finding the relationship between SD and CRMSE. The RMSE value should be low for the model which has low SD, but the plot doesn't show like. Even though SD is low, the RMSE value is high. Could you please see what's the problem with it? Could you sort it out?

Chart Title Min. Temperature
Axis Title Standard Deviation (degree C)
Rays 0.2 0.4 0.6 0.8 0.9 0.95
SD Arcs 1 2 3 4 5 6
Err Arcs +/- 2 4 6 8
SD Max 7
Observed 6.31
Labels Obs bcc cs ip mi
Std Dev 6.31 6.31 6.26 6.35 6.30
Correlation 1 0.92 0.94 0.96 0.93

Since RMSE is automatically plotted based on STD, here is the graph.

https://drive.google.com/file/d/1j3llGLqzQoAiZINcCP2ndW4yrM2WYUir/view?usp=sharing

You can see for Solar Radiation page.

Please let me know with your help.
Thanks,
Ranjeet

 

[RKJHA2]

Hi,
I am also having some problem finding the relationship between SD and CRMSE. The RMSE value should be low for the model which has low SD, but the plot doesn't show like. Even though SD is low, the RMSE value is high. Could you please see what's the problem with it? Could you sort it out?

Chart Title Min. Temperature
Axis Title Standard Deviation (degree C)
Rays 0.2 0.4 0.6 0.8 0.9 0.95
SD Arcs 1 2 3 4 5 6
Err Arcs +/- 2 4 6 8
SD Max 7
Observed 6.31
Labels Obs bcc cs ip mi
Std Dev 6.31 6.31 6.26 6.35 6.30
Correlation 1 0.92 0.94 0.96 0.93

Since RMSE is automatically plotted based on STD, here is the graph.

https://drive.google.com/file/d/1j3l...ew?usp=sharing

You can see for Solar Radiation page.

Please let me know with your help.
Thanks,
Ranjeet

 

[user2453]

Hi, can you help me? From the top, I get that if I click the point I can revise the standard deviation. How do I change the correlation values? I downloaded the .xlam file shown above. It works for me but I don't know how to change the point values.