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))

[TABLE="width: 445"]
<colgroup><col span="3"><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]LINK_ID[/TD]
[TD]HFDU_CASE_ID[/TD]
[TD]ATT_ID[/TD]
[TD]ID[/TD]
[TD]Average[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]58[/TD]
[TD]29[/TD]
[TD]20/11/201729[/TD]
[TD]31[/TD]
[TD]1.666667[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]59[/TD]
[TD]30[/TD]
[TD]21/11/201730[/TD]
[TD]31[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]61[/TD]
[TD]31[/TD]
[TD]22/11/201731[/TD]
[TD]31[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]63[/TD]
[TD]35[/TD]
[TD]06/12/201735[/TD]
[TD]33[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]64[/TD]
[TD]36[/TD]
[TD]21/11/201736[/TD]
[TD]32[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[ro801]

That's great thanks!

Hi,

Try this :

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

[TABLE="width: 445"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]LINK_ID[/TD]
[TD]HFDU_CASE_ID[/TD]
[TD]ATT_ID[/TD]
[TD]ID[/TD]
[TD]Average[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]58[/TD]
[TD]29[/TD]
[TD]20/11/201729[/TD]
[TD]31[/TD]
[TD]1.666667[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]59[/TD]
[TD]30[/TD]
[TD]21/11/201730[/TD]
[TD]31[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]61[/TD]
[TD]31[/TD]
[TD]22/11/201731[/TD]
[TD]31[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]63[/TD]
[TD]35[/TD]
[TD]06/12/201735[/TD]
[TD]33[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]64[/TD]
[TD]36[/TD]
[TD]21/11/201736[/TD]
[TD]32[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]