Making a number end to the nearest 7 (no decimals)

Question

I have found quite a lot of answer about rounding decimals, however I need to sort out a simple formula for LibreOffice Calc that allows me to draw up estimates ending to the nearest 7 so for example if the quote is 1372 it should be rounded down to 1367 while if it is 1375 becomes 1377 what would be a really simply formula that does not involve coding or macros?

Answer 1

As for now the solution I found is this one:

=(ROUND(I25/10)+0,7)*10

The problem with this is that does not round to the nearest 7 but to 7 so for example 362,00 becomes 367,00 and not 357,00 as intended.

Edit: this resolve the issue above, hope this helps:

=(ROUND((I25-10)/10)+0,7)*10

Removing 10 from the total I25 due to the ROUND function will correct the result, so for example 362 becomes 35,2 rounded to 35 + 0.7 we get 35.7 and finally 357 as intended. While for upper values, say 365 rounding 35.5 gives us 36 + 0.7 we get 367,00 again to nearest 7 units as intended!

Answer 2

You could use the default ROUND() function while "shifting" the values by 3:

=ROUND(A1+3;-1)-3

This gives the following results:

In other words: add your offset "3" to the initial value, ROUND() it to the nearest multiple of ten, and subtract the offset again.

But this will round 1372 to 1377, since it's a "shifted" 1375 which will round up to 1380 (see Matthew Strawbridge's comment to the question).