GNOME Bugzilla – Bug 356252
Sum of selected cells in status bar counts dates as numbers
Last modified: 2006-09-18 13:42:24 UTC
Please describe the problem: The gnumeric status bar includes a total of all selected cells, in the appropriate format (e.g. a selection of currency formatted cells will be shown with a currency symbol). Unfortunately, the standard spreadsheet convention of value is used, which includes dates being counted as numbers of days since some epoch or something. If you select two date cells, it shows you a meaningless total of these numbers as a date (meaningless since it depends on the value of the epoch). Worse, if you select e.g. several currency cells which happen to have a date in the middle of them, the value of the currency cells will be added to the date in some incredibly meaningless way, and shown as a new date. This should be fixed by treating date formatted cells as 0, in the same way strings are, for the purposes of this sum. Steps to reproduce: 1. Create a new spreadsheet 2. Enter a currency value into cell A1 (eg $100) 3. Enter a date into cell A2 (eg 2005-01-01) 4. Select A1 and A2 Actual results: The total displayed in the status bar is a large currency value (eg $38,453). Expected results: The date should be counted as 0, like a string, when totaling numbers. The total displayed should be the same as the value in A1 (eg $100). Does this happen every time? Every time Other information: This bug was forwarded from the Ubuntu bug database #50662. For more information see: https://launchpad.net/distros/ubuntu/+source/gnumeric/+bug/50662
dates are numbers. If you want to enter a date as a string, precede it with a '. Lots of users depende on the ability to calculate with dates as if they are numbers. The behaviour of "sum" in the status bar is consistent with the SUM spreadsheet function (and virtually all spreadsheet applications agree on this behaviour of SUM).
Let me elaborate a bit on that. It's not that we cannot see that adding dates and dollar amounts is bogus. Clearly that is bogus. The problems I see are... 1. It makes perfect sense to add 10-Jun-2002, 42, and 11. The result is 55 days later than the given date. Hence the proposed solution would not work. 2. The preview area can do other things but sum. There is average, min, max and others. The three mentioned here make sense on two dates, for example, even if sum does not. I think we would end up confusing more by having those operate on different subsets of the input. 3. As Andreas said, the preview is meant to be a preview of what SUM would do. Any change to that would break Excel compatibility. 4. We simply cannot solve the general problem. If you add a cell containing my height in m (1.95) to a cell containing the speed of light in m/s (299792458) you get nonsense. Garbage in, garbage out. What we could do is to add a warning or drop the fancy formatting when we detect nonsense in the few cases we can. But I don't think that would be really helpful as people who look in the preview almost always will be people who have selected a group of compatible numbers to add. I.e., I don't think it would be worth the effort.