Excel 2007 named ranges - how to use as function parameter?

[Gershon]

Specific case: back in Excel 2003 days, I could have column A = a few decimal
numbers. I named column A "Counter". In column B I could enter the formula
=dec2bin(Counter,6) and each cell gave me the binary representation of the
corresponding cell in column A, just as expected.
The same exact thing in Excel 2007results in a #VALUE! error. I expected
Excel 2007 to do everything that Excel 2003 could.
Why does this happen and how do I fix it?

Regards
GS

 

[Homey]

you say-

back in Excel 2003 days, I could have column A = a few decimal numbers. I
named column A "Counter". In column B I could enter the formula
=dec2bin(Counter,6) and each cell gave me the binary

I try with dec2bin in excel 2003 and it not work. give #Num! error. work
okay with built in function like Round.

| Specific case: back in Excel 2003 days, I could have column A = a few
decimal
| numbers. I named column A "Counter". In column B I could enter the formula
| =dec2bin(Counter,6) and each cell gave me the binary representation of the
| corresponding cell in column A, just as expected.
| The same exact thing in Excel 2007results in a #VALUE! error. I expected
| Excel 2007 to do everything that Excel 2003 could.
| Why does this happen and how do I fix it?
|
| Regards
| GS

 

[Reg]

well the function exists in 2007 so here some things to check

the following excerpt for limits on numbers

If number < -512 or if number > 511, DEC2BIN returns the #NUM! error value.
If number is nonnumeric, DEC2BIN returns the #VALUE! error value.
If DEC2BIN requires more than places characters, it returns the #NUM! error
value.
If places is not an integer, it is truncated.
If places is nonnumeric, DEC2BIN returns the #VALUE! error value.
If places is negative, DEC2BIN returns the #NUM! error value.


also the wording in your questions is a little odd - putting Counter as a
header doesnt 'name' the range unless you are using a table and trying to use
=dec2bin(Counter,6) when the range contains more than one number would error
unless its entered as an array formula.

hth

RegMigrant

 

[Gord Dibben]

Do you have the Analysis Toolpak add-in loaded?

Your #NUM! error may actually be #NAME!


Gord Dibben MS Excel MVP