SUM function resulting WRONG values when some cell contains a string

Question

Level 1

8 points

SUM function resulting WRONG values when some cell contains a string

A very strange problem that can result in wrong financial calculations and big mistakes...

If we have SUM function for some range of cells and one of them is treated as text string (I mean it's number in fact but Number is sure it's text string). than SUM function calulates ALL cells except this sell silently without errors and results in wrong result.

For example we have cells and values

A B
=====
1| 15 USD
2| 0 USD
3| 37 USD
4| 1 USD
5| 3 USD

here the function SUM(B1:B5) should provide the result "56 USD"

But if cell B5 is treated as text string (it can be still displayed as "3 USD" so we don't know it's treated as text string for some reason) than SUM(B1:B5) swill answer "53 USD".

The problem is we DON'T know cell B5 is treated as text and we are fully sure that we have the correct sum of all 5 cells, in reality we see sum of 4 cells only....

I think anybody working with financial or other important data can imagine how dangerous this SUM behaviour is.... I encountered several times that when importing data from Excel or by other ways numbers are imported as text strings. So if I have a lot of cells I'm not sure if all those cells are correctly styled or some of them for some unknown reason is treated as text string.

In Microsoft Excel there is a very right solution. In MS Excel if any cell in the SUM is wrong format the SUM function simply provides error as a result. But in Numbers SUM doesn't tell us about error, it simply provides us with a wrong result!

With formal logic the way SUM works in Numbers is total idiotism. If I ask to SUM B1:B5 than I definitely ask to sum ALL 5 cells, not 4, not 3, not 6 but 5 definite cells. If Numbers can't sum those exactly those 5 cells the only answer of the function should be ERROR.

But Apple Numbers answers me some result of summing those cells that it's able to sum. Apple Numbers SUM function is not confused that I asked for SUM of 5 (not 4 or 3) cells and I expect to have this sum as a result.

In fact Apple uses the right logic when we use another formula. If we use =B1 B2+B3+B4B5 and any cell contains text string that we'll have error result the sam as in Microsoft Excel. So in this example Numbers behaves correctly.

So why SUM (B1:B5) can provide totally different result as =B1 B2+B3+B4B5?

iMac, Mac OS X (10.5.5)

Posted on Oct 11, 2008 3:00 PM

Reply

Answer 1

KOENIG Yvan

Level 8

41,819 points

Oct 12, 2008 3:14 AM in response to alexb2

Nothing strange.
You are the "culprit". 😉
You are comparing two fully different functions.

=A1 B1C1 accepts only numerical values so, if one of the passed arguments is not a numerical value, it logically send an error.

SUM is described by:

+The SUM function calculates the sum of a set of numbers. The numbers can be in individual cells, in ranges of cells, or included directly as arguments to the function.+
+SUM(number, [number, . . .])+
+number: One or more numbers, numeric expressions, or references to a cell or range of cells containing numbers or numeric expressions.+

If cells in the range doesn't contain numeric value, this is not an error. The function is designed to skip them.

If you are not sure that your entered values are numerical ones, it's your duty to check that they are.

As your values are stored in column B, in column C use the formula =B*1
You will get a number when the B cell contains a numerical value, a red triangle if the cell contains a string
You may refine with this formula:

=IF(ISBLANK(B),"",B*1)

which will return an empty string when the original cell is empty.
If you apply SUM on this column, you will get an error if an original value is not a numerical one.

Yvan KOENIG (from FRANCE dimanche 12 octobre 2008 12:13:02)

Reply

Answer 2

alexb2 Author

Level 1

8 points

Oct 12, 2008 3:45 AM in response to KOENIG Yvan

Sorry sorry but you are wrong! Your position is that it's my DUTY (???) to check but it's not so.

You say me "if you have some crap there SUM simply won't add it). Okay and if I have here some crap that is treated not as text string but as a number value. Than SUM will sum this crap with other numbers and I'll receive wrong result of the function.

So first of all right use of this function is to include ONLY those cells that REALLY should be SUMMURIZED. In this case if any of this cells goes wrong SUM should report an error.

We should not include not used cells into SUM range as it's very-very dangerous. They can provide unexpected results.

