Using Office Excel 2003, if A1 contains ="", SUM(A1) returns 0, but
ROUND(A1,0) returns #VALUE!. I prefer the SUM result. Has ROUND (et
al) been fixed in Excel 2007?

By the way, SUM("") returns #VALUE!. So even SUM is not consistent
with itself. Sigh.

Still the same in Excel2007

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"joeu2004" wrote in message oups.com...
| Using Office Excel 2003, if A1 contains ="", SUM(A1) returns 0, but
| ROUND(A1,0) returns #VALUE!. I prefer the SUM result. Has ROUND (et
| al) been fixed in Excel 2007?
|
| By the way, SUM("") returns #VALUE!. So even SUM is not consistent
| with itself. Sigh.
|

"joeu2004" wrote...
Using Office Excel 2003, if A1 contains ="", SUM(A1) returns 0, but
ROUND(A1,0) returns #VALUE!. I prefer the SUM result. Has ROUND
(et al) been fixed in Excel 2007?

By the way, SUM("") returns #VALUE!. So even SUM is not consistent
with itself. Sigh.

It *IS* consistent, but you have to know what the specs are to which
it's consistent. The aggregating functions (those that can take 3D
references and variable number of arguments) expect range references
(and 3D references) as arguments, and they ignore blank cells and
cells evaluating to text or boolean values as they iterate through all
cells in range and 3D arguments. Single cell references are still
range references.

Pretty much all other numeric functions (those that take numeric
arguments and return numeric results) choke on cell references that
evaluate to non-numeric text. All numeric functions, including the
aggregating functions, choke on non-numeric expressions (constants or
terms that evaluate to numeric, text, boolean or error values but NOT
to range references).

If you want Excel to treat blank cells as zeros, turn on Transition
Formula Evaluation, but be prepared for unwanted behavior. With
Transition Formula Evaluation enabled and A1 blank,

=ROUND(A1,0)

and

=SUM("")

happily return 0, but so does

=2*"3"

IOW, Excel is perfectly consistent with its own design specs. If you
don't like it, use something else ('cause MSFT ain't likely to change
it).