I am getting a #Value answer for an array formula that I am using and I can
not figure out what is causing it. I have a spreadsheet with a column
describing the type of data, column with names, columns with monthly data.
For example, in the first column I have the name of different types of data
like Amb for one type of visit and Hosp for another type of visit, then the
next column contains doctor names so I would have two or more rows for Dr. X
one for each data type and if he practices in multiple locations there could
be 4 rows for 2 locations. The next columns are for each month.
My array formula is {=SUM((type=$F$2)*(name=$B15)*Jul)} on the sheet I am
working on. I have named the data type column with the range name of "type",
the name column is range name of "name" and the column with July data is
range name of "Jul". Cell reference of $f$2 refers to the particular type of
data (Amb for visit type) and $b15 refers to the particular doctor and Jul
refers to the column containing the July data.
What is baffling me is this formula is working in every instance except for
the month of August. I've checked that all the named ranges are the same
length but I can't think of anything else.
Thank you for your assistance.
--
Constance

I'd check the dimension of those named ranges once more.

And if you don't have Jan Karel Pieterse's (with Charles Williams and Matthew
Henson) Name Manager, you should get it.

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

===
Second thing I'd do is check for errors in any of those ranges--or non-numeric
stuff in the Aug range.



Constance wrote:

I am getting a #Value answer for an array formula that I am using and I can
not figure out what is causing it. I have a spreadsheet with a column
describing the type of data, column with names, columns with monthly data.
For example, in the first column I have the name of different types of data
like Amb for one type of visit and Hosp for another type of visit, then the
next column contains doctor names so I would have two or more rows for Dr. X
one for each data type and if he practices in multiple locations there could
be 4 rows for 2 locations. The next columns are for each month.
My array formula is {=SUM((type=$F$2)*(name=$B15)*Jul)} on the sheet I am
working on. I have named the data type column with the range name of "type",
the name column is range name of "name" and the column with July data is
range name of "Jul". Cell reference of $f$2 refers to the particular type of
data (Amb for visit type) and $b15 refers to the particular doctor and Jul
refers to the column containing the July data.
What is baffling me is this formula is working in every instance except for
the month of August. I've checked that all the named ranges are the same
length but I can't think of anything else.
Thank you for your assistance.
--
Constance

--

Dave Peterson

Thank you a million times! There was a text entry in my range.
--
Constance


"Dave Peterson" wrote:

I'd check the dimension of those named ranges once more.

And if you don't have Jan Karel Pieterse's (with Charles Williams and Matthew
Henson) Name Manager, you should get it.

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

===
Second thing I'd do is check for errors in any of those ranges--or non-numeric
stuff in the Aug range.



Constance wrote:

I am getting a #Value answer for an array formula that I am using and I can
not figure out what is causing it. I have a spreadsheet with a column
describing the type of data, column with names, columns with monthly data.
For example, in the first column I have the name of different types of data
like Amb for one type of visit and Hosp for another type of visit, then the
next column contains doctor names so I would have two or more rows for Dr. X
one for each data type and if he practices in multiple locations there could
be 4 rows for 2 locations. The next columns are for each month.
My array formula is {=SUM((type=$F$2)*(name=$B15)*Jul)} on the sheet I am
working on. I have named the data type column with the range name of "type",
the name column is range name of "name" and the column with July data is
range name of "Jul". Cell reference of $f$2 refers to the particular type of
data (Amb for visit type) and $b15 refers to the particular doctor and Jul
refers to the column containing the July data.
What is baffling me is this formula is working in every instance except for
the month of August. I've checked that all the named ranges are the same
length but I can't think of anything else.
Thank you for your assistance.
--
Constance

--

Dave Peterson

If you use names a lot, you'll really enjoy(?) excel more if you use Name
Manager.



Constance wrote:

Thank you a million times! There was a text entry in my range.
--
Constance

"Dave Peterson" wrote:

I'd check the dimension of those named ranges once more.

And if you don't have Jan Karel Pieterse's (with Charles Williams and Matthew
Henson) Name Manager, you should get it.

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

===
Second thing I'd do is check for errors in any of those ranges--or non-numeric
stuff in the Aug range.



Constance wrote:

I am getting a #Value answer for an array formula that I am using and I can
not figure out what is causing it. I have a spreadsheet with a column
describing the type of data, column with names, columns with monthly data.
For example, in the first column I have the name of different types of data
like Amb for one type of visit and Hosp for another type of visit, then the
next column contains doctor names so I would have two or more rows for Dr. X
one for each data type and if he practices in multiple locations there could
be 4 rows for 2 locations. The next columns are for each month.
My array formula is {=SUM((type=$F$2)*(name=$B15)*Jul)} on the sheet I am
working on. I have named the data type column with the range name of "type",
the name column is range name of "name" and the column with July data is
range name of "Jul". Cell reference of $f$2 refers to the particular type of
data (Amb for visit type) and $b15 refers to the particular doctor and Jul
refers to the column containing the July data.
What is baffling me is this formula is working in every instance except for
the month of August. I've checked that all the named ranges are the same
length but I can't think of anything else.
Thank you for your assistance.
--
Constance

--

Dave Peterson


--

Dave Peterson