Running a Daily MTD average.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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


Ron Coderre said:
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?
Click to expand...
 
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")
 
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!
 
Back
Top