Take average of each category and then compute the average

[alektherussian]

Hi there,

I have a problem that I can demonstrate in simplified way as follows:
[TABLE="width: 200"]
<tbody>[TR]
[TD]Company
[/TD]
[TD]Amount
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]500
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]400
[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]400
[/TD]
[/TR]
[TR]

[/TR]
</tbody>[/TABLE]
I would like a formula that would first calculate the average over each category (i.e., A = 350, B = 300, and C = 400) and then take the average of the three amounts (i.e., 350). The key is I would like this to be done with a formula(I believe it would have to be some complex array formula) without creating supplemental columns or a PivotTable.

Is this at all possible? Or is writing a custom VBA formula the only option?

Thank you all for you help!

-Alex

 

[Smitty]

Welcome to the Board!

Maybe something like this:

Excel 2012
	A	B	C	D	E	F
1	Company	Amount		Company	Average	Average All
2	A	500		A	350	350
3	B	200		B	300
4	C	400		C	400
5	A	200
6	B	400
Sheet1

Cell Formulas
Range	Formula
E2	=AVERAGEIF(Sheet1!$A$2:$A$6,D2,Sheet1!$B$2:$B$6)
E3	=AVERAGEIF(Sheet1!$A$2:$A$6,D3,Sheet1!$B$2:$B$6)
E4	=AVERAGEIF(Sheet1!$A$2:$A$6,D4,Sheet1!$B$2:$B$6)
F2	=AVERAGE(E2:E4)

HTH,

 

[Michael M]

maybe this

Excel 2007
	A	B	C	D
1	Company	Amount
2	A	500		350
3	B	200
4	C	400
5	A	200
6	B	400
Sheet3

Cell Formulas
Range	Formula
D2	=(AVERAGEIF(A2:A6,"A",B2:B6)+AVERAGEIF(A2:A6,"B",B2:B6)+AVERAGEIF(A2:A6,"C",B2:B6))/3

 

[alektherussian]

Thank you Michael M and Smitty. Both of your approaches definitely work for this situation.

It seems that I wasn't quite clear in my question. The provided example is a simplified version of the real problem. In the actual problem i will have multiple sets with more than 100 company names. This means in Smitty's solution I would have to create a range with all the unique company names or in Michael's solution, i'd have an extremely long formula and I'd need to point out to each unique company in the AVERAGEIF formula.

Thank you again for your help!

 

[Smitty]

Unfortunately, I am by no means a mathematician, so I can't think of how to do a simple weighted average like you want with only one value.

You might want to take a look at this:

Weighted Average in Excel - Formulas to Calculate Weighted Averages/ Weighted Means in Microsoft Excel | Chandoo.org - Learn Microsoft Excel Online

 

[alektherussian]

Thank you! When I was trying to figure this out I also thought that it'd probably be easier if I had some more mathematical knowledge and figure how to do such calculations at least conceptually.

 

[mikerickson]

I have mathematical training and taking the average of averages raised huge alarm bells with me.
In the OP example, the 350 final figure that you get is different than the average of the numbers (340).

Huge, Huge Huge alarm bells!!
Drawing any conclusions from the number you seek is not a good idea.

That being said, with the layout in post #2 the formula =AVERAGE(AVERAGEIF(A1:A5,D1:D3,B1:B5)) will give you this useless number.

Or you could use =AVERAGE(AVERAGEIF(A1:A5, A1:A5, B1:B5)) entered with Ctrl-Shift-Enter

 

[alektherussian]

Thank you Mikerickson - really appreciate your response! I understand the dangers of this approach and agree with you (and so do my colleagues), but some companies have a "standard methodology" do calculate certain things that don't always make sense...

Is it at all possible to perform such calculation without creating column, like column D in post #2? It would require to create a list of unique values for every new set of companies in column A.

Thank you again!

 

[mikerickson]

The second formula that I posted doesn't require a separate list of the unique entries in column A. I haven't tested to see if it will work with full column references, but dynamic ranges should accommodate a changing list of names and values.