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.
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”.=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.
Old fashioned rounding would e.g. avoid misunderstandings caused by “hidden spec”. (See my remark “-1-” in my comment to the answer by @anon73440385.) It gives the full power to the user - but also the full responsibility, of course. That’s what I basically would prefer.
But…
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.)
Hello,
=ROUNDDOWN(E2/100;0)*100 should do it.
That works superbly - many thanks.
I would just like to add how good this place is - I asked my question and got an answer in just four minutes!
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.)
hello @BillDerr:
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:
https://bugs.documentfoundation.org/show_bug.cgi?id=124286 - not fully fixed - and
https://bugs.documentfoundation.org/show_bug.cgi?id=138220 - 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,
reg.
b.
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 …