Average number of unique occurences

[ro801]

Hi. I need some help to complete a formula. I have calculated the number of unique occurrences but now I need to average number of unique occurrences.

I did this to get unique occurrences which works:

Table is below, I need the average number of times for each time same 'ID' occurs. Using the scenario below it visually would be 3+1+1/3=1.66 but fields are added to and change frequently. Any help would be appreciated.

[TABLE="width: 313"]
<tbody>[TR]
[TD]LINK_ID[/TD]
[TD]HFDU_CASE_ID[/TD]
[TD]ATT_ID[/TD]
[TD]ID[/TD]
[/TR]
[TR]
[TD]58[/TD]
[TD]29[/TD]
[TD]20/11/201729[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]59[/TD]
[TD]30[/TD]
[TD]21/11/201730[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]61[/TD]
[TD]31[/TD]
[TD]22/11/201731[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]63[/TD]
[TD]35[/TD]
[TD]06/12/201735[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]64[/TD]
[TD]36[/TD]
[TD]21/11/201736[/TD]
[TD]32[/TD]
[/TR]
</tbody>[/TABLE]

 

[admiral100]

Hi,

Try this :

E2 =COUNT(D2:D6)/SUM(--(FREQUENCY(D2:D6,D2:D6)>0))

	A	B	C	D	E
1	LINK_ID	HFDU_CASE_ID	ATT_ID	ID	Average
2	58	29	20/11/201729	31	1.666667
3	59	30	21/11/201730	31
4	61	31	22/11/201731	31
5	63	35	06/12/201735	33
6	64	36	21/11/201736	32

<colgroup><col span="3"><col><col span="2"></colgroup><tbody>
</tbody>

 

[ro801]

That's great thanks!

Hi,

Try this :

E2 =COUNT(D2:D6)/SUM(--(FREQUENCY(D2:D6,D2:D6)>0))

	A	B	C	D	E
1	LINK_ID	HFDU_CASE_ID	ATT_ID	ID	Average
2	58	29	20/11/201729	31	1.666667
3	59	30	21/11/201730	31
4	61	31	22/11/201731	31
5	63	35	06/12/201735	33
6	64	36	21/11/201736	32

<tbody>
</tbody>