# added to make divisible by 10

[Ugo DI LUCA]

Your are Ed !!

[djgogi]

You could also try the following:

Case(Right(Int(num),1) <= 5,(-1)*Sign(num) *Abs(Mod(num, 10)), Sign(num)*(10-Abs(Mod(num, 10))))

the result should be ADDED to the num in every case

Dj

[jasonwood]

You could also try the following:

Case(Right(Int(num),1) <= 5,(-1)*Sign(num) *Abs(Mod(num, 10)), Sign(num)*(10-Abs(Mod(num, 10))))

The problem with something like this is that in my cause, the number is not just a number, it's a calculation that's 5-7 lines long in itself... so anything requiring the number to be repeated multiple times would make for a very long calculation, and I assume slower to compute.

I should have mentioned in the beginning that this number is always a whole number (it's derived from the first 11 digits in a UPC code), but it made for some interesting discussion!

[djgogi]

[ so anything requiring the number to be repeated multiple times would make for a very long calculation, and I assume slower to compute.

Only if the calculation can not be stored.

In stored calculation, the updates are propagated upward, toward referencing fields.

In this case multiple occurences of num in formula would not cause duplicate updating efforts.

On the other hand, if the calculation can not be stored, the propagation of updates is said to be downward, toward referenced fields.

In this case any inspection of field containing multiple references of field num, will trigger (secondary) computation in all referenced unstored calculation fields, even if those fields will have the same value as the previous time they were computed.

what is much worse, is that each reference to field num in the formula (if unstored) will trigger a separate computation of num, even if it was computed earlier.

This could even cause an exponential increment of time wasted for recalculating.

Dj

[danjacoby]

Ugo -- your original solution is still the most elegant. I don't know why there's all this discussion.

For those who don't feel like scrolling, Ugo's original answer was:

Mod(10 - Mod(your number, 10), 10))

[Lee Smith]

Hi Dan,

I agree, in fact, I said that back on page one.

I made it more flexible by adding a global field called g_X and substituting that for the 10, that way, you can use any combination of numbers and it will work. The calculation looks like this now.

Mod(g_By X - Mod(Number, g_By X), g_By X)

Lee

[Ugo DI LUCA]

Dan,

Also available for weddings, bar....

It was fun and very instructuve though to see each process.

BTW, how was the wedding like this week-end ?

[danjacoby]

The bride was lovely (VERY lovely -- in fact ... uh ... never mind).

[djgogi]

I don't think anyone here was saying that formula Ugo gave was wrong.

Anyway to see the difference in his formula and the formula I gave consider this:

num------- Ugo------- Dj

(23 )-------(7)--------(-3)

(27)--------(3)---------(3)

(-23)-------(3)---------(3)

(-27)-------(7)--------(-3)

As you see in second formula (Dj ) the difference is calculated toward nearest number divisible by 10 .

Dj

[Ugo DI LUCA]

Ciao DJ,

I agree that rounding to the nearest decimal would be what could be mostly asked, rather than only adding the numbers for the next decimal.

I also asked for this in this thred and Eddy gave a hint for this.

That was Jason case and querry though...

Now, the next dividible number should be :

15,01 ------> 20 -------> +4,99

14,99------->10 --------> - 4,99

-15,01 -----> -20 ------> -4,99

- 14,99 ----> -10 ------> +4,99

Are you with me ?

Case(Right(Int(num),1) <= 5,(-1)*Sign(num) *Abs(Mod(num, 10)), Sign(num)*(10-Abs(Mod(num, 10))))

Was it the calc we should use for comparison ?

Therefore Eddy's readapted calc should be :

Case(Round(num, -1) > 0, Case( Round(num, -1) < num,Mod(10 + Mod((num), 10), 10)*(-1), Mod(10 - Mod(num, 10), 10)), Case( Round(num, -1) < num,Mod(10 + Mod((num), 10), 10)*(-1),

Mod(10 - Mod(num, 10), 10)))

[Ugo DI LUCA]

Well,

Seems I need some holidays....

Because the killer calc to autocalculate the num part to be added or substracted based on the nearest number divisible by 10 seems to be :

I can't believe it was so simple....

Round(Num, -1) - Num

[danjacoby]

Ugo -- the winnah and still champeen!

I like simple solutions, I do.