• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Southeast Climate Warming Trend (20 yrs of Data)

C

Clarence Perdue Jr.

Member
I am working on creating a Climate Warming Trend Graph of 20 yrs of Data. I am not sure how to display it visual to see the trends visually. The graph will have an Average Daily Air Temperature on the Y axis and the X will be the days. I have a sample of what scatter graph of how the data would look over a 365 day period. But, need to show this for 365 * 20 = 7300 days. I am not sure this is possible to be display on poster board of 40 inches by 30 inches. I attaching a picture of what single year look like. Do anyone have and ideal of how best to show this trend. Can this be done with Excel? Are there better visual ways to see the trend other than a smooth or scatter graph.
 

Attachments

  • Sandwich_tide_pool_site_Q_temperature_data.xls
    284.5 KB · Views: 11
Hi Clarence ,

First , I do not know why you have selected a X-Y chart , since a simple line chart will also do.

Second , if you wish to show data for all 20 years in a single chart , shouldn't you replace the Julian day axis with the actual date axis ?

Third , you can plot even a hundred years of data on an Excel chart , provided all of the data is in one place ; at present you have separated the data for different years in separate sheets ; can all of the 20 years' data be in one sheet ?

I have added the data for 2006 to the sheet containing the data for 2005 ; the graph now shows data for 2 years. You can extend this to include all 20 years' data.

Narayan
 

Attachments

  • Sandwich_tide_pool_site_Q_temperature_data.xls
    310.5 KB · Views: 8
NARAYANK991 said:
Hi Clarence ,

First , I do not know why you have selected a X-Y chart , since a simple line chart will also do.

Second , if you wish to show data for all 20 years in a single chart , shouldn't you replace the Julian day axis with the actual date axis ?

Third , you can plot even a hundred years of data on an Excel chart , provided all of the data is in one place ; at present you have separated the data for different years in separate sheets ; can all of the 20 years' data be in one sheet ?

I have added the data for 2006 to the sheet containing the data for 2005 ; the graph now shows data for 2 years. You can extend this to include all 20 years' data.

Narayan
Click to expand...
 
I used
NARAYANK991 said:
Hi Clarence ,

First , I do not know why you have selected a X-Y chart , since a simple line chart will also do.

Second , if you wish to show data for all 20 years in a single chart , shouldn't you replace the Julian day axis with the actual date axis ?

Third , you can plot even a hundred years of data on an Excel chart , provided all of the data is in one place ; at present you have separated the data for different years in separate sheets ; can all of the 20 years' data be in one sheet ?

I have added the data for 2006 to the sheet containing the data for 2005 ; the graph now shows data for 2 years. You can extend this to include all 20 years' data.

Narayan
Click to expand...
Hi Narayan,

I was thinking about the simple line chart at first but saw this example online that was using the X-Y Scatter chart.

Secondly, I should have given the data that I am using which has all the 20 years within one sheet. I am not understanding what you are saying about replacing the actual Julian day with the actual date axis? I am upload my actual data. My average data is coming from column G. Column B is the and Column C is the Julian Day. I build column BP with the =TEXT(B2,"00")&TEXT(C2,"000") to try to sort the information.

Thirdly, Will I be able to visual see the Trends.How do I show all that data on graph and not be very distorted. I was looking for what you did with the two years but I am not able to see it in the graph
 

Attachments

  • Sample.xls
    769.5 KB · Views: 3
My previous post I upload and example of how the output would look for one year of daily average reading for 365 days. Also, I have included a sample file of some the years of data. Actually, I needed to do this for 20 years of data. I want to do this to see I can see any climate trends. I am not sure how clear the graph will be visual to see the trends. I initially was looking to do simply line graph instead of the xy scatter. I just saw the previous file as a starting point for me.
 

Attachments

  • 6-17-2014 11-22-21 AM.png
    6-17-2014 11-22-21 AM.png
    121.3 KB · Views: 9
  • Sample.xls
    769.5 KB · Views: 4
Hi Clarence ,

I am not able to understand your problem.

Can you see the chart in this file ?

Narayan
 

Attachments

  • sample.xls
    842 KB · Views: 7
NARAYANK991 said:
Hi Clarence ,

I am not able to understand your problem.

Can you see the chart in this file ?

Narayan
Click to expand...
Hi Narayan,

The sample file is actual showing data correctly on the smooth line when I hover over the line and pick out days. How did the chart determine which days to show on the horizontal axis. I will attempt to do this with 20 years of data. With 20 yrs of data how can I print out the Chart and it be readable.

Secondly, Do you have a suggestion for a good excel book that touch on creating scientific charts. You know today that Dashboard and most of all Visual Presentation is becoming very popular. People want to have charts that they can look at and understand what the results.

You have got me moving the right direction.

Thanks
 