Another fact! I'd like not to explain you that sometimes when I edit cells for some UNKNOWN reason numbers become text strings. In your explanation I should recheck EVERY time? It's not right. I shouldn't. Imagine I have thousands of cells. How to recheck them? And those idea with B and C is not a clue. I want to sum directly not use another column just for checking. In fact you propose no NEVER work with direct data in cells but to create one another column. I work in the bank and NEVER met such approach. Just imagine how it's hard and how large tables become.

Once more let me explain. Yes SUM and =B1+B2 are different functions BUT they are used in different manners here is the difference. But it doesn't mean that error-processing should be different.

For example, you have table like described in my first message. If you use =B1+B2 than if you add ome more string to the middle the table you should manually change the formula to include this string. And if you use SUM than this string is automatically added!

One more example. You have table with 1000 strings that you want to sum. You really want to tell me that I should use =B1 B2+B3..........+B999B1000? Do you imagine somebody spending hours just to type such function? I'm even not sure it'll work as formulas may be limited in length.

So once more. =B1+B2 is the function when you want to add some separate numbers located in different places. SUM function is for adding numbers located together in some column, string (line) or matrix. SUM function is for data that contains ONLY numbers.

Just look - we NEVER need to include text strings inside SUM (). If we want to summarize some numbers that are separated by text strings than we'll have SUM(first massive)+SUM*(second massive) leaving text strings outside massives.

You you work with large massives of data you should know that it is the right way to work with sums. because you shouldn't include some not used cells into SUM() range.

Another question for you. Why then MS Excel reports error here? You can tell me " Excel is wrong". But Excel is standard and often we import tables from Excel to Numbers. So I should totally recheck everything after importing from Excel??? Or I should change formulas from SUM to =B1+B2 when I convert file from Excel to Numbers?

And the last, should I tell people "You know, with Excel you won't bother about such problems - Excel will report you. But with Numbers you should recheck manually or create manual check column!"

Don't you think everybody will answer "Well, I see Excel is more convenient as I have less chances to produces err-data with Excel".

And if you say that in Numbers SUM should work as you've described than I show you how dangerous it is. May be you can show why it's useful? Because now I don't see advantages of this way SUM works in Numbers but I see very very dangerous things here. I'd like software to be not so dangerous as I want my budget to be correct without constant rechecking. And I know there is MS Excel that is much easier and safe for such work.

I don't want to switch to Excel I simply want Numbers programmers to think how dangerous their SUM function is. But if Number programmers also think "It's my duty to check!" I'll swith to Excel because Microsoft programmers don't think it's my duty, they understand it's duty of software.

Reply

Answer 3

KOENIG Yvan

Level 8

41,819 points

Oct 12, 2008 4:17 AM in response to alexb2

alexb2 wrote:
Sorry sorry but you are wrong! Your position is that it's my DUTY (???) to check but it's not so.

You say me "if you have some crap there SUM simply won't add it). Okay and if I have here some crap that is treated not as text string but as a number value. Than SUM will sum this crap with other numbers and I'll receive wrong result of the function.

You refuse to understand

In B9, the formula is:
=SUM(B2:B7) which does exactly what it is designed to do: add the numerical values embedded in the range.

If some cells doesn't contain numerical values, _these values are simply skipped_ .

I'm not giving my advice of end user, I describe the way the function is designed and described.

So, I repeat, it's our duty to check that the values which we want to SUM are numerical ones.

If you don't agree, your only soluce is to leave Numbers which will not be changed only because you don't accept the definition of a function.

Maybe Excel designers made an other choice but Numbers 's one is perfectly logical and coherent.

We recently described the same kind of behaviour with the function PRODUCT().

As I often wrote, before using a function: look at its definition in the Help or in the PDF User Guide.

If you continue to disagree, you may post your advice thru _"Provide Numbers Feedback" in the "Numbers" menu_, describe what you wish.
Then, cross your fingers, and wait _at least_ for iWork'09 😉

A viable evolution would be to add a parameter asking SUM to send an error if there are non Numerical values in the range (same idea than the one used in VLOOKUP which may send an error when there is no exact match). But there is no valid reason to change the default behavior.

Yvan KOENIG (from FRANCE dimanche 12 octobre 2008 13:17:10)

Reply

Answer 4

alexb2 Author

Level 1

8 points

Oct 12, 2008 5:24 AM in response to KOENIG Yvan

