Running a Daily MTD average.


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

Advertisements

G

Guest

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?
 
G

Guest

--
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?
 
G

Guest

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

Advertisements

D

Domenic

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top