XL2003 inconsistency fixed in XL2007?
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. |
XL2003 inconsistency fixed in XL2007?
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. | |
XL2003 inconsistency fixed in XL2007?
"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). |
All times are GMT +1. The time now is 01:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com