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?

 

[tusharm]

Create a PivotTable. Put the account # as the row field and the amounts as the data field. Then, change the statistic that Excel picks (probably sum) to Average.

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

 

[Exceljeanie]

Special chars

Hi Aladin

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.

Thats a good point against countif in some circumstances (I never had the idea that someone uses specialchars)

These are ? and * and ~

Are there more by your experience?

So if these could occur you have to replace in the searchitem first ~ with ~~ and then the others with ~* and ~?


TX for this eyeopener!

 

[Aladin Akyurek]

Re: Special chars

Hi Aladin

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.

Thats a good point against countif in some circumstances (I never had the idea that someone uses specialchars)

These are ? and * and ~

Are there more by your experience?

So if these could occur you have to replace in the searchitem first ~ with ~~ and then the others with ~* and ~?


TX for this eyeopener!

Entries like

<

< Tlex >

 

[Exceljeanie]

Hi Aladin,
tx for this additional information.

Of course these must be problemst to.

Also my stomach turns around on the possibilty, that there are captions outside, which use one of these chars.

Perhaps with barcodes.
But to get sensibilisized is important.

Thanks again

 

[Exceljeanie]

Hi Aladin,
I'm so in this thing now, that I looked deeper in your formula.
And I found:

Excel Workbook
	A	B	C	D
1
2	Haus	50
3	Hausen	100
4	Hausen	100
5	Haus*	70		160
6
7
8	Haus	50
9	Hausen	100
10	Hausen	100
11	Hausa	70		106.6666667
Sheet

So there seems to be a problem too or am I wrong


Added:

And a string ending with ~ will give a problem too.
Due to the formula Match.

 

[tusharm]

Just in case someone responds to my post...I am no longer watching this topic since it seems to have become a series of posts on something not directly related to the original topic.

 

[pgc01]

Hi

Thats a good point against countif in some circumstances (I never had the idea that someone uses specialchars)

These are ? and * and ~

Are there more by your experience?

Besides the wildcards ("*", "?") and the escape character "~" I had problems with the comparison operators. If the first character is a comparison operator, countif interprets the value as a pattern.

I believe that using a direct comparison avoids all these problems:

=SUM(B2:B5)/SUM((A2:A5<>"")/MMULT(--(A2:A5=TRANSPOSE(A2:A5)),ROW(A2:A5)^0))

Remark: Another advange of this formula is that you can also use it if the record is defined by the combination of several columns, for example (Territory, Account). In this case you just have to add another term to the equation.

The problem here is to calculate the number of unique values. To illustrate the differences, I post several cases and the results of the number of unique values for the 3 formulas: the countif formula, the frequency formula and the comparison formula.

=SUMPRODUCT((A2:A5<>"")/COUNTIF(A2:A5,A2:A5&""))
=SUM(IF(FREQUENCY(IF(A2:A5<>"",MATCH("~"&A2:A5,A2:A5&"",0)),ROW(A2:A5)-ROW(A2)+1),1))
=SUM((A2:A5<>"")/MMULT(--(A2:A5=TRANSPOSE(A2:A5)),ROW(A2:A5)^0))
The last 2 are CSE.

Please test it.

Kind regards
PGC

P. S. Although I wrote all this about the problems of using Countif to count the unique values, the truth is that the countif formula is the one that I use the most.
In fact, the special characters are not usually a problem, like in this Oorang problem with account numbers and amounts.

Book1
	A	B	C	D	E	F
1			Average	Countif	Frequency	Comparison
2	Haus	50
3	Hausen	100
4	Hausen	100
5	Haus*	70	106.6667	2.25	2	3
6
7	Haus	50
8	Hausen	100
9	Hausen	100
10	Hausa	70	106.6667	3	3	3
11
12	Haus	50
13	Hausen	100
14	Hausen	100
15	Haus~	70	106.6667	3	2	3
16
17	Haus	50
18	Hausen	100
19	Hausen	100
20	Hau?	70	106.6667	2.5	2	3
21
22
23	Hausen	100
24	Hausen	100
25	Haus~	70	135	2	#N/A	2
26
27	Haus	50
28	Hausen	100
29	Hausen	100
30	<=Haus	70	106.6667	2.5	3	3
Sheet4