- May 19, 2011
- 19,689
- 13,560
- 136
Before I start, I should point out that working with spreadsheets has never been my 'bread and butter' professionally (though I've used spreadsheets for professional purposes plenty of times), so to some these points may come as no surprise.
I don't have a newer version of Excel to test in, hence 2007 (SP3, fully up-to-date). I'm using LibreOffice 5.3.1.6.
I can't believe I haven't noticed this issue sooner, but anyway. Take a blank spreadsheet and put a currency value (such as $31.20, needs to have the second decimal place as a zero) into a cell, format it as currency so you can see the second decimal place.
Select another cell, then do Ctrl+F and type in 31.20. Not found. I understand why of course: Mathematically, 31.20 is 31.2, so Ctrl+F finds 31.2 no problem.
I just would have thought for usability reasons (say doing a series of comparisons between two sources of information) that a spreadsheet program would treat a search for a number in a similarly mathematical manner rather than a literal string search.
Out of curiosity, I tried something else, searching for $31.2. Excel throws an error saying I shouldn't have symbols in the search string (fair enough). LibreOffice just acts like it can't find what I'm searching for (LO doesn't throw an error, just no reaction to the search request, normal LO behaviour for 'search string not found').
I then went one further and made a simple formula: Adding the contents of two cells together, then searching for the resulting figure in Ctrl+F. No result from Excel, however I get a correct result in LO. For Excel I assume this is the result of the contents of the cells being searched (in this case, the formula is searched rather than the calculated result).
Can someone try all of this in a newer version of Excel and/or another spreadsheet program? I'm just curious.
I don't have a newer version of Excel to test in, hence 2007 (SP3, fully up-to-date). I'm using LibreOffice 5.3.1.6.
I can't believe I haven't noticed this issue sooner, but anyway. Take a blank spreadsheet and put a currency value (such as $31.20, needs to have the second decimal place as a zero) into a cell, format it as currency so you can see the second decimal place.
Select another cell, then do Ctrl+F and type in 31.20. Not found. I understand why of course: Mathematically, 31.20 is 31.2, so Ctrl+F finds 31.2 no problem.
I just would have thought for usability reasons (say doing a series of comparisons between two sources of information) that a spreadsheet program would treat a search for a number in a similarly mathematical manner rather than a literal string search.
Out of curiosity, I tried something else, searching for $31.2. Excel throws an error saying I shouldn't have symbols in the search string (fair enough). LibreOffice just acts like it can't find what I'm searching for (LO doesn't throw an error, just no reaction to the search request, normal LO behaviour for 'search string not found').
I then went one further and made a simple formula: Adding the contents of two cells together, then searching for the resulting figure in Ctrl+F. No result from Excel, however I get a correct result in LO. For Excel I assume this is the result of the contents of the cells being searched (in this case, the formula is searched rather than the calculated result).
Can someone try all of this in a newer version of Excel and/or another spreadsheet program? I'm just curious.
Facebook
Twitter