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

Solved

Posted on 2004-11-09

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

13 Comments

NewValue:(Int([MyValue]/24

or

NewValue:(Int([MyValue]/24

Mike

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

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

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?

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

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

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

= ( ( 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.0

only for a *true* ceiling workaround???

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!

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

email documents from Access database | 7 | 38 | |

Reading the Contents of a Directory In Access VBA | 5 | 36 | |

Application.FileSearch | 5 | 28 | |

MS Access Form Control Background Color Change Depending On How Long The String Text Length Is | 18 | 33 |

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

Connect with top rated Experts

**16** Experts available now in Live!