Average Per

[Oorang]

If you Have a list of account numbers and amounts, is there a way in one forumla to get the average per account?

 

[Oaktree]

Not sure what you mean "average per account".... do you mean the average for *each* account (e.g. account 1's average is XYZ, account 2's average is ABC)?

Or, once you have the average for each account, the average of those averages (e.g. [XYZ+ABC]/2)?

Either way, I'd think a Pivot table would be better than a formula approach...

 

[barry houdini]

Hello Oorang,

I'm not quite sure I'm with you - you can get the average for a specific account with something like

=AVERAGE(IF(A$1:A$100=C1,B$1:B$100))

confirmed with CTRL+SHIFT+ENTER, where C1 contains the specific account number

or

=SUMIF(A$1:A$100,C1,B$1:B$100)/COUNTIF(A$1:A$100,C1)

 

[Oorang]

Sorry should have been more specific. What I meant was the average of each account's subtotal. Right now I am generating a unique list of account numbers, then using SumIF to get the Per-Account Total, then Averaging those Totals.

 

[Exceljeanie]

Hi,

Excel Workbook
	A	B	C	D	E
1	Account	Money		Account	Average
2	1	5		3	8.5
3	2	6
4	3	7
5	1	8
6	2	9
7	3	10
Sheet

 

[Aladin Akyurek]

A2:A10 houses accounts.
B2:B10 houses amounts.

=SUM(B2:B10)/SUM(IF(FREQUENCY(IF(A2:A10<>"",MATCH("~"&A2:A10,A2:A10&"",0)),ROW(A2:A10)-ROW(A2)+1),1))

confirmed with control+shift+enter, yields an average where N is the number of unique accounts.

Is this what you have in mind?

 

[barry houdini]

Couldn't you just use

=SUM(B2:B10)/SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&""))

 

[Aladin Akyurek]

Couldn't you just use

=SUM(B2:B10)/SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&""))

Of course. But the reason I invoke the formula with FREQUENCY is that it's a better approach to count the distinct items than the one with COUNTIF that fails with items which contais some special chars.

 

[barry houdini]

Thanks Aladin,

Foolishly I thought you might have over-elaborated

I should have delved a bit more deeply.......

 

[Oorang]

Hello All,
Thanks for your responses !
ExcelJeannie > I can see the value of this approach for certain things, but it yields only a per account average. and in this instance I am going for the average of account totals.

Aladin: That one may be a bit beyond me> I tested it out enough to be convinced it works, but I hate to use things I don't understand completly.
I understand frequency, match quite well , and have a decent grasp on array forumulas. But this one just plain lost me Could I prevail upon you to walk me through it?