AverageIf function or macro in Excel?

September 8, 2010 at 09:59:04
Specs: Windows 7
Hi and thanks in advance for you help.

I need to calculate the average of a range of cells in Ecxel 2007 but only for cells with the same ID.

Project ID is in column A. The file is quite large with more than 300 000 entries and about 160 different Project IDs. Do I have to use a macro to do this or does an averageif function help me here?
A little example of how the file looks like

ID value1 value2
1 3 2
1 4 1
1 3 4
1 2 2
1 3 2
2 4 3
2 6 4
2 4 2


See More: AverageIf function or macro in Excel?

Report •


#1
September 8, 2010 at 10:11:35
What version of Excel are you using?

2007 has an AVERAGEIF function. Look it up in the Excel Help files.

=AVERAGEIF( Range, Criteria, Sum Range)

For 2003, you have to create your own using SUMIF and COUNTIF:

=SUMIF($A$1:$A$10,A10,$B$1:$B$109) / COUNTIF($A$1:$A$10,A1)


Report •

#2
September 8, 2010 at 11:21:18
Thanks for your reply DerbyDad
I'm using Excel 2007. I tried to figure out how the Averageif would apply for my case but couldn't. Do you know how I get the function to work in my example?

Report •

#3
September 8, 2010 at 11:40:22
What are you trying to average?

I can't test AVERAGEIF on this machine, but I assume that based on the data below, this would return 3, because there are 5 values in B2:B9 that add up to 15 associated with the ID "1".

=AVERAGEIF($A$2:$A$9, A2, $B2:$B$9)

and this would return 4.67

=AVERAGEIF($A$2:$A$9, A7, $B2:$B$9)

      A      B        C
1    ID   value1  value2
2    1       3         2
3    1       4         1
4    1       3         4
5    1       2         2
6    1       3         2
7    2       4         3
8    2       6         4
9    2       4         2

If you need the average of both columns for each ID, I'd just add up 2 AVERAGEIF formulas. (Again, this is untested)

=AVERAGEIF($A$2:$A$9, A2, $B2:$B$9) + AVERAGEIF($A$2:$A$9, A2, $C2:$C$9)

or

=SUM((AVERAGEIF($A$2:$A$9, A2, $B2:$B$9), AVERAGEIF($A$2:$A$9, A2, $C2:$C$9))


Report •

Related Solutions

#4
September 8, 2010 at 11:57:02
Thanks once again!
Its height and diameter measurements of tree plantation trial sites. There are in the whole moe than 300 000 single tress measured. I need the average of each trial. There are about 160 sites (different IDs).

If I understand your example right I would have to identify in which cell number a new ID starts and then rewrite the function accordingly. I think there is a way to this with an array function. If I find the solution I'll post it here but in the meantime I'm glad to receive any further comments.


Report •

#5
September 8, 2010 at 12:37:29
Ok I think I got it, thanks for your assistance!

The function looks like this. Supposedly you write the first ID (1) in cell A11 and the second ID (2) in cell A12 then

=AVERAGE(IF($A$2:$A$9=A11,$B$2:$B$9)) for ID = 1

When enter the function you have to hit ctrl + shift + enter.


Report •

#6
September 8, 2010 at 12:57:16
re: When enter the function you have to hit ctrl + shift + enter.

I could be wrong, but I believe that the 2007 AVERAGEIF function can be used instead of using AVERAGE and IF as an array formula.

Note that AVERAGEIF doesn't have a parenthesis between the AVERAGE and IF like yours does.

Did you look up AVERAGEIF in the Excel Help files?

I have 2010 at home...I really need to install it so I can test these new fangled functions! ;-)

re: If I understand your example right I would have to identify in which cell number a new ID starts and then rewrite the function accordingly.

If I were doing this, I would uses Data...Filter...Advanced Filter to create a filtered list of uniques IDs in another column and then just drag an AVERAGEIF formula down alongside it.

There no need to manually determine where the next ID starts. Let Excel create the list of ID's for you.


Report •

#7
September 8, 2010 at 16:06:05
Hi,

AVERAGEIF() seems to work just fine - using the example data:

ID	value1	value2
1	3	2
1	4	1
1	3	4
1	2	2
1	3	2
2	4	3
2	6	4
2	4	2

Then in A11 enter 1 and in A12 enter 2

In cell B11 enter this formula:=AVERAGEIF($A$2:$A$9,$A11,B$2:B$9)
Note the $ signs (they are different to the way you used them in your two-function formula).
Drag to extend the formula to cell C11
then select both B11 and C11 and drag them down a row to B12 and C12
This is what you get:

	A	B	C
	ID	Avg 1	Avg 2
10	1	3.0	2.2
11	2	4.7	3.0

In Excel 2007 and later there is no need to use the two-function array formula entered with Ctr+Shift+Enter.

DerbyDad03's advice looks good.

Also it is likely that the AVERAGEIF() function will be faster on large amounts of data than the array formula equivalent entered with Ctrl+Shift+Enter

Regards


Report •

#8
September 9, 2010 at 08:09:43
Thanks for your help! Works like a charm.

Just from what I experienced both formulas are reasonably fast in handling the 300 000 rows. Averageif without array is a bit faster though.


Report •

#9
September 9, 2010 at 08:18:59
Just curious...Did you go with the Filtered list suggestion?

Report •

#10
September 9, 2010 at 08:24:22
Similar.

I copied the whole ID column and removed duplicates. I think the results is the same.


Report •


Ask Question