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:

addition
substraction
multiplication
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?

How are the numbers represented ? What range ? What accuracy ? — , Aug 02 '15 at 16:49
Unfortunately, this knowledge is hidden from me. Can we make an assumption for floating-point (this concept I'm more familiar to then the arbitrary precision)? Range as for c# data type of Double — kamilzet_, Aug 02 '15 at 16:51
If the numbers stored have finite precision, say to $n$ digits, and you want precision to some $k \leq n$ digits, then just divide by $10^{n - k}$ and then multiply by $10^{n - k}$. The divide will "knock the digits off" the end, then the multiply will bring back in a string of $0$s in their place. — Colm Bhandal, Aug 02 '15 at 16:52
If you don't know the precision $n$, then play around with some values until it works. — Colm Bhandal, Aug 02 '15 at 16:53
@kamilzet_ are you programming in c#? Do they not have a remainder function? And loops? You can do it with these. Or with bit-shifting. — Colm Bhandal, Aug 02 '15 at 16:55
@ColmBhandal: the shifting approach is a step towards the solution, but if the numbers are represented in floating-point, you don't know what power of 10 to use. Also, if the representation doesn't use base 10, truncation won't give the expected result. — , Aug 02 '15 at 16:57
Still what you could do (laboriously without a loop) is a) multiply by $10^k$, where $k$ is your precision b) extract each digit c) put the digits back together. Step b) would require lots of cases of $n$ and dividing, but it could be done with bounded $n$. — Colm Bhandal, Aug 02 '15 at 17:03
@ColmBhandal I'm programming in C#, however in discussed case I was asked to define a formula in some 3rd party software, which has very limited formula building capabilites, as mentioned in the main question. — kamilzet_, Aug 02 '15 at 17:03
@kamilzet_: are you sure this application doesn't support more operations that could be useful ? (even if you don't think they are) MS Access ? — , Aug 02 '15 at 17:04
@Yves Daoust,@Colm Bhandal: Well, there is one basic requirement, not provided from the beginning: this also cannot be a procedural code, it has to be written as a one expression. (However your answers with alghoritmic approach are also valuable to me) — kamilzet_, Aug 02 '15 at 17:38

score 1 · Accepted Answer · 2015-08-02T17:33:51.300

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.

edited Aug 02 '15 at 17:33

answered Aug 02 '15 at 16:53

Probably the best you're going to get with the instruction set given. – Colm Bhandal Aug 02 '15 at 17:29
@Yves Daoust, are you able to provide implementation of this part:
round(x)= floor(x+0.5) and

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

,using code instructions?
– kamilzet_ Aug 02 '15 at 17:59
@kamilzet_: that's your task, the hint is sufficient. – Aug 02 '15 at 19:18
@Yves Daoust: Yes, I've implemented it. Thanks for your prompt. – kamilzet_ Aug 03 '15 at 09:06
@kamilzet_: are you sure this application doesn't support more operations that could be useful ? (even if you don't think they are) MS Access ? – Aug 03 '15 at 09:16
@Yves Daoust: it is an application build from the ground, using modern languages. It's not an application developed in RAD enviroments (like MS Excel or MS Access). I've found that aggregate function are also available to be included in formula pattern; these are:
1. min()
2.max() 3.avg() 4.sum()
– kamilzet_ Aug 03 '15 at 09:25
Mh, I don't see how these aggregates could be used. Nothing else ? – Aug 03 '15 at 10:27

score 0 · Answer 2 · answered Aug 02 '15 at 17:23

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.

Formula to round decimal values

2 Answers2