I'm following some instructions that say "Round the result to two decimal places based on value in 3rd decimal place." I've used the Round() function before, but how do I control rounding based on the value in the 3rd decimal place? All I can think of is to first truncate the number to 3 decimal places and then round from that. But is there a better solution?
1 Answer
To round to n decimals always means the n+1'th digit determines the rounding. If the n+1'th digit is < 5 the number is simply truncated after n digits. If its >= 5 you truncate at n decimals and add 10^-n.
This is the typical "round to the nearest" significant digit method with the typical "round half up" tie breaking rule.
TL;DR
The instructions you are following are supplying superfluous information. It is simply asking to round to 2 decimals
=ROUND(A1, 2)
-
1"means the n+1'th digit determines the rounding" - Thanks, never knew that. I always thought you were supposed to go to the right end of the number and work your way back to the left. But I guess that wouldn't make any sense, such as with something ending in .666666..... Commented Mar 26, 2022 at 23:21
-
Don't feel bad ;) Rounding is a lot more complex than I knew as well. If you are really bored or suffer from insomnia, have an detailed read here en.wikipedia.org/wiki/Rounding. There are various formulae, tie breaking rules and computer implementations - some being more easier/faster to implement on a computer than others but then less useful or intuitive. There's even a case where an index fund on some USA stock exchange underperformed by ~50% because of daily rounding errors. Commented Mar 27, 2022 at 8:52
=ROUND(A1,2)
will do it. Anything in the 3rd position that is 5 or above will round up and anything in the third less than 5 will round down.