Data sorting

**longgamma** · 10-15-2011, 09:51 PM

I want to find the arithmetic average, by month, of a data set where I have all of the data paired with the data in corresponding columns, but they are not grouped by month.

For example:

A = Date (MM/DD/YYYY)
B = Data point (123.45)

An | Bn
A1 | B1
A2 | B2
A3 | B3
etc.

I want to separate it so that I can find each month's Bn average. The data set is large and time prohibitive to do this manually and I am quite new to excel outside of simply entering data. A search of this forum, while helpful for learning Excel, has produced too many nontopical results for this issue.

**MarvinP** · 10-15-2011, 11:02 PM

Hi longgamma and welcome to the forum,

Find the example of what I think you want.

It is done by using a pivot table of your data. You can sum the data by month and count how many data points are in that month and even get the average. See the example.

**longgamma** · 10-16-2011, 01:31 AM

This is exactly what I am trying to do, but I am still having no progress and I am becoming very frustrated.

Let me start over.

The data is as follows:

A = Date (MM/DD/YYYY)
B = Data point

An = One Day
Bn = One data point

An | Bn
A1 | B1
A2 | B2
A3 | B3
etc

I have over 20,000 points to examine by month.

I tried to create a pivot table like the one in the Excel file, but had minimal success. The other problem with using Excel, so far, has been that sorting only allows me to do it by day, when I need to sort it by month.

**MarvinP** · 10-16-2011, 06:59 AM

Hi longgamma,

See if any of these links are helpful for understanding Pivot Tables and grouping by month.
http://chandoo.org/wp/2009/11/17/gro...-pivot-tables/
http://www.contextures.com/xlPivot07.html
http://www.databison.com/index.php/p...nd-date-field/

Pivot Tables are REALLY great and no formulas are needed. It does take some playing around with dragging field names to different boxes to start. Then right clicking on different parts of the table allows more functions to be accessed.

**longgamma** · 10-16-2011, 03:03 PM

Wow, incredibly helpful links. I am very appreciative for your help. I have progressed quite quickly after reading those and watching the videos.

Perhaps now a more advanced question on creating a custom formula within the Pivot Table.

My data is now grouped and sorted as such:

Date_____Data Value_____Day Counts
Year_____Average of Year_____#N/A (Want: # of Days of Data within that year (ie 365)
Month_____Average of Month_____#N/A (Want: # of Days of data within that month (ie 28-31)
Day_____Recorded Value_____Number of this day as numbered sequentially in order throughout month (ie 1-31)

I am trying to find a way to find the difference between [Average of Month] and [Day 1/Count 1] of that month. Adding another column as % difference between those two (and absolute values as well). Would be needed.

I initially created the "Count" under values and made the value field settings as Count-->Running Total in [Date], but this only provided me with data for the days, and not the months or years.

Thank you once again for all of the help thus far. It is greatly appreciated.

**MarvinP** · 10-16-2011, 03:41 PM

I need a sample file showing what you have and what you need to get out of it. To attach a sample, click on Go Advanced and then the Paper Clip Icon above the advanced message area.

**longgamma** · 10-16-2011, 03:59 PM

I have uploaded an example file.

The end goal is to have a pivot table as follows:

Date___Average of Value___Count___Abs. Difference from Month Avg vs Day 1 of Month___% Difference from .....

**longgamma** · 10-23-2011, 01:17 PM

Bumping this up to see if anybody had any ideas of ways to help with this.

One of the end goals here is to get the average of values in a given month and look at the difference between Day1-AvgMonth.