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%

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%

Hmm getting #DIV/0! Would the date format have anyhting to do with it? I
type 2005/01/01 and it formats it to 01/01/2005

"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%

=SUMPRODUCT(--(MONTH(A1:A10)=MONTH(TODAY())),--(B1:B10<0),--(B1:B10))/SUMPRODUCT(--(MONTH(A1:A10)=MONTH(TODAY())),--(B1:B10<0))
Been trying to play with this code but to no resolve. I still get the
#DIV/0! error for the results. I tried setting the < to just or < since
the 95.4% is actually 0.954 turned into percentage. But still no go. I
played around with the date format, I even checked to see that the MONTH and
TODAY function part of the coding pulled teh right numbers and it did. Could
anyone else maybe see why this code isn't working for me?

"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%

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%