Averaging the total in a range of columns but don't count zeros in leading columns only

[kristylee]

This is a stumper - hoping someone has an idea. I need to calculate average hours worked by an employee in a quarter so I have all of the hours separated by each week in columns. I don't want to penalize new hires for the zero hours in the columns prior to when they were hired. But I do want to count the zeros if it is just a random week worked with no hours.

So in the example below, for Tom Smith, the first 2 weeks should not be counted into the average and for Jane Jones, all zeros should be counted since she had leading columns that had hours in them.

Anyone have a solution?


Tom Smith 0 0 8.5 27.75 9.25 17.25
Jane Jones 40.5 21.5 24 0 0 20

 

[admiral100]

Hi,

Try this:-

I2 =IF(IFERROR(LOOKUP(0,--(B1:G1<>0),B1:G1),1)=0,SUM(B1:G1)/COUNTIF(B1:G1,"<>0"),AVERAGE(B1:G1))

[TABLE="width: 743"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Tom Smith[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]8.5[/TD]
[TD]27.75[/TD]
[TD]9.25[/TD]
[TD]17.25[/TD]
[TD][/TD]
[TD]15.6875[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jane Jones[/TD]
[TD]40.5[/TD]
[TD]21.5[/TD]
[TD]24[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]20[/TD]
[TD][/TD]
[TD]17.66667[/TD]
[/TR]
</tbody>[/TABLE]

 

[kvsrinivasamurthy]

Try this
ARRAY formula (to be confirmed with Ctrl+Shift+Enter keys together) in I2 then drag down.

=SUM($B2:$G2)/(COLUMNS($B2:$G2)-MATCH(FALSE,$B2:$G2=0,0)+1)

 

[kristylee]

Seemed to work at first but then did some testing. If you change Tom Smith's column G to zero, it should count that last zero into the average but it doesn't. It should be average of 11.375 and it comes out as 15.16. Then, on Jane Jones I tried zeroing out columns C and D - the average should be 10.08 and it comes out as 30.25.

 

[kristylee]

Thanks for this but I tested it and something's not right. I changed Tom Smith's column G to zero (so that should figure into the average) and it gave me an average of 15.16 instead of 11.375. Then on Jane Jones I zeroed out 2 extra columns, C & D so average should be 10.08 and it comes out as 30.25. Help!

 

[kristylee]

Oops - I thought my first reply didn't go through so I'm sorry for the duplication. Couldn't figure out how to delete one of my replies. And that was in response to the first suggested solution. Re: the ARRAY one, not quite sure what that is... I have to play around a bit more.

 

[admiral100]

Hi,

Just Enter

I1 =SUM(B1:G1)/(COLUMNS(B1:G1)-MATCH(TRUE,INDEX(B1:G1<>0,),0)+1)


[TABLE="width: 714"]
<colgroup><col><col><col span="8"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Tom Smith[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]8.5[/TD]
[TD]27.75[/TD]
[TD]9.25[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]11.375[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jane Jones[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]20[/TD]
[TD][/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]




BTW , kvsrinivasamurthy 's formula works fine .