#NUM! Error for named formula

[Don]

I am doing a very simple named formula that doesn't work (#NUM! error). Here
are the details:

I have two column ranges named CoL_A,and Col_B (sheet1!$A:$A, sheet1!$B:$B).

Col_A and Col_B have small random numbers in each row.

I have a formula named TOT that is simply defined as =Col_A+Col_B.

When I put =tot in column C I get a #NUM! error (any row).

If I put =Col_A+Col_B in column C it works fine (any row).

What am I doing wrong, It's driving me crazy.

I'm using Excel 2003 and Excel 2000 on two different computers, same error
in each one.

Don

 

[Dave Peterson]

How about:
=sum(col_A,col_B)

I bet that there is something non-numeric in one of those ranges.

But it's a good thing you got the error.

if you had used:
=a1:a10+b1:b10

You may not expect to see what's returned!

 

[Don]

I tried =a1:a10+b1:b10 in cell c3 and got the sum of a2 and b2 which sounds
right.

I tried =sum(col_A,col_B) and got the sum of column a plus the sum of column
b. which also sounds right.

I still get #NUM! when I use =tot.

 

[Dave Peterson]

Sorry, I missed that extra name.

Try defining Tot as:
=sum(Col_A,Col_B)

 

[Charles Williams]

XL doesn't like using entire columns in Named Formulae: works fine if
you use $A$1:$A$65000 etc

regards
Charles

 

[Don]

Yup, that did it.

Thanks very much. I guess it's a bug.

I also noticed that sometimes if there is a non-numeric number anywhere in
the columns it causes a #NUM! error. I think Dave Peterson mentioned that
issue.

Thanks again.