Determine if 2 fields are multiples of eachother

[trulyblessed]

How can I go about determining if two fields are multiples of eachother?

For now, I'm trying to create two calculated fields. Calculated Field1 is qty/packsize and Calculated Field2 is packsize/qty. My thought was that if both of the calculated fields contained a number with a decimal, then the numbers were not multiples of eachother. Is there an easier, more sophisticated way of doing this?

Thanks,
Kim

 

[Golom]

IIF(Number1 Mod Number2 = 0, "Multiples", "Not Multiples&quot

Where Number1 >= Number2

That's if they are integer multiples (i.e. Number1 = X * Number2) where X is a whole number.

 

[trulyblessed]

Thank you. This looks much better than what I have.

What about if they are not whole integers. For example, the packsize is 1.5 and the qty is 3. It's coming up as not a multiple, but really it is. Is there a way around this?

Kim

 

[Golom]

That's a limitation of Mod. It rounds floating point numbers to whole numbers so

3 Mod 1.5

is converted to

3 Mod 2

If you have (for example) 3 digits after the decimal then you might try

(Number1 * 1000) Mod (Number2 * 1000)

to get move the decimal place and make everything whole numbers.

 

[mikevh]

I never realized that about the rounding with Mod.
Guess I've only ever used it with integers.

How about

IIF(Number1/Number2 = Int(Number1/Number2),"Multiples", "Not Multiples&quot

Where Number1 >= Number2

 

[trulyblessed]

Thank you so much to both of you. I just tried Mikevh's syntax and that really seemed to take care of my problem.

I really appreciate it!

Kim

 

[MichaelRed]

hmmmmmmmmmmmmmm ... mmmmmmmmmmm ... mmmmmmmmm

at least a good try, but it appears to not recognise the basic concept of digital representation of non-'integer' values.

See the following example:

Public Function basMultiChk(dblVal As Double, dblVal2 As Double) As Boolean

    Dim dblValTst As Variant

    basMultiChk = (dblVal / dblVal2) = CInt(dblVal / dblVal2)

End Function

for xx = 1 to 10: ? xx, xx * 13.2, basMultichk(xx * 13.2, 13.2): Next xx
 1             13.2         True
 2             26.4         True
 3             39.6         False
 4             52.8         True
 5             66           True
 6             79.2         False
 7             92.4         True
 8             105.6        True
 9             118.8        True
 10            132          True
[code}



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[MichaelRed]

... but you can come somewhat closer:

Public Function basMultiChk(dblVal As Double, dblVal2 As Double) As Boolean

    Dim dblMultiTst As Double
    Const dblMultiDif = 0.00000015

    dblMultiTst = Abs((dblVal / dblVal2) = CInt(dblVal / dblVal2))
    basMultiChk = dblMultiTst >= dblMultiDiff

End Function


for xx = 1 to 10: ? xx, xx * 13.2, basMultichk(xx * 13.2, 13.2): Next xx
 1             13.2         True
 2             26.4         True
 3             39.6         True
 4             52.8         True
 5             66           True
 6             79.2         True
 7             92.4         True
 8             105.6        True
 9             118.8        True
 10            132          True

of course, you can use any value for dblMultiDif, realizing that the function's "sensitivity" will vary inversly to its' value.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places