Calculate average headcount YTD

[erniepoe]

Hi All,

Am trying to calculate average headcount YTD based on the month selected from the dropdown. Cell A4 is where the month is listed, and C2:N2 is January-December, and then row 3 is the headcount for that month.

I want the average headcount YTD, but not if the headcount is 0 in any month. The offset portion of this formula works before changing the average to averageif and selecting each monthly headcount (and adding the ,0 and the end), but the below formula fails.

Any idea what I am doing wrong?


AVERAGEIF(Headcount!C3:N3,">0",(OFFSET(Headcount!C3,0,0,1,MATCH(A4,Headcount!C2:N2,0))),0)

 

[kevin9999]

Perhaps:

=AVERAGEIFS(OFFSET(Headcount!C3,0,0,1,MATCH(A4,Headcount!C2:N2,0)),OFFSET(Headcount!C3,0,0,1,MATCH(A4,Headcount!C2:N2,0)),">0")