Transfer ceiling function of excel to Access

Posted on 2004-11-09
Last Modified: 2012-06-27

I have a function in an Excel spreadsheet that takes a value from a field, adds 3%, rounds that value up to a multiple of 5 while ensuring that it is divisible by 24.

It is =(CEILING((C34*1.03)/24,5))*24

does anyone know how to replicate this over to an Access query
Question by:jainesteer
    LVL 16

    Expert Comment


    =(Int((C34*1.03)/24,5) + 1)*24

    Author Comment

    That gives an error of "function contains the wrong number of arguments"
    LVL 33

    Expert Comment

    by:Mike Eghtebas
    Say [MyValue] contains the original number you had in C34,



    NewValue:(Int([MyValue]/24)*5)+1)*24    ' I am not sure about +1

    LVL 26

    Expert Comment

    by:Alan Warren
    Hi jainesteer

    There is no roundup or rounddown function in Access, I wrote one that can be called in SQL if you want it, let me know.

    If you use the backslash integer divisor it will always return an integer, truncating the decimal part.

    Think this is mathematically correct,
    but as soon as you round it up to the nearest 5 no guarantee it will be divisible by 24 anymore

    returns: 12700

    To round down dont add 5

    Returns: 12695


    LVL 33

    Expert Comment

    by:Mike Eghtebas
    Igno my post... I leave it up to real experts (Alan and  Nestorio)

    LVL 26

    Expert Comment

    by:Alan Warren
    Mike, what the...?

    I've learnt many a neat trick from you mate.

    LVL 34

    Expert Comment

    my 2cs.

    I just let excel to what it does best

    Function myCeiling(lNumber, lSignificance) As Double
    'Needs a refrence to Microsoft Excel Object Library ?.?
    'To do this, in VBA window, Tools - Refrences and tick it from the list
    myCeiling = Excel.WorksheetFunction.CEILING(lNumber, lSignificance)

    End Function

    Sub testIt()

    MsgBox myCeiling(0.5, 1)

    End Sub

    Author Comment

    Alan:  I need to be sure so I don't think I will go with your suggestion - but thanks for the effort

    Mike:  I did not seem to get the right answer

    Flavo:  It is more than I wanted to do with the problem but I suppose at least I know that it will work.   Which leads me to the question, how to make it work.  I was wanting to run a macro from Access that effectively ran a query and dropped the result into excel.  If the query is designed to create a new excel spreadsheet, do you have any idea how I could populate this field directly from the macro?
    LVL 26

    Expert Comment

    by:Alan Warren
    jainesteer: what do you need to be sure of?
    LVL 9

    Expert Comment

    Why not just recreate the ceiling function as a custom function and use that?

    Function pCeiling(dblValue As Double, dblSig As Double) As Double
    If Int(dblValue / dblSig) <> dblValue / dblSig Then
        pCeiling = Int(dblValue / dblSig) * dblSig + dblSig
        pCeiling = dblValue
    End If
    End Function

    This will work OK but does not check that the signs are the same.



    LVL 58

    Accepted Solution

    Back to Excel:

    If you do not have CEILING, use INT, thus
       = CEILING( C34, 5 )
    is almost the same as
       = ( INT( C34 / 5 ) + 1 ) * 5
    but it fails for 5... so you have to remove a small value, as in
       = ( INT( C34 / 5 - 0.00000001 ) + 1 ) * 5

    In the original statement, you combine the CEILING and the INT method...
    Let's use INT (leaving the .0000001 out for the time being):
       = (CEILING((C34*1.03)/24,5))*24
       = ( ( INT( (C24*1.03)/24 / 5) +1 ) * 5 ) * 24

    But that looks very much like:
       =  ( INT( (C34*1.03)/120 ) +1 ) *120

    So this is your expression, Access compatible, too :)

    You will have to decide whether you need the - 0.000001 :)
    That's the hard part of a Ceiling function, actually...

    Looking a bit closer the true equivalent of Ceiling(value) is something like:
       Int(Value) - ( ( Value - Int(Value ) > 0 )       ' Assuming True = -1
    Or: take the integer portion and add 1 if there is a decimal portion...

    But do you really want (in Excel, TRUE = 1):
       = ( INT(C34*1.03/120) + ( ( (C34*1.03/120)-INT(C34*1.03/120) ) > 0 ) ) * 120
    only for a *true* ceiling workaround???

    Good Luck :)
    LVL 58

    Expert Comment

    > " If the query is designed to create a new excel spreadsheet, do you have any idea how I could populate this field directly from the macro?"
    Sometimes, depending on the method used for the transfer, you can write functions into Excel. If the query has something like:
        FunctionXL: "= CEILING( RC[-1] * 103%, 120)"
    This would create a function taking the next multiple of 120 for 103% of the cell in the previous column...

    Good Luck!

    Author Comment

    Thanks everyone - all for something that I thought should be so straightforward.  I will go with Harfang with the  =  ( INT( (C34*1.03)/120 ) +1 ) *120
    I have put this into the sql and all works well.  From there I will just pull the field into Excel as I do with all of the others in that query.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    779 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