-----Original Message-----
As I said in my original post HM Customs & Excise like to
see VAT on calculated as ([Cost On] * 47) /7 NOT 17.5% OR
1.175 or any other combination that isn't 7/47.
Debug.Print [CostOff] & " * 47 / 40 = " & CostOff * 1.175
Customs & Excise like to see that VAT is calculate at 7/40
(VAT on at 17.50%) and 7/47 (VAT off at 17.50%), has
anyone come accross code to calculate the lowest common
demonitor or has anyone sat down and worked out all the
possibles between 0% and let's say 50% (knowing our Tony)
in 0.25% increment?
Well, this is still O-level maths: once upon a time I knew a really nifty
algorithm for getting lowest-common-denominator but this is the long way
round:
Option Compare Database
Option Explicit
Public Sub GetFraction(Percentage As Double)
'
' percentage should be passed as a number eg 17.5
' assuming 0.25 increments
'
Dim dwNumerator As Long
Dim dwDenominator As Long
' any number you like here, it's only for demonstration
Const CostOff As Double = 134.75
Dim CostOn As Double
dwNumerator = 4 * (100 + Percentage)
dwDenominator = 400
Reduce dwNumerator, dwDenominator
CostOn = CostOff * (100 + Percentage) / 100
Debug.Print CostOff; " * "; dwNumerator; "/"; dwDenominator; _
" = "; CostOn
Debug.Print CostOn; " * "; dwDenominator; "/"; dwNumerator; _
" = "; CostOn * dwDenominator / dwNumerator
End Sub
Public Sub Reduce(ByRef NumOne As Long, ByRef NumTwo As Long)
'
' This is the brute force approach to reducing two numbers to their
' lowest common denominator. It's reasonably quick for the low numbers
' we are using here.
'
Dim va_dwPrimes As Variant
Dim dwPrimeTemp As Long
Dim wPrimeCounter As Integer
' quick array of prime numbers
' don't need to go above sqrt(400) which is 20
va_dwPrimes = Array(2, 3, 5, 7, 11, 13, 17, 19) ' 23, 27)
' use repeated division to get rid of shared factors
For wPrimeCounter = LBound(va_dwPrimes) To UBound (va_dwPrimes)
dwPrimeTemp = va_dwPrimes(wPrimeCounter)
' quick exit if prime is higher than sqrt(number)
If NumOne < dwPrimeTemp * dwPrimeTemp Or _
NumTwo < dwPrimeTemp * dwPrimeTemp Then
Exit For
End If
' check if the numbers are still divisible
Do While True
If NumOne Mod dwPrimeTemp > 0 Or _
NumTwo Mod dwPrimeTemp > 0 Then
Exit Do
End If
' yes, okay do it
NumOne = NumOne / dwPrimeTemp
NumTwo = NumTwo / dwPrimeTemp
Loop
Next wPrimeCounter
End Sub
Hope that helps
Tim F
.
)