舍入到最接近的N数

I have a set of decimal numbers, and I need to round them to the nearest number ending in 7.

我有一组十进制数,我需要将它们舍入到最接近7的最接近的数字。

Ex:

0.45 --> 0.47
4.70 --> 4.67
6.49 --> 6.47
8.32 --> 8.37

I can use either Excel or Access for this (basically VBS).

我可以使用Excel或Access(基本上是VBS)。

I can live with just grabbing the 2 decimal places, converting that to an integer (*100), then applying the rounding rules and then pre-pending the integer value of the original value.

我只能抓住2位小数,将其转换为整数(* 100),然后应用舍入规则,然后预先挂起原始值的整数值。

Ex:

0.45 --> 45 --> 47 --> 0.47
4.70 --> 70 --> 67 --> 4.67
6.49 --> 49 --> 47 --> 6.47
8.32 --> 32 --> 37 --> 8.37

I've tried the rounding formula:

我尝试过舍入公式:

Let N represent the nearest number to round to.
Let X represent the input number.
output = ROUND(X / N, 0) * N

However this does not give expected results.

然而,这并没有给出预期的结果。

Ex:

0.45 --> ROUND(45 / 7, 0) * 7 --> 42 != 47
4.70 --> ROUND(70 / 7, 0) * 7 --> 70 != 67
6.49 --> ROUND(49 / 7, 0) * 7 --> 49 != 47
8.32 --> ROUND(32 / 7, 0) * 7 --> 28 != 37

How can I round to the nearest number ending in 7?

如何舍入到最接近7的最近数字?

1 个解决方案

#1

You can round only to zero. So you need to transform the number in such a way that 0.07 is 0.00. For example you can add 0.03 or subtract 0.07. Then after rounding you do the opposite (subtract 0.03 or add 0.07) and get the number you want.

你只能舍入到零。因此,您需要以0.07为0.00的方式转换数字。例如,您可以添加0.03或减去0.07。然后在四舍五入后你做相反的操作(减去0.03或加0.07)并得到你想要的数字。

The code:

=ROUND(A1+0.03, 1) - 0.03