Spreadsheet danger of right justify numbers

[ItDontMeanAThing]

I currently use LibreOffice. Maybe this potential 'gotcha' exists in other spreadsheet apps.

For reasons I've long since forgotten, I formatted a year summary template with right justified number columns. That's unnecessary. Every spread sheet app I've used defaulted to right justification of numbers.

Somehow, I input a number preceded by a single quote. That converts a number into a character string. Right justification meant it looked like a number, but the column sum formula ignored it. A non-zero value in my double checking formulas alerted me to the error.

This error was small. None of the other year summaries had numbers accidentally converted to character strings. I'll check the rest of my templates tomorrow.

 

[rayinpenn]

Excel has a function to convert text to numbers... Often files imported from mainframes are imported as text. It is annoyingly common.


Sent from my iPad using Early Retirement Forum.

 

[ERD50]

That seems odd - but I tried it, and I think you found a bug. I have not searched to see if it has been reported yet. Look at my attached screenshots - a calculation on the entry ['1] sees it as a number and adds correctly, but when I include it as a SUM function, it is ignored.

The image on the left is what you normally see. On the right, I pressed CNTRL + ` - (the non-shifted tilde key), which displays the formulas. The actual entries are the second column, and it does not display the single quote prefix in either, that why I added the " '1 " comment to the left of the actual entry cell.

-ERD50

 

[Animorph]

Yep, Excel does the same thing. Not a bug exactly, since it is summing text and numbers according to its conventions. But something to be aware of.

 

[ERD50]

Yep, Excel does the same thing. Not a bug exactly, since it is summing text and numbers according to its conventions. But something to be aware of.

But it does seem like a bug that a "+" operation gives a different result from a "SUM" operation?

-ERD50

 

[target2019]

Look for flag warnings about this. Excel is pretty good on this point.

 

[ItDontMeanAThing]

But it does seem like a bug that a "+" operation gives a different result from a "SUM" operation?

-ERD50

Arrgh. It may be yet another aspect of the curse of the BASIC programming language, which both Excel and LibreOfffice use as a scripting language. It's been too many years to be certain, but I remember BASIC converted to numbers anything that could be a number. However, I may be getting this confused with Visual Basic converting any number that could be a date into a date.

> CNTRL + ` - (the non-shifted tilde key), which displays the formulas.
Thanks! I had no idea that feature existed.

 

[Animorph]

I think SUM() needs to be more flexible than "+" because it is more likely to include text within its area of operation. "+" is more specific, one cell at a time. I have certainly used it that way. For example, you can sum an entire column with SUM(B:B) and not worry about column titles causing a problem. "+" gives an error if you add text that can't be interpreted as a number. Probably just a compromise between competing complications.

 

[ItDontMeanAThing]

If SUM() gave an error when the values summed included character strings there would be two side effects. First is spreadsheet jockeys would be slightly less efficient because they'd have to define the range summed. Second said jockeys would be alerted to unintentional strings in their data.

I expect early in the history of spreadsheet apps, someone decided efficiency was more important than accuracy. Probably because (with tongue in cheek) the nerds thought 'it's a spreadsheet. Why would anyone put strings in a column of numbers? At least the LibreOffice documentation gets it right: "Adds all the numbers in a range of cells."

I was a software developer who worked on in-house apps. Your w*rk history was blessed if you never saw a room full of dread like that after the manager announced to a development team the 'database' we have to convert is a set of spreadsheets created by someone with no accounting, finance or programming training (he was an Engineer doing Cost Accounting). And that we are taking over the 'database' because the results stopped making sense. And the discrepancies are a major concern of the CFO.

 

[ERD50]

I think SUM() needs to be more flexible than "+" because it is more likely to include text within its area of operation. "+" is more specific, one cell at a time. I have certainly used it that way. For example, you can sum an entire column with SUM(B:B) and not worry about column titles causing a problem. "+" gives an error if you add text that can't be interpreted as a number. Probably just a compromise between competing complications.

I agree with your first observation (and that fits withItDontMeanAThing's response as well). It is handy that SUM ignores text - you can sum a range and leave your headings and comments in place.

But... " "+" gives an error if you add text that can't be interpreted as a number" isn't consistent, so I might still consider that a bug.

You are correct, if I enter "qwerty" into cell A1, and create a formula in cell B1 that is "=A1+99", I get an error (#VALUE! in LibreOffice). If I entered a numeric "1", I'd get a numeric "100". So that's fine - it doesn't calculate with a formula, and it doesn't calculate in the SUM function.

But, when I enter a single quote with a number (to left justify it), (and I'll use brackets here to avoid confusion with single/double quotes) like [ '1 ] - the calculation works fine, but the SUM function ignores it.

So that seems inconsistent to me - If SUM function ignores it, a calculation should throw an error as well? Or is there something I'm not considering (I keep my spreadsheets fairly basic, when I can)?

-ERD50

 

[ItDontMeanAThing]

So that seems inconsistent to me - If SUM function ignores it, a calculation should throw an error as well? Or is there something I'm not considering (I keep my spreadsheets fairly basic, when I can)?

-ERD50

It is inconsistent if one assumes math functions and operators should work only on numbers and throw errors otherwise. But, as Animorph pointed out in #4, it's a feature not a bug, one that probably dates back to Lotus 123. Trying to sell a spreadsheet that isn't backward compatible to all but the most obscure prior spreadsheets is a good way to shoot yourself in the foot, so the feature lives on. We have to deal with it.

LibreOffice has a ISNUMBER() function which returns FALSE on a cell value of [ '1 ]. Now all I have to do is figure out how to include it in custom numeric formats of the ones I use with the added feature of changing a cell attribute (eg font size, background color) for non-numbers.

 

[target2019]

It is inconsistent if one assumes math functions and operators should work only on numbers and throw errors otherwise. But, as
LibreOffice has a ISNUMBER() function which returns FALSE on a cell value of [ '1 ]. Now all I have to do is figure out how to include it in custom numeric formats of the ones I use with the added feature of changing a cell attribute (eg font size, background color) for non-numbers.

Conditional formatting on cell itself? Would be possible with Excel.

With the formula above, I'd add a column and insert formula. This would take care of the problem until you find the best way.

For Excel problems I usually read about similar solutions on formulas. I suspect your problem has been solved in many ways.

 

[bingybear]

use data validation
https://help.libreoffice.org/Calc/Validity_of_Cell_Contents

Limiting Entries to Numeric Values (Microsoft Excel)

this should allow you to also check for values too large or small... but won't catch all typos