I double the information in the file to get 20 yrs of data to see how the graph would look. I see that the graph begin to squeeze the graph smaller and show less daily averages on the horizontal x-axis of the chart. I am somewhat struggling with how to make visual show certain things on the chart. I am trying to get as much out of the tool without using VBA Macro.

Give me your feedback on what I am doing?

Thank


Clarence Perdue Jr.
 

Attachments

  • sampleoutput16yrs.zip
    111.8 KB · Views: 1
I simply duplicated the data. Here is the duplicated data. I will find some good data tomorrow and play around with the chart. I will look at your link and start working on the Chandoo training.
 

Attachments

  • sample16yrsData-2.zip
    800.8 KB · Views: 3
I am working with 20 years of Climate data from 10 location. I am sure how to visually displace the data. If I at 10 different series . It will get compressed and jumble up. The reading are coming from the same region. Does anyone have a suggestion of how to view the multiple data-set with each of the 10 has over 20 years of data. Here is an example of what it look like.
 

Attachments

  • sampleoutput16yrs.jpg
    sampleoutput16yrs.jpg
    126.5 KB · Views: 6
  • sample16yrsData-2.zip
    800.8 KB · Views: 1
See the attached. I developed this in Excel 2010(compatibility mode), so I'm not sure how it will be in versions of Excel before that.
I converted your year and day numbers to actual Excel dates thsan created a Pivot table and chart. This allows you to filter the dates you see, and to group the dates. If the dates are grouped you get an average value for the grouped dates, if not grouped at all you get the real values back.

You can:
Get all the daily information for all (or some of) the years
Get just the daily June temperatures for all (or some of) the years
Get just the 21st June temperatures for all (or some of) the years
Get monthly average temperatures for all (or some of) the years (by removing the Days part of the grouping)

All the while there is a linear trend line showing, with its equation being just the trend of what's visible on the chart.

OK, I tried to upload the file, but even zipped it was too large so here's a link to it:
https://app.box.com/s/njb0hl37m95vims3suzu

ps. Interesting title to this thread: Southeast Climate Warming trend; seems as if the direction of the trend is a given. I suppose statistics can show what we want them to show.
 
NARAYANK991 said:
Hi Clarence ,

I cannot think of too many ways of resolving your problem :

1. Have 5 separate charts one beneath the other , so that there is perfect alignment between them. Hopefully 5 years of data in one chart will still allow you to see each point properly.

2. Use VBA to scroll and zoom.

See if these links help :

a. http://excelunusual.com/easy-zoom-chart-axis-scaling-using-vba/

b. http://www.databison.com/stock-chart-with-scroll-and-zoom/

Narayan
Click to expand...
Hi Narayan,

It seem that you are only person that is online. I would like to know how I can create the grid line for all 366 day to see each average. If I can make the graph large with all the detail point and send it to a HP Designerjet with print on 3 foot wide paper. Also, Do you think that I can overlay a graph if I can get those detail lines.

Regards




Clarence Perdue Jr.
 
Hi Clarence ,

You can turn on the Minor Gridlines , but I think it will not give you any advantage ; given the size of the paper is 3 ft. , it is about 900 mm. wide ; within this if you need to have 366 gridlines , it will mean the separation between lines is going to be less than 3 mm. I doubt that it is going to be very easy on the eyes.

This is if you have just one year's data on the page ; if you have more , that is going to make it even more difficult.

Can you not reduce the number of samples per year from 365 to some lesser number , by taking an average over every 5 samples ? This can still give you the pattern , but will do it with a lesser number of points.

See the attached file for one such chart.

Narayan
 

Attachments

  • sample.xls
    934.5 KB · Views: 9
NARAYANK991 said:
Hi Clarence ,

You can turn on the Minor Gridlines , but I think it will not give you any advantage ; given the size of the paper is 3 ft. , it is about 900 mm. wide ; within this if you need to have 366 gridlines , it will mean the separation between lines is going to be less than 3 mm. I doubt that it is going to be very easy on the eyes.

This is if you have just one year's data on the page ; if you have more , that is going to make it even more difficult.

Can you not reduce the number of samples per year from 365 to some lesser number , by taking an average over every 5 samples ? This can still give you the pattern , but will do it with a lesser number of points.

See the attached file for one such chart.

Narayan
Click to expand...

I see the quality of the graph is more clean in compared to all the numbers. I am still looking at it to see.
 
NARAYANK991 said:
Hi Clarence ,

You can turn on the Minor Gridlines , but I think it will not give you any advantage ; given the size of the paper is 3 ft. , it is about 900 mm. wide ; within this if you need to have 366 gridlines , it will mean the separation between lines is going to be less than 3 mm. I doubt that it is going to be very easy on the eyes.

