Ask Your Question

Rounding down to nearest hundred

asked 2021-02-11 11:34:10 +0200

Bill Derr gravatar image

I have a value in E2 of 570. I need (in L2) to have this number rounded down to the nearest hundred, so 500 - the value in E2 changes so I need the rounding function to reference the cell E2 rather than the number it contains.

I tried adding E2 to the round down function but with no luck. Any suggestions? Thanks.

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2021-02-11 11:38:15 +0200

Opaque gravatar image


=ROUNDDOWN(E2/100;0)*100 should do it.

edit flag offensive delete link more


That works superbly - many thanks.

Bill Derr gravatar imageBill Derr ( 2021-02-11 11:42:51 +0200 )edit

I would just like to add how good this place is - I asked my question and got an answer in just four minutes!

Bill Derr gravatar imageBill Derr ( 2021-02-11 11:45:09 +0200 )edit

Two additional hints:
-1- ROUNDDOWN() will interpret "down" as "towards 0 (zero)". Relevant for negative numbers!
-2- The second parameter can also be a negative integer.
(-2- is to tell that =ROUNDDOWN(E2;-2) would do the trick without additional calculations.)

Lupp gravatar imageLupp ( 2021-02-11 12:09:02 +0200 )edit

answered 2021-02-11 12:07:16 +0200

keme gravatar image

updated 2021-02-11 12:08:08 +0200

Divide/round/multiply is the "old school" which works on every spreadsheet app in the known universe. Traditionally, only the INT() function was available for this, so you would use =INT(E2/100)*100. This still works, and gives maximum portability.

With the added rounding functions available in modern spreadsheet apps, you can write it somewhat simpler, which improves readability (easier to understand what you meant to do, when you revisit your formulas in 5 years time or so).

You can use:

  • =FLOOR(E2;100)The second parameter is the factor to round to. You want "nearest hundred".
    There is an optional third parameter to adjust behavior for negative numbers - round down or round towards zero.
  • =ROUNDDOWN(E2;-2) The second parameter is "number of decimals". A "negative decimal count" can be used to round to factors of ten, so -2 returns nearest 100.
edit flag offensive delete link more



Old fashioned rounding would e.g. avoid misunderstandings caused by "hidden spec". (See my remark "-1-" in my comment to the answer by @Opaque.) It gives the full power to the user - but also the full responsibility, of course. That's what I basically would prefer.
Read "typical" questions concerning "financial functions" and you will join my pessimistic attidude insofar. Things we have to cope here with must not be ruled by the famous "market forces", but they are.
(And we are on the way to again subject teaching to the same forces, Click here ... Hit this key then... That's 12th in the ribbon..., Watch this...on youtube then to "understand" it even better.)

Lupp gravatar imageLupp ( 2021-02-11 12:18:34 +0200 )edit

answered 2021-02-11 13:59:36 +0200

newbie-02 gravatar image

hello @Bill Derr:

calc's round functions are capable to round to 'negative amount of decimals', will say 'make n digits before the decimal point to zero', e.g. =ROUNDDOWN(12345,6789;-2) will result in '12300', =ROUNDUP(12345,6789;-2) in '12400' and =ROUNDDOWN(E2; -2) could make it for you,

limits: -/+ 20 decimals,

just be aware that there are! small flaws in runddown (and roundup), see: - not fully fixed - and - new -,
imho they won't affect your results, at least not the displayed string, but be careful ...
'round' is better but i'm not finally sure if perfect after last patches by @erAck,



P.S. 'solved marks' and 'likes' welcome,
click the grey circled hook - ✓ - top left to the answer to turn it green if the problem is solved,
click the "^" above it if you 'like' the answer,
"v" if you don't,
do not! use 'answer' to add info to your question, either edit the question or add a comment,
'answer' only if you found a solution yourself ...

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2021-02-11 11:34:10 +0200

Seen: 45 times

Last updated: Feb 11