Calculating the average no. of staff per week

[FredFitzgerald]

Hi Guys

I want a formula to calculate the average number of staff working per week in the attached data. Any help?


Many thanks

[cbatrody]

Hi,

Please see the attached file. I could do this with the help of a Pivot Table after adding 2 helper columns to the data source.

[cbatrody]

Without the Pivot Table. This is an array formula, please press CTRL+SHIFT+ENTER to confirm.

Please SORT the Data -> Week first (smallest to largest) & Staff A-Z before applying the formula.

=SUM(IF(A2:A4989&B2:B4989<>A1:A4988&B1:B4988,1,0))/SUM(IF(A2:A4989<>A1:A4988,1,0))

[samba_ravi]

=SUM(IF(FREQUENCY(COUNTIF(B2:B4989,">="&B2:B4989)+MATCH(A2:A4989,A2:A4989,0)*1000,COUNTIF(B2:B4989,">="&B2:B4989)+MATCH(A2:A4989,A2:A4989,0)*1000)>0,1,0))/SUM(IF(FREQUENCY(MATCH(A2:A4989,A2:A4989,0)*1000,MATCH(A2:A4989,A2:A4989,0)*1000)>0,1,0))
Try this formula
Otherwise give expected Result

[boopathiraja]

Try

COUNTA(A2:A5000)/SUMPRODUCT((A2:A5000<>"")/COUNTIF(A2:A5000,A2:A5000&""))

[boopathiraja]

Try

COUNTA(A2:A5000)/SUMPRODUCT((A2:A5000<>"")/COUNTIF(A2:A5000,A2:A5000&""))

[FlameRetired]

Does this do what you want?
It required some "helper" columns for the problem to "explain itself" to me as I went.
The final answer you seek is in your yellow highlighted cell.

Hope this helps.


Calculating the average no. of staff per week.xlsx

[samba_ravi]

=SUM(IF(FREQUENCY(COUNTIF(B2:B4989,">="&B2:B4989)+MATCH(A2:A4989,A2:A4989,0)*1000,COUNTIF(B2:B4989,">="&B2:B4989)+MATCH(A2:A4989,A2:A4989,0)*1000)>0,1,0))/SUM(IF(FREQUENCY(MATCH(A2:A4989,A2:A4989,0)*1000,MATCH(A2:A4989,A2:A4989,0)*1000)>0,1,0))
this will solve your problem without helper column try it

[FlameRetired]

=SUM(IF(FREQUENCY(COUNTIF(B2:B4989,">="&B2:B4989)+MATCH(A2:A4989,A2:A4989,0)*1000,COUNTIF(B2:B4989,">="&B2:B4989)+MATCH(A2:A4989,A2:A4989,0)*1000)>0,1,0))/SUM(IF(FREQUENCY(MATCH(A2:A4989,A2:A4989,0)*1000,MATCH(A2:A4989,A2:A4989,0)*1000)>0,1,0))
this will solve your problem without helper column try it

So it does.
Amazing formula, nflsales.

[FlameRetired]

Try this one.

Please Login or Register  to view this content.

It does not have to be array entered.

This one works, too.

Please Login or Register  to view this content.

Hope this helps.