Average Not Counting Zero ... but maybe a few??

[Erehwon]

Greetings - Haven't been to board in a while but have something that may be tricky so I turned to the pros!!!

Given:

Week Starts Total Work Utilization Jul-2 0% Jul-9 100% Jul-16 100% Jul-23 0% Aug-6 0% Aug-16 0% Aug-20 0% Aug-27 0% Sep-3 0% Sep-10 0% Sep-17 0% Sep-24 0% Total <tbody> </tbody>

<colgroup><col></colgroup><tbody>
</tbody>

I need to calculate the Average not including the 0%'s after the current week. However - this person was on PTO the 1st week so I DO need to count that Zero. I've used this formula int he past =AVERAGEIF(R5:R17,"<>0") to ignore zeros but it breaks when I have a legit 0 that needs to be factored in.

Any suggestions??

 

[Scott Huish]

Do you have a cell that indicates the PTO status for each week?

 

[inactiveuserps07]

@Erehwon, assuming your header "Week Starts" is in Q4 and your header "Total Work Utilization" is in R4, in R17:

=SUM($R$5:$R$16)/COUNTIF($Q$5:$Q$16,"<"&NOW()-7)

Also, just a note: you have Aug-16 in the list of "Week Starts," but that date is a Wednesday not a Sunday (which is Aug-13)