A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old May 11th, 2010, 11:40 PM posted to microsoft.public.excel.misc
Gershon
external usenet poster
 
Posts: 1
Default Excel 2007 named ranges - how to use as function parameter?

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
  #2  
Old May 12th, 2010, 02:12 PM posted to microsoft.public.excel.misc
Homey[_2_]
external usenet poster
 
Posts: 57
Default Excel 2007 named ranges - how to use as function parameter?

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.

"Gershon" wrote in message
...
| 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

  #3  
Old May 12th, 2010, 03:06 PM posted to microsoft.public.excel.misc
Reg
external usenet poster
 
Posts: 71
Default Excel 2007 named ranges - how to use as function parameter?

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

"Gershon" wrote:

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

  #4  
Old May 12th, 2010, 08:27 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 20,252
Default Excel 2007 named ranges - how to use as function parameter?

Do you have the Analysis Toolpak add-in loaded?

Your #NUM! error may actually be #NAME!


Gord Dibben MS Excel MVP

On Wed, 12 May 2010 09:12:20 -0400, "Homey" none wrote:

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.

"Gershon" wrote in message
...
| 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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:51 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.