# added to make divisible by 10

[jasonwood]

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?

[Ugo DI LUCA]

Try yhis :

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

[BobWeaver]

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

10 - Mod(YourNumber,10)

[Ugo DI LUCA]

Hi Bob,

Is it ?

Are you sure this returns 0 if it is 90 ?

[Peter Fenner]

Or:

10 - Right(YourNumber,1)

[Ugo DI LUCA]

Pete,

This doesn't work for 85,5

Bob,

Just checked. Returns 10 not 0.

[Peter Fenner]

Incorrect, Sorry:

Abs(0 - Right(YourNumber,1) )

[Peter Fenner]

OK, I forgot about fractions. Let me think

Pete

[Ugo DI LUCA]

Pete.....

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

[Ugo DI LUCA]

Go try :

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

It's working for me....

[Peter Fenner]

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

Pete

[Peter Fenner]

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

It worked beautifully of course.

Pete

[Ugo DI LUCA]

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.

[BobWeaver]

Sorry Ugo, you are quite right.

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

[Ugo DI LUCA]

With Abs, we could try :

Abs (Mod(YourNumber,10))

[Ugo DI LUCA]

Nope :

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

[jasonwood]

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

[Lee Smith]

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

[Peter Fenner]

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

[Peter Fenner]

Hi Lee,

And if the number is -157

Pete

[Ugo DI LUCA]

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 ?

[Lee Smith]

Hi Pete

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

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

Lee

[Peter Fenner]

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

[Ugo DI LUCA]

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.

[Peter Fenner]

Round(YourNumber / 10, 0) * 10

[Ugo DI LUCA]

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.

[EddyB]

Hi all

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

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

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

:? That right?

Ed

[Ugo DI LUCA]

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

[EddyB]

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

Ed.

[Ugo DI LUCA]

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

Thanks for the answer.

[EddyB]

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.

[BobWeaver]

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.

[Ugo DI LUCA]

Bob,

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

Very good call again...

[EddyB]

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

Ed.

[BobWeaver]

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.