Just simply explain why Numbers behaviour is more logical than Excel behaviour.

Just compare.

Excel
===

Pluses:
- any mistake will have notification for the user, we are not in danger to produce err-data in financial and other reports.

Minuses:
- I don't see any minuses in this way Excel works. May be you know?

Numbers
======

Pluses:
- What are the pluses of this Numbers behaviour? I don't see ANY advantage for now. May be you know?

Minuses:
- As we see error are very possible because SUM may ignore some data in the range if smth goes wrong with the style.
- Numbers also incourages us to create SUM ranges containing not only numbers but other strings as well (why bother, it'll simply ignore strings). But if any number gets in the range it'll be calculated. That's dangerous.
- Excel way is standard for many people as Numbers is new app it's unlogical to change standard behaviour as many people do not know about different approach in Numbers they'll get incorrect results, send them to other people and a lot of problems may arise with incorrect data. People may loose money, reputation and so on.
- Numbers is not standard application and we should import Excel data often. As you should know many times import ends in incorrect styles. When you import some sheet that is numeric values in Excel in Numbers it is imported as string values. So you should recheck just every cell! If we know sometimes import generates incorrect styles it's even more dangerous to use such behaviour of SUM function.

So I'd like to ask where is logic of SUM function working such way? I don't ask about how it's documented. I just ask where is logic for working so. Where this logic is better then Excel SUM's logic.

So, to end this discussion I'd like to ask. Just imagine my situation.

1) I often import Excel data and some cells become text strings for unknown reason.
2) Sometimes I work with cells that are numbers but after some editing I notice they become string numbers. So I can't be sure all my cells are Ok. I don't know why they go wrong but I do not behave wrong as I had no such problems with Excel.
3) I have some tables that should be summed. I want to sum them and receive correct data. When you now p.1 and p.2 of this list what can you recommend me as the most easy way to have my task solved. You've recommended to create another column for checking but it's very hard to create such columns for this data and tables are increasing almost twice. I want some more elegant way.

BTW, if we can't find an elegan way for this very simple task (ad I think my task is not something rare used) than the verdict is "Numbers is not well built in this ares, Excel is better".

The last thought. I'm against creating another version of SUM function or some additional operator for checkign as you've proposed. Why? Just because when we import from Excel or another software we need functions to work the same. And with your proposition we should manually change formulas after importing from Excel to have the same style of work as in Excel. I'd like to propose another solution. Let standard functions as SUM work as in Excel and other soft. And if Apple wants to add another variant of this function than Apple should crreate it as another function or as special option of the standard function.

P.S. Of course I sent feedback to Apple as you've recommened.

Reply

Answer 5

KOENIG Yvan

Level 8

41,819 points

Oct 12, 2008 5:47 AM in response to alexb2

alexb2 wrote:

- As we see error are very possible because SUM may ignore some data in the range if smth goes wrong with the style.
- Numbers also incourages us to create SUM ranges containing not only numbers but other strings as well (why bother, it'll simply ignore strings). But if any number gets in the range it'll be calculated. That's dangerous.

Numbers encourages nothing. It just allow us to do some things.

- Excel way is standard for many people as Numbers is new app it's unlogical to change standard behaviour as many people do not know about different approach in Numbers they'll get incorrect results, send them to other people and a lot of problems may arise with incorrect data. People may loose money, reputation and so on.

A standard application is not an application used by many users, its one which is defined as such by an authorized structure.
As far as I know, there are standard file formats, there are no standard applications.

- Numbers is not standard application and we should import Excel data often. As you should know many times import ends in incorrect styles. When you import some sheet that is numeric values in Excel in Numbers it is imported as string values.

As far as I know, if it is imported as a string it's because it's the result of a formula which Numbers can't translate. As there are around 500 functions() in Excell and 156 in Numbers, it's not surprising. In such a case there is a warning.
It's our duty to take care of this warning.

So I'd like to ask where is logic of SUM function working such way? I don't ask about how it's documented. I just ask where is logic for working so. Where this logic is better then Excel SUM's logic.

I didn't wrote than the Numbers's logic is better than the Excel's one and as far as I know no one is better than the other. They are different.

So, to end this discussion I'd like to ask. Just imagine my situation.

1) I often import Excel data and some cells become text strings for unknown reason.

