Hi,
I'm no expert with excel and need to convert a lot of data to more managable amounts so I need to convert it to a more manageable set.
With imagination is sort of what my spreadsheet is looking like now:
DateTime NEE Rg0 Rg1 Rg2 Ta1 Ta2 RH WindSpeed Precip NPP Resp GPP
22/10/2004 14:15 -5.01651 NaN NaN NaN NaN NaN NaN 4.44457 NaN -5.01651 NaN NaN
Arrays and pivot tables have me flummoxed so far.
A way to calculate hourly, daily or monthly averages would save my thesis!
Regards
Rlil
Why dont you attach a sample file with some dummy data? It will help us understand the format and suggest solutions much faster.
To Attach a File:
1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Thanks! The dummy spreadsheet is attached (hopefully)
This can be one solution (using 3 helper columns) -
In column N, put this formula - =DATE(YEAR(A2),MONTH(A2),DAY(A2)) - use "Date" as heading in N1
In column O, put this formula - =MONTH(N2) - use "Month" as heading in O1
In column P, put this formula - =HOUR(A2) - use "Hour" as heading in P1
Drag down as required. Then create a pivot for averages using the helper columns.
Ok that's worked so far, thank you. For the pivot how do I go about organising that? Do the helper columns go in the column labels and the rest in the values section?
Rebecca
You could put the helper columns in the column labels or in the page values. That way, you can drag up what you dont need and drag down what you need and the values will populate automatically.
Right I think I'm almost there. I'm not not sure where to put each heading.
I need the summaries for each data type and my excel seems to have had a heart attack as I have nearly 10,000 measurements.
Any chance you could talk me through it?
Thank you!
Rlil
You will need to explain to me which are the fields that contain your data types. I am not able to understand much looking at the sample data you provided since most of it would be dummy data.
Each of the columns represent a value of measurement that was taken every half hour (with some gaps as NaN). I'm hoping to analyse trends in these measurements but as I have 10,000 values of each measurement any analysis on this scale would be unworkable and give me crazy graphs. I need the daily averages for each of the headings NEE Rg0 Rg1 Rg2 Ta1 Ta2 RH WindSpeed and so on.
Thank you so much for helping me!
Rlil
If it helps here is a sample with actual data
Ok well I think I found help elsewhere, but thank you very much for your time.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks