Why this anamoly?

  • Thread starter Thread starter dindigul
  • Start date Start date
D

dindigul

While defining constants it does/does not matter whether you precede the
value by an "=", still it works. Suppose the name is "Rate" and in Refers To
box, you type either 12.5 or =12.5 both work. Why this anamoly? Thanks
 
dindigul said:
While defining constants it does/does not matter whether you precede the
value by an "=", still it works. Suppose the name is "Rate" and in Refers
To box, you type either 12.5 or =12.5 both work. Why this anamoly? Thanks
Click to expand...

When you enter a number into a cell, it's always interpreted
as a number unless you precede it with a single quote in
order to make it a string.

=12.5 is the simplest possible formula you can have. It means
"Take 12.5 and don't add or subtract anything to/from it.
The result is, of course, still a number - no anomaly at all!
 
Sun said:
While defining constants it does/does not matter whether you precede the
value by an "=", still it works. Suppose the name is "Rate" and in Refers To
box, you type either 12.5 or =12.5 both work. Why this anamoly? Thanks
Click to expand...

The = sign signals that what follows is a formula. A number is a
particularly simple type of formula, but syntactically it's a
formula.

So there's no anomaly.

The = form probably takes an infinitesimally longer time to process,
but the larger question is, why type the extra keystroke when you
don't have to?
 
dindigul said:
While defining constants it does/does not matter whether you precede the
value by an "=", still it works. Suppose the name is "Rate" and in Refers
To box, you type either 12.5 or =12.5 both work. Why this anamoly? Thanks
Click to expand...

The other two answers aren't exactly accurate with regard to defined names.
If you enter 12.5 in the Refers To box, Excel AUTOMATICALLY precedes it with
an =. You could check that by running Insert > Name > Define immediately
after defining Rate as 12.5, and you'll see that Excel stored it as =12.5.
If you were to define Test as this (no =, no double quotes), Excel will
store it as ="this". More interestingly, if you define Test as $A$1 (no =,
no double quotes), Excel stores it as ="$A$1", a text constant rather than a
cell reference.

The rule-of-thumb (since this isn't fully documented) is that if you omit
the leading =, Excel will interpret what you type in the Refers To box as a
constant. If what you type can be interpretted as a number, it'll be stored
as an = followed by the numeric constant. Otherwise, Excel will store it as
an = followed by what you typed INSIDE DOUBLE QUOTES, so as a text constant.

For numeric constants, this just doesn't matter. But it makes a big
difference for EVERYTHING ELSE.
 
Back
Top