Divisible by 3 3

[FreddyBotz]

Hello to all. I have been given the responsibility of ensuring that thousands of numbers meet certain criteria in order for the calculations to be accurate. In a nutshell, these number MUST be divisible by three in order for this program to perform the calculations correctly. I've come up with a formula that works very well in Excel, but being that I'm new to Access, I'm not quite certain how I can implement such a formula. Here's the formula: IF(ROUND(A20/3,2)*3-A20=0,A20,ROUND(A20/3,2)*3)

Is there any way something like this can be used in Access? Any advice would be greatly appreciated.

Regards

FB

 

[tlbroadbent]

You can use the IIf function in a query.

IIF(ROUND([Col1]/3,2)*3-[Col1]=0,[Col1],ROUND([Col1]/3,2)*3)

I really don't see a reason to do the IIf test if you are always going to use the value returned by the function. Why not just use ROUND([Col1]/3,2)*3 for all the calculations without testing? The result is the same. The IIF function above can be rewritten to better show this is true.

IIF(ROUND([Col1]/3,2)*3=[Col1],[Col1],ROUND([Col1]/3,2)*3)

IIf will choose either Col1 when it is equal to ROUND([Col1]/3,2)*3 or it will choose ROUND([Col1]/3,2)*3. In either case the result is the same value. Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[JohnYingling]

In VB it is lngResult = X mod 3 ' Remainder of X / 3.
In Excel the formula is =MOD(A1,3).
There is usually a MOD function wherever there is a ROUND.

http://WWW.VBCompare.Com

 

[MichaelRed]

I know I'm old and slow. I still don't get it.

Consider:

MyVal = 5

? IIf(ROUND(MyVal/3,2)*3-MyVal=0,MyVal,ROUND(MyVal/3,2)*3)
5.01
which is FreddyBotz's calculation. Just setting myval in place of the cell reference with a value odf 5. It does not return a value (evenly?) divisible by 3.

John's approach (mod) likewise fails to yield the value (envnly) divisible by three. Since we are easily able to divide (almost) any numeric value by three and get SOME results, it would appear that Freddy must really want the whole number divisible by three.


Freddy states:
"I have been given the responsibility of ensuring that thousands of numbers meet certain criteria in order for the calculations to be accurate.

This - in itself is weird. Other than some obscure accocunting trick (rule of nines type) I know of NO real application of needing a LOT of values to be evenly divisible by any value.

In what ever instance, I think that the calculation should be:

Val3 = ValIn - (ValIn mod 3)



MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[JohnYingling]

Michael,
The problem as stated was to verify that the number was divisible by three. So
? IIf(MOD(MyVal,)=0,.....
should do the trick.

http://WWW.VBCompare.Com

 

[MichaelRed]

John,

With the addition of the IIF, it is certainly closer. I am still not sure what is FreddyBotz wants. His post was in the Ms. Access forum and he sates that he needs the function for that environment. The 'Statement'/Formula he says provides him witht the results in Excel does NOT appear to give the value he seems to be saying he wants in Ms. Access.

His "formula" is fraught with peril, as the round (to two places) will return "floating point" values and seldom return the "evenly divissible" value.

The comment that he " ... ensuring that thousands of numbers meet certain criteria ... " does NOT specify what to do if (WHEN the input value is not evenly divisible by three. Further, the statement re "thousands of numbers" implies (to me) that the values are in a table.

Unless you provide the conditional clauses for your IIF, I still do not know what you intend to return, only that there are still possabilities for the return of SOME value. Like wise, we cannot be really sure what FreddyBotz wants, but his (Excel) formula SUGGESTS that he wants the nearest value less than or equal to the input value which is evenly divisible by three. Your IIF COULD do that, and my simplistic statement will do that without the conditional. MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[FreddyBotz]

I appreciate the advice and I also agree that it sounded a little weird at
first but here's the reason: the entire number must be divisible by 3 to the
nearest penny. Why? Because of percentage increases. Somehow when finance
increases it first by 8% then by 25%, if the number is not divisible by 3,
the computer will generate an error message. I still don't know why the
computer can't figure it out. All I know is that I was just given this
assignment and the person before me was doing each of the 41,000 calculations
by hand then inputting them back into Access. I knew there had to be an
easier way.

Thanks Again

 

[scking]

Assuming the types of the data items used in the calculations are currency, since you mentioned they must be to the nearest penny you would have a calcuation similar to this if we were to fill in currency values for the unknowns.

Steve King

Dim MyTotalCurrency As Currency
MyTotalCurrency = ( compute the totals expression )
If IsDivisibleByThree(MyTotalCurrency) Then

' when mytotalcurrency is 0
' or the value when it is equally
' divisible by 3 then this statement
' is true and will process the statments
' within the if

End If
Public Function IsDivisibleByThree(InMoney As Currency) As Boolean
' Function tested OK
Dim LongValue As Long
LongValue = InMoney * 100
If (LongValue Mod 3) = 0 Then
IsDivisibleByThree = True
End If

End Function Growth follows a healthy professional curiosity