Rounding Errors

Lets suppose you have two numbers which are calculated from complicated formulas and they should both return the same value
Because of round-off errors the two values differ by a very small amount and subtracting one from the other may not give zero.
There may be some number in decimals (i.e. base 10) which cannot be written or displayed with an infinite precision such as 10/3.


To overcome the problem of rounding errors there are times when numbers will be exactly equal
It is perfectly adequate just to make sure that the difference between the two numbers is less than say an arbitrary small number (for example 0.01)


Nedd to include an example where the formatting only alters the display and not the actual values
You can change the actual value that is stored using any of the following functions:
ROUND


If you are using rounded values this can result is misleading and incorrect values being displayed
Consider a table of numbers that need to be added up:
one cell has = 1.006
another cell has = 3.007
Both these cells are displayed with rounding making then 1.01 and 3.01
If you add up these two values and apply similar rounding the result is 4.013 - which when rounded is 4.01
But on the sheet it looks like (1.01 + 3.01 = 4.01) which obviously looks wrong ???
Does it only apply to currency formats ??


You should be aware that converting decimals to binary can result in round off errors.


Same Precision As Displayed

(Tools > Options)(Calculation tab)
Changes all the underlying values in your worksheet to be the same as their displayed valued. This will change values permanently from having 15 significant figures to whatever format is displayed. This cannot be undone. (Advanced tab, When calculating this workbook)
This should be used with caution as it permanently changes the numbers on the worksheet/workbook -
Does the option apply to worksheets or workbooks ??



© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext