https://bugs.documentfoundation.org/show_bug.cgi?id=173053
--- Comment #12 from ady <[email protected]> --- (In reply to simon from comment #10) > Thanks Ady > The VALUE() didn't help in what I am trying to produce but it turns out that > I can evaluate the decimal places from a TEXT cell, which I didn't expect > and is a good result. > > So, TEXT works well and VALUE() can be used for maths that doesn't like > TEXT. Good; that is how it should be done (with further improvements). For instance, an automatic calculation that needs to consider the number of relevant decimal places would depend on the Text cell (e.g. with LEN()). > > I've added a real world example, which mostly works. I don't need VALUE() Since you are dealing with accuracy / tolerances / precision, I would suggest to use the VALUE() function anyway. Generally speaking, Calc might parse the Text values and automatically use them for numeric calculations, but this is not always an adequate approach for some functions. For instance, using the simple addition ("+") might work, but using SUM() might not (e.g. values with trailing zeros might be ignored). Example: in attachment 208070, worksheet "Tolerance Calcs", the range B10:B20 should add up to a total of 1147.05 (or 1147.050 if you want), but since you have "107.350" and "102.250", then the total result is 937.45 using SUM(). Similarly, the AVERAGE() function will also be incorrect. It does not matter whether this specific functions are needed in this case; it is just a matter of being used to the methodology, to be on the safe side no matter the calculation. So, you should rather use VALUE() and the adequate (display) Cell Format. Then the columns showing "Decimal places" should rather depend on the cells located on the "CSV Read in as TEXT" worksheet. The "reverse" of VALUE() would be something like '=TEXT(B11;"000")' in order to show the number "107.35" located in cell B11 as "107.350" on another cell (as Text). > for the calcs so far. I thought you might like to see what it is that I've > been going on about, basically it's the evaluation of physically > measurements against the design tolerances with are either the Default (D) > drawing top corner stand or stated per measurement by dimension limits. > > I have found what I believe to be a rounding error, I'm not sure if it's the > internal TEXT to number conversion or something else, but changing the TEXT > using VALUE() doesn't resolve it. I've duplicated the sheet to both > approaches are tested. > > The IF() statement and the associated maths produce a wrong result in one > case and it doesn't matter whether I calculate using the TEXT cells or the > VALUE() cells. The error is tiny, 15 decimal places, but enough to make a < > in an IF statement go wrong. It's with BORE 3, the Calculation should be 0, > if I change the tolerance ( BORE 4) from -0.4 to -0.400000000001 then it > works. > > IF -0.4 < -0.4 should be FALSE and -0.4 - -0.4 should be 0 . In the case > of BORE 3, it isn't. This is, again, the issue of Accuracy (see link in comment 1). Since you are dealing with tolerances, you should also use the rounding functions according to your needs (e.g. ROUND, ROUNDUP, ROUNDDOWN, CEILING.MATH, FLOOR.PRECISE...; please always be careful with INT(), because it is not what people think it is). Please note that these are actual calculations, not just a display format; you should always be aware of both aspects (and the width of the cells). > > At least I've proved the concept, I might need to add a function to ignore > errors that are outside of the decimal places that the tolerances are to so > any error values smaller that 0.0009 can be ignored. If I can prove the > cause of the error, I will raise a bug report. As mentioned, no bug report needed; it is just the Accuracy limitations of most spreadsheet tools (see comment 1). > > Thanks for the Help Hopefully these somewhat off-topic comments can help explain why the request for the "setting" to use numeric values "as-is" is not such a simple task as adding one checkbox when importing data, and how to overcome the issue when dealing with spreadsheet tools. -- You are receiving this mail because: You are the assignee for the bug.