This is if you have just one year's data on the page ; if you have more , that is going to make it even more difficult.

Can you not reduce the number of samples per year from 365 to some lesser number , by taking an average over every 5 samples ? This can still give you the pattern , but will do it with a lesser number of points.

See the attached file for one such chart.

Narayan
Click to expand...

Hello Narayan,

I went today to a presentation on Business Intelligent (BI) and the instructor was talking about this tool call Power Query with Excel 2010. He talk about three areas (Discovery, Consume/Analyzing and Visualization) when it comes to handling data . He talk about this tool Power Query that can include within Excel 2010. After going through the Data Discovery, Consume/Analyzing, he show Visualization aspect of the the Power Mapping. It made begin to think about how this data can be visualize. There seem to be allot different ways to Visualize Data that really make sense to the layman. Give me your feedback on this.
 
Hi ,

I have not used BI , but you can find a lot of information on the Internet ; check out these links :

http://exceluser.com/ideas/greatbi.htm


http://www.techradar.com/news/softw...using-excel-for-business-intelligence-1145717

The following link gives a general introduction to the newer tools :

http://www.microsofttrends.com/2014/02/15/powerview-vs-powerpivot-vs-power-bi/

Specifically regarding Power Query , I hope this link will give you the information you need to decide whether to go in for Power Query or not ; it all depends on the kind of data you process or rather the data sources that you handle.

http://blogs.msdn.com/b/ajmee/archi...er-query-when-i-already-have-power-pivot.aspx

Narayan
 
Clarence Perdue Jr. said:
Hello Narayan,

I went today to a presentation on Business Intelligent (BI) and the instructor was talking about this tool call Power Query with Excel 2010. He talk about three areas (Discovery, Consume/Analyzing and Visualization) when it comes to handling data . He talk about this tool Power Query that can include within Excel 2010. After going through the Data Discovery, Consume/Analyzing, he show Visualization aspect of the the Power Mapping. It made begin to think about how this data can be visualize. There seem to be allot different ways to Visualize Data that really make sense to the layman. Give me your feedback on this.
Click to expand...
NARAYANK991 said:
Hi ,

I have not used BI , but you can find a lot of information on the Internet ; check out these links :

http://exceluser.com/ideas/greatbi.htm


http://www.techradar.com/news/softw...using-excel-for-business-intelligence-1145717

The following link gives a general introduction to the newer tools :

http://www.microsofttrends.com/2014/02/15/powerview-vs-powerpivot-vs-power-bi/

Specifically regarding Power Query , I hope this link will give you the information you need to decide whether to go in for Power Query or not ; it all depends on the kind of data you process or rather the data sources that you handle.

http://blogs.msdn.com/b/ajmee/archi...er-query-when-i-already-have-power-pivot.aspx

Narayan
Click to expand...
Hello,

I just got finish looking at a video on power Query dealing with Climate Data.
Showing the 20 Yr Climate trending line graph and Power Query Visualization Map would make for a powerful visual effect. The BI class today got me thinking about using Excel Dashboard or SharePoint Cloud as a reporting vehicle for presentation of these finding.
 
Hello Narayan

I have another question about Crop Growing Days in a year. Crop Growing Days are a number of continuous day when the temperature is above 32 degrees and the last continuous day above 30 degrees. Do you have and ideal of how I could do this with the data. I want to do it without having to write a macro. I see you did something with this thing call and offset for the last chart.

Thanks
 
Hi Clarence ,

Since you have two conditions to check for , with the second one dependent on the first , doing it with one formula might be difficult.

It might be easy if you use one or more helper columns ; if you can upload some data , I can help.

Narayan
 
Tomato seeds germinate readily in constant temperatures of 20 to 26.7 degrees Celsius, emerging in six to 14 days. Although tomatoes will germinate in temperatures as low as 15.5 C, it takes longer. Starting seeds indoors six to eight weeks before the average last spring frost date, or directly sowing them in the garden after the last frost date once soil temperatures reach 15.5 C or warmer will produce seedlings. A temperature range of 18.3 to 23.8 C is best for tomato plant growth. When temperatures drop below this range, growth slows. Tomatoes will grow in temperatures warmer than 23.8 C, but you'll need to water more often to prevent wilting. So, I am looking to see from the data what will be a good start and end date for growing temperature above 15.5 and soil temperature above 15.5 Celsius. The average temperature data is in column 7. There is an average soil temperature in this files which is column 42. What type of graph of the growing season do you think you can get from this data looking at the above scenario for Tomatoes. It is the same data file that you uploaded that had samples.
 

Attachments

  • sample (5).zip
    247.1 KB · Views: 4
Hi Clarence ,

Column 42 is not correct ; can you say which is the correct column number containing data on average soil temperatures ?

Narayan
 
You must log in or register to reply here.
Back
Top