To be short, no one forced you to use Numbers.

I never uses µSoft products, you are perfectly free to never use Numbers !
If I was often forced to use .xls files, mainly for professional usage, _I would not use Numbers_ . I would look at openOffice or neoOffice.

Yvan KOENIG (from FRANCE dimanche 12 octobre 2008 14:42:45)

Reply

Answer 6

alexb2 Author

Level 1

8 points

Oct 12, 2008 5:47 AM in response to KOENIG Yvan

The simpliest way is to respond "Don't use if you like". But that's not a solution. If app is wrong than it's correct to say "Yes, I love it but it's wrong this way".

And I'm sure you know what I mean when I call Excel smth that is standard.

By the way, I found a small solution. It's alignment. Numbers can align text to the left and numbers to the right with a special alignment option. With this way I see when some number becomes text string. Although it's not ideal and not applicable to very large tables...

Reply

Answer 7

alexb2 Author

Level 1

8 points

Oct 12, 2008 5:53 AM in response to KOENIG Yvan

By the way, Numbers already has in settings checkbox to show empty cells inside formulas "Show warnings when formulas reference empty cells".

It proves that Apple understands it's may be not Ok when formula addresses empty cell. Logically cell with text string is not better than empty cell so if Apple includes checkbox for empty cell the non-existing checkbox for incorrect data in cells is not Apple's logic but simply the problem of version 1.0.2.

Reply

Answer 8

KOENIG Yvan

Level 8

41,819 points

Oct 12, 2008 6:24 AM in response to alexb2

alexb2 wrote:
The simpliest way is to respond "Don't use if you like".

Why
I worked during 30 years as a potter.
I tested several tools. I kept those fitting my needs but dropped those which didn't.

But that's not a solution. If app is wrong than it's correct to say "Yes, I love it but it's wrong this way".

At this time you are just able to write "Numbers doesn't behave as I wish it do"
This doesn't prove that it is wrong. From my end user's point of view it behaves flawlessly (at least on the discussed point)

And I'm sure you know what I mean when I call Excel smth that is standard.

Of course I know but I don't agree with this "kind of definition". A standard is something which is well defined. I repeat that it's not because a program is widely used that it is a standard.
Ask the ISO to define a standard spredsheet and, if they do, I will take care of this definition.

At this time there is not such item and Apple is perfectly free to define the SUM() function as they do.

By the way, I found a small solution. It's alignment. Numbers can align text to the left and numbers to the right with a special alignment option. With this way I see when some number becomes text string. Although it's not ideal and not applicable to very large tables...

I feel that you are playing with fire.

You import a sheet knowing that some cells are no longer containing formulas but fixed values.
If for some reason, you change one value which is, in the original sheet, used by a not importedformula, your results would be wrong.
I think that this one is the real problem.
And against this one you can't do nothing.

At this time, I'm just wondering what may make an entry like '12 USD' behaving different ways. I asked the English Numbers to display every currency it is aware of, USD is unavailable. It is able to accept: $, USN and USS. So, every cell containing a numerical value completed by " USD" is always treated as a string 😉

Maybe you may get rid of your problem with a simple Find & Replace call.

Advanced Mode
Find: " USD"
check: Match Case
check: Whole words (apply these setting to drop words like "Usdertyuio")
Replace: "$"
Replace all

I assumes that it would be safer than the well known alignment tip (which was already available in the first ClarisWorks in 1990.

Yvan KOENIG (from FRANCE dimanche 12 octobre 2008 15:23:19)

Reply

Answer 9

alexb2 Author

Level 1

8 points

Oct 12, 2008 1:11 PM in response to KOENIG Yvan

Well in fact I don't have problem with formulas imported as fixed value. At least I don't remember such problem for a long time.

The problem is my numbers can be imported as string values or even can become string values. after some editing. I don't know why. May be its connected with different localizations of Excel and
Numbers, with '.' or ',' in number format or with smth else.

Well I mean not USD of course 🙂 My example was in USD simply to be clear. My real tables use national currency that can't be even shown with latin font 🙂 🙂 🙂 When I use USD in my tables it looks like US$. I'm not very glad with this US$ although.

So when I set format for my number as my local currency it's displayed as 123 XXX (XXX is my currency but it's not in latin). I see it's not only displayed but also stored as 123 XXX. That's different from Excel that stores only numbers in cells and shows number with prefix or suffix - Numbers storex number with prefix or suffix.

