Formula for grouping the average of daily values by Julian Week

**XANADU79** · 06-06-2011, 01:06 PM

I am looking for a formula when given a daily results will average the results grouping into Julian Week. (Basically 5 days of data averaged, but by week). The formula also needs to excude when values of "0" are reported.

Where:
Column A = Date
Column B = Julian Week Number
Column C= Daily Percentage
Column D= To equal average of daily results within a Julian Week that excludes "0" values.

**ChemistB** · 06-06-2011, 01:16 PM

Try this
=SUMPRODUCT(--($B$2:$B$500 = J1),--($C$2:$C$500 >0), ($C$2:$C$500))/SUMPRODUCT(--($B$2:$B$500 = J1),--($C$2:$C$500 >0))
Where J1 is the cell containing your week # to average. Does that work for you?

**XANADU79** · 06-07-2011, 10:38 PM

This did not give me the desired outcome.

I have attached a test file to review.

I need to group the incidents on a single day that are tracked for each person. Then, I need the average of those incidents on that single day. Then, I need the average of the daily incidents on a weekly basis, excluding "0", and working on a rolling basis to cover the entire year.

When I used your formula, excel "Help" made a few adjustments:
=SUMPRODUCT(--$C$2:$C$236)*($D$2:$D$236>0)*($D$2:$D$236)/SUMPRODUCT(--($C$2:$C$236),--($D$2:$D$236>0))

Resulting in an average of 114.

**ChemistB** · 06-08-2011, 08:42 AM

Have you ever tried pivot tables? See attachment. Is that what you are looking for?

**XANADU79** · 06-08-2011, 02:35 PM

This is EXACTLY what I was looking for!

Thank you!

**XANADU79** · 06-08-2011, 07:18 PM

Is there a way to exclude values of "0" when averaging daily and weekly?

**ChemistB** · 06-09-2011, 09:00 AM

Hmm, the only thing I can tell you is to remove the zeros from your original list. Do a Search and Replace making sure that the option of "Match Entire Cell Contents" is checked. Find 0 Replace with nothing > Replace all. Then Refresh your pivot tables (Pivot tables do not refresh themselves)