jainesteer
asked on
Transfer ceiling function of excel to Access
Hi
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
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)
does anyone know how to replicate this over to an Access query
ASKER
That gives an error of "function contains the wrong number of arguments"
Say [MyValue] contains the original number you had in C34,
NewValue:(Int([MyValue]/24 )*5))*24
or
NewValue:(Int([MyValue]/24 )*5)+1)*24 ' I am not sure about +1
Mike
NewValue:(Int([MyValue]/24
or
NewValue:(Int([MyValue]/24
Mike
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
(((((12345*1.03)\24)*24)+5 )\5)*5
returns: 12700
To round down dont add 5
((((12345*1.03)\24)*24)\5) *5
Returns: 12695
Alan
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
(((((12345*1.03)\24)*24)+5
returns: 12700
To round down dont add 5
((((12345*1.03)\24)*24)\5)
Returns: 12695
Alan
Igno my post... I leave it up to real experts (Alan and Nestorio)
Mike
Mike
Mike, what the...?
I've learnt many a neat trick from you mate.
Alan
I've learnt many a neat trick from you mate.
Alan
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.CE ILING(lNum ber, lSignificance)
End Function
Sub testIt()
MsgBox myCeiling(0.5, 1)
End Sub
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.CE
End Function
Sub testIt()
MsgBox myCeiling(0.5, 1)
End Sub
ASKER
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?
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?
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
Else
pCeiling = dblValue
End If
End Function
This will work OK but does not check that the signs are the same.
=(pCeiling((C34*1.03)/24,5 ))*24
HTH
Peter
Function pCeiling(dblValue As Double, dblSig As Double) As Double
If Int(dblValue / dblSig) <> dblValue / dblSig Then
pCeiling = Int(dblValue / dblSig) * dblSig + dblSig
Else
pCeiling = dblValue
End If
End Function
This will work OK but does not check that the signs are the same.
=(pCeiling((C34*1.03)/24,5
HTH
Peter
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
> " 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!
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!
ASKER
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.
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.
=(Int((C34*1.03)/24,5) + 1)*24