OK I think I figured out the bug with this code. Either that or I'm just got
a ID10T error. I entered the code as

=SUMPRODUCT(--(MONTH(A2:A366)=MONTH(DAY(A2:A366))),--(C2:C366<0),--(C2:C366))/SUMPRODUCT(--(MONTH(A2:A366)=MONTH(DAY(A2:A366))),--(C2:C366<0))

I then copied the code down the rows. so the next row ended up being.

=SUMPRODUCT(--(MONTH(A3:A367)=MONTH(DAY(A3:A367))),--(C3:C367<0),--(C3:C367))/SUMPRODUCT(--(MONTH(A3:A367)=MONTH(DAY(A3:A367))),--(C3:C367<0))

My result for the MTD % are basicly in reverse order. what I mean is that
it's showing my the MTD% numbers from the 31st to the 1st and not the 1st to
the 31st. if I was able to have the array look backwards this would work.


"David Billigmeier" wrote:

This is a long one... Assume your dates are in the range A1:A10 and your
Recovery is in B1:B10:

=SUMPRODUCT(--(MONTH(A1:A10)=MONTH(TODAY())),--(B1:B10<0),--(B1:B10))/SUMPRODUCT(--(MONTH(A1:A10)=MONTH(TODAY())),--(B1:B10<0))

Basically this sums all values in column B that fall within the current
month, and divides by the number of values that fall within the current month
and are not 0.

Hope that helps,

--
David Billigmeier


"Mike Punko" wrote:

Trying to setup a column to show the Month to Date average. My data sheet
looks like this. I also need this not to average 0.0% values

DATE RECOVERY MTD
1/1/05 95.4%
1/2/05 95.2%
1/3/05 92.3%
1/4/05 0.0%
1/5/05 91.2%