Transfer ceiling function of excel to Access


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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


=(Int((C34*1.03)/24,5) + 1)*24
jainesteerAuthor Commented:
That gives an error of "function contains the wrong number of arguments"
Mike EghtebasDatabase and Application DeveloperCommented:
Say [MyValue] contains the original number you had in C34,



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

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Alan WarrenApplications DeveloperCommented:
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


Mike EghtebasDatabase and Application DeveloperCommented:
Igno my post... I leave it up to real experts (Alan and  Nestorio)

Alan WarrenApplications DeveloperCommented:
Mike, what the...?

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

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
jainesteerAuthor Commented:
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?
Alan WarrenApplications DeveloperCommented:
jainesteer: what do you need to be sure of?
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.



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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
> " 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!
jainesteerAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.