Excel 2007 : Averaging across rows based on some criteria

**cde1983** · 05-13-2010, 01:55 PM

I have an excel file with over 500,000 rows, each representing monthly temperature data for a particular weather station and year. I would like to find the average temperature and accompanying standard deviation across all available years for each individual weather station and each month. However, I only need to do this for a small portion of the weather stations. In the attached shortened example of my data the second sheet lists the weather stations for which I need averages and standard deviations.

Short of manually selecting all the rows I need for each location, is there a macro or function that can average across all appropriate rows in the first sheet for each row in the second sheet?

**teylyn** · 05-13-2010, 07:47 PM

Can you explain the data structure a bit more? There seem to be an awful lot of duplicates.

Can you provide a mocked up example of a few results?

On Sheet1, you have a code for Country. On Sheet2 you have country names. Several countries seem to have the same Temp station number (Belgium, Netherlands).
When there are several rows for each country, with the same temp station, which data should be used for the average?

It can be done with formulas, but there needs to be a logic for the formula to find the data in the first place.

**cde1983** · 05-14-2010, 10:50 AM

I have updated the excel file with a bit more information to help aid in answering some of your questions. There are duplicates on Sheet 2 because this sheet represents locations of populations (both male and female), many of which are located close to the same weather station. The country column in Sheet 2 refers not to the country where the temp station is located but the country where the population is located. It can be ignored; I was simply using it to orient myself.

Countries like Belgium and the Netherlands have the same temperature station because there are no stations in Belgium, and the station in the Netherlands is the closest weather station to the Belgian population.

Therefore, all that is needed is matching between weather station numbers in Sheet 2 and Sheet 1. The weather station numbers are unique (irrespective of country code), so country should not need to be factored into the formula.

I appreciate you taking a look at the data! I provided some example formulas that I would have to do by hand. Hopefully, this is a little more straight forward...

**teylyn** · 05-15-2010, 06:39 AM

Well,

one problem is that your temp station codes in the two sheets are not the same.

If they were, you could do a simple AVERAGEIF() like

=AVERAGEIF(Sheet1!C:C,Sheet2!E10,Sheet1!E:E)

But in Sheet1, the temp station is text, with a leading zero, for example

028970000

In Sheet2, though, the same temp station is a number, with no leading zero and only three zeros at the end, like

2897000

Averageif can't manage that. A workaround could be:

=SUMPRODUCT(--(Sheet1!C:C=TEXT(Sheet2!E2,"00000000")&"0"),Sheet1!E:E)/SUMPRODUCT(--(Sheet1!C:C=TEXT(Sheet2!E2,"00000000")&"0"))

but that is assuming that there will always be a leading and a trailing zero.

If this assumption is not true, you will need to adjust your temp station codes to be the same on each sheet and go with the Averageif() version.

cheers

**DonkeyOte** · 05-15-2010, 06:44 AM

Originally Posted by teylyn

...in Sheet1, the temp station is text, with a leading zero, for example

028970000

In Sheet2, though, the same temp station is a number, with no leading zero and only three zeros at the end, like

2897000

Averageif can't manage that.

=AVERAGEIF(Sheet1!$C:$C,TEXT($E10,"00000000")&"0",Sheet1!E:E)

(you can't manipulate the source values, you can manipulate the criteria however you wish)

Your point regards consistency of temp_station convention does hold true however.