Array Formula Problem

=AVERAGE(IF(MONTH(B$5:B$200)=J2,H$5:H$200))

The above array formula is used in 12 different cells, for each month of the
year. All of the formulas generate the proper values, except for Month 1
(January).

For January, and January ONLY, this formula only works when the range is the
exact same size as the last date in the worksheet.

For example:
Row 65 contains the last date (in Column B), Rows greater than 65 contain
null values for the date (Column B), and null values to average (Column H).
So when J2 has a value of 1, the formula only works when I change it to the
last row as follows:

=AVERAGE(IF(MONTH(B$5:B$65)=J2,H$5:H$65))

Can someone give me a hint as to what may be the problem here?

Thanks,
Bernie
p.s.
Data is sorted by date (column B), and the formula works for all months
except January.

On Thu, 27 Oct 2005 06:27:29 -0600, "bw" <[email protected]> wrote:

>=AVERAGE(IF(MONTH(B$5:B$200)=J2,H$5:H$200))
>
>The above array formula is used in 12 different cells, for each month of the
>year. All of the formulas generate the proper values, except for Month 1
>(January).
>
>For January, and January ONLY, this formula only works when the range is the
>exact same size as the last date in the worksheet.
>
>For example:
>Row 65 contains the last date (in Column B), Rows greater than 65 contain
>null values for the date (Column B), and null values to average (Column H).
>So when J2 has a value of 1, the formula only works when I change it to the
>last row as follows:
>
>=AVERAGE(IF(MONTH(B$5:B$65)=J2,H$5:H$65))
>
>Can someone give me a hint as to what may be the problem here?
>
>Thanks,
>Bernie
>p.s.
> Data is sorted by date (column B), and the formula works for all months
>except January.
>

1. What exactly happens if you don't alter the formula?
2. If you select cells below row 65, and examine the formula bar,is it empty
or is there some formula there? If so, what is the formula?


--ron

Bernie,

That is because the empty cells are being treated as 01/01/1900, i.e they
pass the month 1 test. You need to test for blanks as well

=AVERAGE(IF((MONTH(B$5:B$200)=J2)*(B$5:B$200<>""),H$5:H$200))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"bw" <[email protected]> wrote in message
news:[email protected]...
> =AVERAGE(IF(MONTH(B$5:B$200)=J2,H$5:H$200))
>
> The above array formula is used in 12 different cells, for each month of
the
> year. All of the formulas generate the proper values, except for Month 1
> (January).
>
> For January, and January ONLY, this formula only works when the range is
the
> exact same size as the last date in the worksheet.
>
> For example:
> Row 65 contains the last date (in Column B), Rows greater than 65 contain
> null values for the date (Column B), and null values to average (Column
H).
> So when J2 has a value of 1, the formula only works when I change it to
the
> last row as follows:
>
> =AVERAGE(IF(MONTH(B$5:B$65)=J2,H$5:H$65))
>
> Can someone give me a hint as to what may be the problem here?
>
> Thanks,
> Bernie
> p.s.
> Data is sorted by date (column B), and the formula works for all months
> except January.
>
>

It's because Excel sees blank as a zero (put 0 in a cell and format it as
mm/dd/yyyy and you'll see it will be January 0 1900 the day Excel dates
started) so you must have blank cells in there

=AVERAGE(IF((MONTH(B$5:B$200)=J2)*(ISNUMBER(B$5:B$200)),H$5:H$200))

should take care of it


--

Regards,

Peo Sjoblom

"bw" <[email protected]> wrote in message
news:[email protected]...
> =AVERAGE(IF(MONTH(B$5:B$200)=J2,H$5:H$200))
>
> The above array formula is used in 12 different cells, for each month of
the
> year. All of the formulas generate the proper values, except for Month 1
> (January).
>
> For January, and January ONLY, this formula only works when the range is
the
> exact same size as the last date in the worksheet.
>
> For example:
> Row 65 contains the last date (in Column B), Rows greater than 65 contain
> null values for the date (Column B), and null values to average (Column
H).
> So when J2 has a value of 1, the formula only works when I change it to
the
> last row as follows:
>
> =AVERAGE(IF(MONTH(B$5:B$65)=J2,H$5:H$65))
>
> Can someone give me a hint as to what may be the problem here?
>
> Thanks,
> Bernie
> p.s.
> Data is sorted by date (column B), and the formula works for all months
> except January.
>
>

Try instead, array-entered:
=AVERAGE(IF(($B$5:$B$200<>"")*(MONTH(B$5:B$200)=J2),H$5:H$200))
to avoid the problems faced with null values within B5:B200 evaluating to
TRUE
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"bw" <[email protected]> wrote in message
news:[email protected]...
> =AVERAGE(IF(MONTH(B$5:B$200)=J2,H$5:H$200))
>
> The above array formula is used in 12 different cells, for each month of
the
> year. All of the formulas generate the proper values, except for Month 1
> (January).
>
> For January, and January ONLY, this formula only works when the range is
the
> exact same size as the last date in the worksheet.
>
> For example:
> Row 65 contains the last date (in Column B), Rows greater than 65 contain
> null values for the date (Column B), and null values to average (Column
H).
> So when J2 has a value of 1, the formula only works when I change it to
the
> last row as follows:
>
> =AVERAGE(IF(MONTH(B$5:B$65)=J2,H$5:H$65))
>
> Can someone give me a hint as to what may be the problem here?
>
> Thanks,
> Bernie
> p.s.
> Data is sorted by date (column B), and the formula works for all months
> except January.
>
>

Wow! Thanks guys...
I never would never have been able to solve this "mysterious problem"
without your help.

Thanks again,
Bernie

"bw" <[email protected]> wrote in message
news:[email protected]...
> =AVERAGE(IF(MONTH(B$5:B$200)=J2,H$5:H$200))
>
> The above array formula is used in 12 different cells, for each month of
> the year. All of the formulas generate the proper values, except for
> Month 1 (January).
>
> For January, and January ONLY, this formula only works when the range is
> the exact same size as the last date in the worksheet.
>
> For example:
> Row 65 contains the last date (in Column B), Rows greater than 65 contain
> null values for the date (Column B), and null values to average (Column
> H). So when J2 has a value of 1, the formula only works when I change it
> to the last row as follows:
>
> =AVERAGE(IF(MONTH(B$5:B$65)=J2,H$5:H$65))
>
> Can someone give me a hint as to what may be the problem here?
>
> Thanks,
> Bernie
> p.s.
> Data is sorted by date (column B), and the formula works for all months
> except January.
>