So may be at some moment my 123 XXX is treated like string value.

Yes, find and replace is a nice tool when importing data - thanks for the tip. the last problem is with already Numbers files that were imported not today (or even createde as Numbers file) where some cell becomes string value after some editing. I had several times this situation and last time I found error only because I have my budget in 2 programs - in Numbers and in another special budgeting program. As each program has advantages I have in fact 2 instances of the same budget.

And when I changed some string in Numbers and another program I noticed the sum is different. I was sure it's another program somewhere buggy but noticed than Numbers simply ignores one of the budget strings and sums without it... In fact it was even not the string I changed, I remember I changed the buggy string about a week ago but I haven't compared budgets that time so my budget had a mistake for some time till I noticed it. If I used only Numbers without copy of budget in another program may be even today I won't notice the mistake.

The same situation happened not once.

Reply

Answer 10

KOENIG Yvan

Level 8

41,819 points

Oct 13, 2008 4:27 AM in response to alexb2

alexb2 wrote:
So when I set format for my number as my local currency it's displayed as 123 XXX (XXX is my currency but it's not in latin). I see it's not only displayed but also stored as 123 XXX. That's different from Excel that stores only numbers in cells and shows number with prefix or suffix - Numbers storex number with prefix or suffix.

No, you get this behavior because the original sheet is displaying values as currencies. I assumes that the import process pass it as a string because he doesn't recognize it as a currency value. As far as I know, Numbers stores the currency entries as a pure number and an indicator of the used currency.
Here is an example.

(a) the description of the numerical value (I underlined it: 456)
<sf:number-cell sf:flags="4" sf:value=" 456" sf:col="1" sf:row="2">
<sf:cell-style-ref sfa:IDREF="SFTCellStyle-46"/>
<sf:content-size sfa:w="58.659881591796875" sfa:h="14"/>
</sf:number-cell>
(b) the description of the format (I underlined the used currency: XAM)
<sf:cell-style sfa:ID="SFTCellStyle-46" sf:cell-style-default-line-height="12" sf:parent-ident="tabular-Basic-body-cell-style-id">
<sf:property-map>
<sf:SFTCellStylePropertyNumberFormat>
<sf:number-format sfa:ID="SFTNumberFormat-23" sf:format-type="1" sf:format-string="#,##0.00 ¤;-#,##0.00 ¤" sf:format-decimal-places="2" sf:format-currency-code=" XAM" sf:format-negative-style="0" sf:format-show-thousands-separator="true" sf:format-fraction-accuracy="-3" sf:format-use-accounting-style="false"/>
</sf:SFTCellStylePropertyNumberFormat>
<sf:SFTCellStylePropertyImplicitFormatType>
<sf:number sfa:number="256" sfa:type="i"/>
</sf:SFTCellStylePropertyImplicitFormatType>
<sf:SFTCellStylePropertyFormatType>
<sf:number sfa:number="257" sfa:type="i"/>
</sf:SFTCellStylePropertyFormatType>
</sf:property-map>
</sf:cell-style>

Of course, if the original currency string is not recognized, it can't be described as such a feature and the value is treated as a simple string.

So may be at some moment my 123 XXX is treated like string value.

Yes, find and replace is a nice tool when importing data - thanks for the tip. the last problem is with already Numbers files that were imported not today (or even createde as Numbers file) where some cell becomes string value after some editing.

Here again, if you edit a currency string and replace it to a not recognized one, the result will be logically a string.

