Greater than a month
 

I have these two formulas below working great. I have been asked to modify
it by doing the calculation for a period greater than one month. I currently
identify the month by, "TEXT(G17,"mmyyyy")". How do I modify this to
calculate if the data is between two dates. Thanks.

=COUNT(IF(TEXT(Work!$N$3:$N$5724,"mmyyyy")=TEXT(G1 7,"mmyyyy"),IF(Work!$O$3:$O$5724="",Work!$O$3:$O$5 724)))

=AVERAGE(IF(TEXT(Work!$N$3:$N$5724,"mmyyyy")=TEXT( G17,"mmyyyy"),IF(Work!$O$3:$O$5724<"",Work!$B$3:$ B$5724)))

Greater than a month
 

=COUNT(IF(TEXT(Work!$N$3:$N$5724,"mmyyyy")=TEXT(G 17,"mmyyyy"),IF(Work!$O$3:$O$5724="",Work!$O$3:$O$ 5724)))

What is that formula supposed to be doing?

If you want an average based on a date range...

A1 = lower date boundary, for example 1/1/2010
B1 = upper date boundary, for example 1/15/2010

Array entered**:

=AVERAGE(IF(date_range=A1,IF(date_range<=B1,range _to_average)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
I have these two formulas below working great. I have been asked to modify
it by doing the calculation for a period greater than one month. I
currently
identify the month by, "TEXT(G17,"mmyyyy")". How do I modify this to
calculate if the data is between two dates. Thanks.

=COUNT(IF(TEXT(Work!$N$3:$N$5724,"mmyyyy")=TEXT(G1 7,"mmyyyy"),IF(Work!$O$3:$O$5724="",Work!$O$3:$O$5 724)))

=AVERAGE(IF(TEXT(Work!$N$3:$N$5724,"mmyyyy")=TEXT( G17,"mmyyyy"),IF(Work!$O$3:$O$5724<"",Work!$B$3:$ B$5724)))

Greater than a month
 

Hi,

Here's a modified AVERAGE formula that does what you want. Note we are now
compoaring full dates so we can drop the TEXT bits of the formula. The date
ranges are now G17 (Earlier date) and f17 (Later date)

=AVERAGE(IF(Work!$N$3:$N$5724=G17,IF(Work!$N$3:$N $5724<=F17,IF(Work!$O$3:$O$5724<"",Work!$B$3:$B$5 724))))

The other formula seems to ne making hard work of doing something simple and
could be modified to

=SUMPRODUCT((N3:N14=G17)*(O3:O14=""))

or now to your new requirement to include a start end date

=SUMPRODUCT((Work!N3:N5724=G17)*(Work!N3:N5724<=F 17)*(Work!O3:O5724=""))

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"PAL" wrote:

I have these two formulas below working great. I have been asked to modify
it by doing the calculation for a period greater than one month. I currently
identify the month by, "TEXT(G17,"mmyyyy")". How do I modify this to
calculate if the data is between two dates. Thanks.

=COUNT(IF(TEXT(Work!$N$3:$N$5724,"mmyyyy")=TEXT(G1 7,"mmyyyy"),IF(Work!$O$3:$O$5724="",Work!$O$3:$O$5 724)))

=AVERAGE(IF(TEXT(Work!$N$3:$N$5724,"mmyyyy")=TEXT( G17,"mmyyyy"),IF(Work!$O$3:$O$5724<"",Work!$B$3:$ B$5724)))