0

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?

Improve this question
3
  • 3
    just =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.
    – Scott Craner
    Commented Mar 25, 2022 at 16:58
  • Rounding automatically does this; the only "hard case" is when the value of the digit after the last rounding position (in this case, the value of the third decimal digit) is 5 - then you need to know whether your ROUND() function rounds up, down, even, or odd. (Rounding even or odd means to round to the required decimal position being even or odd - that is, rounding .35 odd would round to .3; rounding it to even would round to .4)
    – Jeff Zeitlin
    Commented Mar 25, 2022 at 16:59
  • I checked the documentation for Excel's ROUND() and it doesn't say whether it rounds even or odd. There are separate functions for rounding up or down. I think the instructions I'm following mean anything after the 3rd digit should be ignored. So would rounding odd or even still be relevant?
    – user348514
    Commented Mar 25, 2022 at 21:39

1 Answer 1

Reset to default
0

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)
Improve this answer
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.....
    – user348514
    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.
    – ExcelEverything
    Commented Mar 27, 2022 at 8:52

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .