Round to nearest number - Divisible by 4,000

Posted on 2012-08-15
Last Modified: 2012-08-15
I am using the following formula to round the resulting number to the nearest 1,000:


Open in new window

I need to modify this formula so it rounds to the nearest number divisible by 4,000.

Thanks for your help.
Question by:DanielAttard
    LVL 65

    Expert Comment

    by:Jim Horn

    Give this a whirl...

    Declare @num numeric = 5538354.25

    SELECT CAST(ROUND(@num / 4000,0) as int) * 4000

    Let us know if you're asking for this as an Access query expression, SQL Server T-SQL, or Excel expression.
    LVL 68

    Accepted Solution

    Just replace your 1000's with 4000's to get the expected result:
                Int( (999000*1.2) / 4000 + 0.5 ) * 4000
    LVL 48

    Expert Comment

    by:Gustav Brock
    You can simply use:

    RoundedUp: -Int(-999000*1.2/1000)*1000


    RoundedUp4000: -Int(-999000*1.2/4000)*4000


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now