Running a Daily MTD average.
 

A B C
1 DATE RECOVERY MTD
2 1/1/2005 95.36% 95.36%
3 1/2/2005 95.17% 95.26%
4 1/3/2005 92.27% 94.26%

ok this is what I'm looking for. Problem is the MTD Calculation has to be
edited for each row and month.

C2 =AVERAGE(C2)
C3 =AVERAGE(C2:C3)
C4 =AVERAGE(C2:C4)

Then when 2/1/2005 hit I need to start over

C33 =AVERAGE(C33)
C34 =AVERAGE(C33:C34)

I need a formula that would know what MONTH range and DAY RANGE it was to
run the average for the corrisponding DATE. Any ideals?

David Billigmeier (Thanks David)
gave me this formula but it didn't want to work.

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))

Try this:

Put this formula in Cell C2 and copy down:
=SUMPRODUCT(--(TEXT($A$2:A2,"YYYYMM")=TEXT(A2,"YYYYMM"))*$C$2:C2 )

Does that help?

--
Regards,
Ron


"Mike Punko" wrote:

A B C
1 DATE RECOVERY MTD
2 1/1/2005 95.36% 95.36%
3 1/2/2005 95.17% 95.26%
4 1/3/2005 92.27% 94.26%

ok this is what I'm looking for. Problem is the MTD Calculation has to be
edited for each row and month.

C2 =AVERAGE(C2)
C3 =AVERAGE(C2:C3)
C4 =AVERAGE(C2:C4)

Then when 2/1/2005 hit I need to start over

C33 =AVERAGE(C33)
C34 =AVERAGE(C33:C34)

I need a formula that would know what MONTH range and DAY RANGE it was to
run the average for the corrisponding DATE. Any ideals?

David Billigmeier (Thanks David)
gave me this formula but it didn't want to work.

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))

--
Regards,
Ron


"Ron Coderre" wrote:

Try this:

Put this formula in Cell C2 and copy down:
=SUMPRODUCT(--(TEXT($A$2:A2,"YYYYMM")=TEXT(A2,"YYYYMM"))*$C$2:C2 )

Does that help?

--
Regards,
Ron


"Mike Punko" wrote:

A B C
1 DATE RECOVERY MTD
2 1/1/2005 95.36% 95.36%
3 1/2/2005 95.17% 95.26%
4 1/3/2005 92.27% 94.26%

ok this is what I'm looking for. Problem is the MTD Calculation has to be
edited for each row and month.

C2 =AVERAGE(C2)
C3 =AVERAGE(C2:C3)
C4 =AVERAGE(C2:C4)

Then when 2/1/2005 hit I need to start over

C33 =AVERAGE(C33)
C34 =AVERAGE(C33:C34)

I need a formula that would know what MONTH range and DAY RANGE it was to
run the average for the corrisponding DATE. Any ideals?

David Billigmeier (Thanks David)
gave me this formula but it didn't want to work.

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))

Almost, it just gives me the sum of the percentages

95.36%
190.53%
282.79%

if I then divide it by the DD it works. now I jsut need to filter out any
Dates where the Recovery was 0.00%

=SUMPRODUCT(--(TEXT($A$3:A3,"YYYYMM")=TEXT(A3,"YYYYMM"))*$C$3:C3 )/TEXT(A3,"DD")


"Ron Coderre" wrote:


--
Regards,
Ron


"Ron Coderre" wrote:

Try this:

Put this formula in Cell C2 and copy down:
=SUMPRODUCT(--(TEXT($A$2:A2,"YYYYMM")=TEXT(A2,"YYYYMM"))*$C$2:C2 )

Does that help?

--
Regards,
Ron


"Mike Punko" wrote:

A B C
1 DATE RECOVERY MTD
2 1/1/2005 95.36% 95.36%
3 1/2/2005 95.17% 95.26%
4 1/3/2005 92.27% 94.26%

ok this is what I'm looking for. Problem is the MTD Calculation has to be
edited for each row and month.

C2 =AVERAGE(C2)
C3 =AVERAGE(C2:C3)
C4 =AVERAGE(C2:C4)

Then when 2/1/2005 hit I need to start over

C33 =AVERAGE(C33)
C34 =AVERAGE(C33:C34)

I need a formula that would know what MONTH range and DAY RANGE it was to
run the average for the corrisponding DATE. Any ideals?

David Billigmeier (Thanks David)
gave me this formula but it didn't want to work.

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))

Try...

E2, copied down:

=AVERAGE(IF($A$2:A2-DAY($A$2:A2)+1=DATE(YEAR(A2),MONTH(A2),1),$C$2:C2) )

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
"Mike Punko" wrote:

A B C
1 DATE RECOVERY MTD
2 1/1/2005 95.36% 95.36%
3 1/2/2005 95.17% 95.26%
4 1/3/2005 92.27% 94.26%

ok this is what I'm looking for. Problem is the MTD Calculation has to be
edited for each row and month.

C2 =AVERAGE(C2)
C3 =AVERAGE(C2:C3)
C4 =AVERAGE(C2:C4)

Then when 2/1/2005 hit I need to start over

C33 =AVERAGE(C33)
C34 =AVERAGE(C33:C34)

I need a formula that would know what MONTH range and DAY RANGE it was to
run the average for the corrisponding DATE. Any ideals?

David Billigmeier (Thanks David)
gave me this formula but it didn't want to work.

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))/SUMPROD
UCT(--(MONTH(A1:A10)=MONTH(TODAY())),--(B1:B10<0))