There is an other way to give this result:
copy a value from a cell formatted as XAM currency (just an example)
paste it in a cell whose format is currency Euro (once again it's just an example).
The result will be a string.

The culprit is not the program, it's the user.

I had several times this situation and last time I found error only because I have my budget in 2 programs - in Numbers and in another special budgeting program. As each program has advantages I have in fact 2 instances of the same budget.

And when I changed some string in Numbers and another program I noticed the sum is different. I was sure it's another program somewhere buggy but noticed than Numbers simply ignores one of the budget strings and sums without it... In fact it was even not the string I changed, I remember I changed the buggy string about a week ago but I haven't compared budgets that time so my budget had a mistake for some time till I noticed it. If I used only Numbers without copy of budget in another program may be even today I won't notice the mistake.

The same situation happened not once.

I think that the choice made by Apple for SUM() is correct. You feel that it is wrong. It's useless to debate more, we will not change the behavior of the function.
I try to give a workaround.
The neater one is to add columns as I described in my first response.
The given formula will clearly flag cells which aren't containing numerical values. If you move the SUM from the original range to the new one, the oddities will be flagged automatically.

Here is an alternate workaround:

The currency is XAM.

In B4 I introduced a typo so the value is no longer a numerical one.
Given that, the sum in the footer doesn't count the 34 (which is the normal behavior).
In column C1 I entered =1*B
which flagged the bad cell.

The alternate is to use an auxiliary table like aux1
In A1 I entered:
=IF(ISERROR(INDIRECT(ADDRESS(ROW(),COLUMN(),,,"main"))),"",IF(ISBLANK(INDIRECT(A DDRESS(ROW(),COLUMN(),,,"main"))),"",1*INDIRECT(ADDRESS(ROW(),COLUMN(),,,"main") )))
Exactly the same formula in B1, C1, D1, …
So, the table will automatically grab the contents of the main table (just take care to insert more columns in aux1 than in main)
In the footer row of aux1, insert a sum formula for columns where there is required.

Looking at this footer will flag columns where a value may be "wrong" so, it will be necessary (but easy) to scan the column's content to find the red triangle(s).

Yvan KOENIG (from FRANCE lundi 13 octobre 2008 13:27:40)

Reply

Answer 11

Oct 13, 2008 5:28 AM in response to alexb2

It baffles me how long this post went on...

Yvan is absolutely right. The point to derive from all of this is that Numbers is the way Numbers is and you will not change it. You state that it is 'wrong', yet you didn't design it.

If numbers performed as you wish (SUM would only work if the cells in the range were all numerical entries), someone else could have posted that Numbers was wrong and that SUM should allow people to SUM a range of cells with text and numerical entries, only extracting the numerical cells for the result.

Many users = many variations on how each wished Numbers would perform according to their needs. Yvan is right - Apple chose the path it chose for the SUM function. It is incorrect in the fullest to state that they are 'wrong' when they have laid out the definition of a formula and then followed that definition. In programming, developers make a design choice and then stick with it.

Yvan is also correct that any chance you have to change things lies in providing feedback. Restating your point over and over in here will not change things. Either provide some feedback or just use the darn program how it's designed to be used... or use Excel 😉

-John

Reply

Answer 12

KOENIG Yvan

Level 8

41,819 points

Oct 13, 2008 7:15 AM in response to John Hendy

Thank's John.
I was quite tired with this thread … but I kept my nerves 😉
From my point of view, the only positive point is that some readers may have learnt some details about the behavior of currency formatted cells.

Yvan KOENIG (from FRANCE lundi 13 octobre 2008 16:14:58)

Reply

Answer 13

alexb2 Author

Level 1

8 points

Oct 13, 2008 11:24 AM in response to KOENIG Yvan

Well may be it's with currencies. If you mean Excel and Numbers for some currencies may be incompatible and result in such behaviour that everything becomes clear.

Reply

Answer 14

KOENIG Yvan

Level 8

41,819 points

Oct 13, 2008 12:34 PM in response to alexb2

There are two things:

(a) the fact that it seems that the two programs doesn't use the same naming for some currencies.

(b) the way the SUM() function behaves which, from my point of view is perfectly correct (no less, no more than the Excel's behavior).

I missed the message containing that:

It proves that Apple understands it's may be not Ok when formula addresses empty cell. Logically cell with text string is not better than empty cell so if Apple includes checkbox for empty cell the non-existing checkbox for incorrect data in cells is not Apple's logic but simply the problem of version 1.0.2.

Not at all. As far as I know, flagging empty cells is useful to highlight the difference between an _empty cell_ and a _cell containing an empty string_ which are displayed the same way but are _two completely different objects_ .

If =B refers to an empty cell, the result will be 0
If =B refers to a cell containing an empty string, the result will be an empty string.

Yvan KOENIG (from FRANCE lundi 13 octobre 2008 21:34:02)

Reply