Finding the most recent month's (or whatever) data
=COUNTIF($A:$A,"<" &TODAY()-60)-COUNTIF($A:$A,"<" &TODAY()-90)
gives an answer of 30
=COUNTIF($A:$A,"<" &TODAY()-60)-COUNTIF($A:$A,"<=" &TODAY()-90)
gives an answer of 29
"Dallman Ross" wrote:
In , Dallman Ross <dman@localhost.
spake thusly:
=SUMIF($A:$A,"" &TODAY()-60,G:G)/COUNTIF($A:$A,"" &TODAY()-60)
gives me an average of col. G for the last 60 days' data.
Okay, but how do I find the average of data from 31 days ago
through 60 days ago, i.e., the previous month's alone?
This seems to be the ticket:
=(SUMIF($A:$A,"<" &TODAY()-60,G:G)-SUMIF($A:$A,"<=" &TODAY()-90,G:G))/(COUNTIF($A:$A,"<" &TODAY()-60)-COUNTIF($A:$A,"<=" &TODAY()-90))
My only remaining question is, do I have the "<=" and the "<" set right
to give me a 30-day period starting 60 days ago? It kind of makes my
brain hurt thinking about that too hard. However, as a cross-check
I averaged last-30, prior-30, and the 30 before prior-30, and they
do *not* quite match. E.g., 49.389% for last-90 as opposed to
48.985% for the average of the three 30-day periods. So something
isn't perfect.
dman
|