Rounding by 5

[tjr222]

Is there any way to multiply two fields a number and a currency and have the answer be rounded up to the nearest 5. For example $41.50 would be $45.00 and $46.00 would become $50.00, etc. Any ideas on how to accomplish this would be appreciated.

 

[Len Boorman]

Think that you need to be a bit inventive here. If you divide the answer by 5 using \ operator you get div or mod can never remember whhich but basically you get integer value so if you divide 41.5 \5 you get 8. Now if you know that you want answer to nearest 5 then to the original calculation you need to add 5 so that you always round up

So original calculation then add 5
Divide this answer by 5 using special operator and multiply result by 5

Yes ?

HTH

Len B

 

[CJBIRKIN]

Hello

I'm sure i'll get shot down in flames yet again but this works in Excel so the logic should be sound.

INT(44/5)*5+5 = 45

INT(47/5)*5+5 = 50


Chris

apologies len must be writting same time!

 

[Jeff Bailey]

INT(44/5)*5+5 = 45

INT(47/5)*5+5 = 50

so far so good - but

INT(45/5)*5 + 5 = 50

so you need to check Int(n/5). If n/5 and Int(n/5) are the same then n is a multiple of 5 and you don't want to round ...

eg

If Int(n/5) <> n/5 then

use INT(n/5)*5 + 5

end if

 

[KKilfoil]

You can eliminate the 'iif' check in Jeff B's post as follows:

INT((n-0.0001)/5)*5+5

Subtacting a very small number from 45 equals 44.9999, which becomes 8 after the /5 and INT, and results in a 45 again aster the *5+5

 

[Len Boorman]

Quite right. I should have picked that up and suggested adding 4.999 instead of 5.

Some interesting alternatives.

Len B