Given a number... like 86 for example, I need a calculation field to work out what needs to be added to that number to make it divisible by 10. In the case of 86 it would be 4.

I tried this...

Truncate (TheNumber, -1) + 10 - TheNumber

But that doesn't work if the number is already divisible by 10 (because it returns 10 when I want 0)

Do I have to add in an if statement to check for this or am I missing something simple?

Try yhis :

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

If you are only dealing with positive numbers, you only need:

10 - Mod(YourNumber,10)

Hi Bob,

Is it ?

Are you sure this returns 0 if it is 90 ?

Or:

10 - Right(YourNumber,1)

Pete,

This doesn't work for 85,5

Bob,

Just checked. Returns 10 not 0.

Incorrect, Sorry:

Abs(0 - Right(YourNumber,1) )

OK, I forgot about fractions. Let me think

Pete

Pete.....

7/5th of all people do not understand fractions.....

Go try :

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

It's working for me....

OK, I can't think of another method. Ugo, I see you are back to your absolute sharpest tonight. Excellent!

Pete

I did try it - but get rid of that last bracket!

It worked beautifully of course.

Pete

OOps...

MOD(10 - MOD(num; 10); 10) for sure...

The bracket was a rest of

(MOD(10-MOD(num; 10); 10) + num) which I had used to pull 86 to 90...

which I first thought was what Jason was looking for.

Sorry Ugo, you are quite right.

I got sidetracked thinking about negative numbers, and missed the obvious.

With Abs, we could try :

Abs (Mod(YourNumber,10))

Nope :

Abs (10- Mod(YourNumber,10)) but only for positive values

Thank you all... my UPC bar code font is now fully functional and I'm ready to take over the world!!! Muhahaha!!!

Hi Ugo,

Your are the champ,

No need mucking up the waters with this:

Abs (Mod(YourNumber,10))

As it returned the same as:

Mod(Number, 10)

Lee

Its final. Ugo takes all the honors here.

I thought I was being clever with the little simple "Right" calculation. Oh No, what if the number is already divisible by 10. So, I thought I got clever with my abs post. Oh No, what about fractions.

Thats it, before I try to be too smart next time I am going to think the whole thing through before I post! But first I am thinking of a new "vanity" title for Ugo.

Pete

Hi Lee,

And if the number is -157

Pete

Jason and all here...

We now know that Abs is not working,

that MOD(10 - MOD(num, 10), 10) returns 4 for 86 if we want to add up

and

that MOD(10 + MOD(num, 10), 10) returns 4 for 84 if we want to substract.

Now, what would be the function to determine if it is 85,01 to round to 90 and if it is 84,99 to round to 80 ?

I've tried it at first and didn't find anything.

Any clue here ?

Hi Pete

[color:"blue"]And if the number is -157

Both returned 7, the opsit of coure of thecorrect answer of 3.

Lee

You mean to return 4.99 if it is 84.99 or 85.01?

I mean round values

from 80 to 84,99-----> 80

and values

from 85,01 to 90------>90

thus extracting the value (+ or -) necessary for this rounding.

Round(YourNumber / 10, 0) * 10

Well,

just forgot about Round to round...

So Round(YourNumber / 10, 0) * 10 - Your Number gives me the number to add or substract.

Cool. I'll use it for my pricing system....

Just forgot this silly titles

Thanks for the whole thread.

Hi all

If ( Round(YourNumber/10, 0)*10 < YourNumber,

MOD(10 - MOD(num, 10), 10),

MOD(10 + MOD(num, 10), 10)

:? That right?

Ed

Thanks Eddy,

I'll try both method...

Just for information, I was planning to round my price list when the prices are > 200 euros.

So if a good is listed 204,90 euros, I'll round it to 199,99 with either "Eddy's calc -0,01" or "Pete's calc -0,01"

If it is listed 205,10, I'll round it to 209,99.

Thanks to both of you.

Well, I'd just change If to Case....

Ugo, it's not working as planned - I missed something somewhere but I'm sure you'll work it out! I'm tired!

Ed.

Eddy, just a switch from < to > makes it work like a charm...

Thanks for the answer.

OK it was going to annoy me until I got it down so here's my final answer!

Case( (Round(Number/10, 0)*10) < Number , " - " & Mod(10 + Mod(Number, 10), 10) , (Round(Number/10, 0)*10) > Number , "+" & Mod(10 - Mod(Number, 10), 10))

Now I can sleep!

Ed.

Ugo, you can use the Round() function to round to a negative number of decimal places (ie, positions to the left of the decimal point). So, you can simply do it with:

Round(TheNumber,-1)

That will round it to the nearest 10.

Bob,

just a great Thank you. Again a good day for my FM Brain.

Very good call again...

OK that just blew my case statement out the water! Thanks Bob - thought I was getting good for a moment there!!!

Ed.

And while I'm being obsessive,

Here's another possiblility for Jason's calculation:

Mod(1000000000000000-TheNumber,10)

Okay, I'm going to quit now.

Your are Ed !!

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

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!

[ 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

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))

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

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 ?

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

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

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)))

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

Ugo -- the winnah and still champeen!

I like simple solutions, I do.