TYPE(B290:B295) in Excel 2000

[Jonathan Rynd]

Hello,

According to the documentation, if the argument to the TYPE function is an
array, the return value should be 64.

B290:B295 contain numbers only.

We are seeing an inconsistent behavior:
- The result of the formula is 16 (corresponding to "error")
- But, in the Insert Function/Function Wizard dialog, the result of the
formula shows as 64.
- Also, if the formula is entered as an array formula (i.e. press control-
shift-enter when done editing the formula) the result shows as 64.

Is this a known bug in Excel? If so, which versions have the bug?

 

[Frank Kabel]

Hi
what is the exact formula you have used?

 

[Jonathan Rynd]

Hi
what is the exact formula you have used?

I posted it in the subject of my message

=TYPE(B290:B295)

where b290 through b295 contain numbers.

 

[Frank Kabel]

Hi
this function only does not accept a range of cells (only a single cell
reference)

 

[Jonathan Rynd]

Hi
this function only does not accept a range of cells (only a single cell
reference)

Makes sense, except that the documentation says that the value of 64 is for
an array. Why is that value there if it doesn't accept a range of cells?
If it doesn't accept a range of cells, why doesn't it produce the #VALUE!
error?

 

[Harlan Grove]

According to the documentation, if the argument to the TYPE function is an
array, the return value should be 64.

B290:B295 contain numbers only.

We are seeing an inconsistent behavior:
- The result of the formula is 16 (corresponding to "error")
- But, in the Insert Function/Function Wizard dialog, the result of the
formula shows as 64.
- Also, if the formula is entered as an array formula (i.e. press control-
shift-enter when done editing the formula) the result shows as 64.

Is this a known bug in Excel? If so, which versions have the bug?

This is *NOT* a bug. It's standard functionality in most of the older
worksheet functions, i.e., if you pass ranges or derived arrays to most of
the older worksheet functions that normally expect single value arguments,
you *MUST* enter the formula as an array or Excel returns #VALUE!. TYPE only
recognizes array *CONSTANTS* as array type arguments when entered in
nonarray formula, so =TYPE({1;2;3}) returns 64.

The reason you see 64 in the Function Wizard dialog is because it always
assumes array entry (just as pressing [F9] in Edit mode does). Now I'd be
the FIRST to agree with the proposition that Microsoft should add an
indicator in that dialog to the effect that the formula SHOULD be entered as
an array formula, but that's not the same as this being a bug.

Obviously Excel's, er, documentation (online help) is sadly lacking in
details. That's been the case since Excel 3 at least (I can't say I remember
the docs for Excel 2). Since Microsoft hasn't felt the need thoroughly to
improve its documentation for Excel in the last 15 years, it'd be unwise to
expect much improvement in the near or mid-term future, and also unwise to
expend any energy beyond that needed to remember Excel's quirks.

 

[Jonathan Rynd]

This is *NOT* a bug. It's standard functionality in most of the older
worksheet functions, i.e., if you pass ranges or derived arrays to
most of the older worksheet functions that normally expect single
value arguments, you *MUST* enter the formula as an array or Excel
returns #VALUE!. TYPE only recognizes array *CONSTANTS* as array type
arguments when entered in nonarray formula, so =TYPE({1;2;3}) returns
64.

Thanks for your explanation! It was just what I needed.

It turns out my job is to pay attention to Excel's quirks. Do you know of
a website or book that lists some more of them?

 

[hgrove]

Jonathan Rynd wrote...
...

It turns out my job is to pay attention to Excel's quirks. Do you
know of a website or book that lists some more of them?

Just read this newsgroup regularly for a month and you'll come acros
most of them

 

[Jonathan Rynd]

Just read this newsgroup regularly for a month and you'll come across
most of them.

Keeping up with this newsgroup is job in and of itself. I used to read it
for 2 hours a day but that wasn't enough, I was still falling behind.

I have a very hard time reading about other people's problems without
jumping in and trying to fix them myself. Guess I need a newsreader which
doesn't allow any way to respond to what's there.