Join Early Retirement Today
Reply
 
Thread Tools Search this Thread Display Modes
Spreadsheet danger of right justify numbers
Old 01-17-2016, 10:18 AM   #1
Full time employment: Posting here.
 
Join Date: Jun 2008
Location: Hua Hin, Thailand
Posts: 523
Spreadsheet danger of right justify numbers

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.
__________________

__________________
ER Oct 2008 at age 54. An expat mostly settled in Thailand.
ItDontMeanAThing is offline   Reply With Quote
Join the #1 Early Retirement and Financial Independence Forum Today - It's Totally Free!

Are you planning to be financially independent as early as possible so you can live life on your own terms? Discuss successful investing strategies, asset allocation models, tax strategies and other related topics in our online forum community. Our members range from young folks just starting their journey to financial independence, military retirees and even multimillionaires. No matter where you fit in you'll find that Early-Retirement.org is a great community to join. Best of all it's totally FREE!

You are currently viewing our boards as a guest so you have limited access to our community. Please take the time to register and you will gain a lot of great new features including; the ability to participate in discussions, network with our members, see fewer ads, upload photographs, create a retirement blog, send private messages and so much, much more!

Old 01-17-2016, 11:18 AM   #2
Thinks s/he gets paid by the post
 
Join Date: May 2014
Posts: 1,049
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.
__________________

__________________
rayinpenn is offline   Reply With Quote
Old 01-17-2016, 11:24 AM   #3
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: Sep 2005
Location: Northern IL
Posts: 18,278
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
Attached Images
File Type: png LibreOffice entries - bug?.png (6.4 KB, 18 views)
File Type: png LibreOffice formulas - bug?.png (10.0 KB, 18 views)
__________________
ERD50 is offline   Reply With Quote
Old 01-17-2016, 02:13 PM   #4
Thinks s/he gets paid by the post
 
Join Date: Jul 2005
Posts: 3,862
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.
__________________
Animorph is offline   Reply With Quote
Old 01-17-2016, 02:25 PM   #5
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: Sep 2005
Location: Northern IL
Posts: 18,278
Quote:
Originally Posted by Animorph View Post
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
__________________
ERD50 is offline   Reply With Quote
Old 01-17-2016, 02:37 PM   #6
Thinks s/he gets paid by the post
target2019's Avatar
 
Join Date: Dec 2008
Posts: 3,705
Look for flag warnings about this. Excel is pretty good on this point.
__________________
target2019 is offline   Reply With Quote
Old 01-17-2016, 05:58 PM   #7
Full time employment: Posting here.
 
Join Date: Jun 2008
Location: Hua Hin, Thailand
Posts: 523
Quote:
Originally Posted by ERD50 View Post
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.
__________________
ER Oct 2008 at age 54. An expat mostly settled in Thailand.
ItDontMeanAThing is offline   Reply With Quote
Old 01-17-2016, 06:34 PM   #8
Thinks s/he gets paid by the post
 
Join Date: Jul 2005
Posts: 3,862
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.
__________________
Animorph is offline   Reply With Quote
Old 01-17-2016, 07:10 PM   #9
Full time employment: Posting here.
 
Join Date: Jun 2008
Location: Hua Hin, Thailand
Posts: 523
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.
__________________
ER Oct 2008 at age 54. An expat mostly settled in Thailand.
ItDontMeanAThing is offline   Reply With Quote
Old 01-17-2016, 08:23 PM   #10
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: Sep 2005
Location: Northern IL
Posts: 18,278
Quote:
Originally Posted by Animorph View Post
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
__________________
ERD50 is offline   Reply With Quote
Old 01-17-2016, 09:58 PM   #11
Full time employment: Posting here.
 
Join Date: Jun 2008
Location: Hua Hin, Thailand
Posts: 523
Quote:
Originally Posted by ERD50 View Post
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.
__________________
ER Oct 2008 at age 54. An expat mostly settled in Thailand.
ItDontMeanAThing is offline   Reply With Quote
Old 01-18-2016, 06:02 AM   #12
Thinks s/he gets paid by the post
target2019's Avatar
 
Join Date: Dec 2008
Posts: 3,705
Quote:
Originally Posted by ItDontMeanAThing View Post
It is inconsistent if one assumes math functions and operators should work only on numbers and throw errors otherwise. But, as [URL="http://www.early-retirement.org/forums/members/animorph-5298.html"]
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.
__________________
target2019 is offline   Reply With Quote
Old 01-18-2016, 07:41 AM   #13
Thinks s/he gets paid by the post
 
Join Date: Dec 2014
Posts: 1,660
use data validation
https://help.libreoffice.org/Calc/Va..._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
__________________

__________________
bingybear is offline   Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
FA tries to justify 2% fees. nun FIRE and Money 89 01-29-2015 11:55 PM
Do these numbers look right? bulbar Hi, I am... 20 08-19-2014 11:11 AM
Tracking basis with spreadsheet, how to structure the spreadsheet? bamsphd FIRE and Money 13 08-03-2009 10:31 AM
Stephen Colbert gets it right: #1 danger calmloki Other topics 9 12-30-2008 04:39 PM
Need to justify your spending? ex_CFO_now_RVer Life after FIRE 39 03-25-2007 08:08 PM

 

 
All times are GMT -6. The time now is 02:51 AM.
 
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2017, vBulletin Solutions, Inc.