Formula to round decimal values

Question

I'm using an application, which offers a feature of creating user-defined functions. Available set of methematical operations which could be incorporated is rather small, namely I can use:

1. addition
2. substraction
3. multiplication
4. division

Also, I can use IIF control statement with the default signature of: IIF(expression,truePart,falsePart)

Now, my goal is to create a function which could calculate rounded values from input decimals. The precision is set to the second position from the decimal point. However, it would be very nice if the precision could be parametrized through some input argument to the formula.

For example, if the input variable holds value of 3.14159, then the result of the formula would be 3.14

Can you advice me on how to define this formula?

Answer 1

Given the poverty of the instruction set, this is barely possible.

If there is a known maximum possible value, say 32767, you can implement a floor function by dichotomic search.

d= IFF(x<16384, x, x-16384)
d= IFF(d<8192, d, d-8192)
d= IFF(d<4096, d, d-4096)
d= IFF(d<2048, d, d-2048)
d= IFF(d<1024, d, d-1024)
d= IFF(d<512, d, d-512)
d= IFF(d<256, d, d-256)
d= IFF(d<128, d, d-128)
d= IFF(d<64, d, d-64)
d= IFF(d<32, d, d-32)
d= IFF(d<16, d, d-16)
d= IFF(d<8, d, d-8)
d= IFF(d<4, d, d-4)
d= IFF(d<2, d, d-2)
d= IFF(d<1, d, d-1)

Then x-d is floor(x).

From that you derive

round(x)= floor(x+0.5)

and

round(x, scale)= round(x * scale) / scale

where scale is a power of 10.

Answer 2

Here is a suggestion, assuming at most $n$ decimal digits can be stored. Let $x$ be your original number $y$ be your answer. First, if $x = 0$, you're done. Trivial case aside, you start by finding the most significant digit as follows. Keep subtracting $10^k$ for increasing values of $k$ until you get a negative number, so say $ x -10^{k}$ is negative. Then revert to your last value of $k$ i.e. set $k := k - 1$. Now iterate the following:

• Keep subtracting $10^k, 2 \times 10^k, 3\times 10^k, \dots, i \times 10^k$ etc. from $x$ until again you get a negative number. Revert to the previous $i$ (where the answer was non-negative). This is your most significant digit. This can be done in at most $n + 10$ steps.

• Now subtract $i \times 10^k$ from your original number $x$ and add it to your answer $y$.

Iterate the above process for $10^{k -1}, 10^{k -2}, 10^{-p}$, where $p$ is your precision, or stop when $x = 0$. The whole thing can be done in at most $n(n + 10)$ steps, so it can be (laboriously) written out without loops.