0

My code in Excel-VBA is the following:

Public Function ci(p As Double) As Long

Dim i As Long

i = 0
ci = 0

Do Until (p + i * 1.725) Mod 20 = 0

i = i + 1

ci = i 

Loop
End Function

So, the results were not real because this kind of forums let me know that variable type double is not exact. Therefore, the operator mod didn´t give reliable results too. I hope you can give how to deal with this issue and to work with the right kind of variable.

CreamStat
  • 2,155
  • 6
  • 27
  • 43
  • 1
    Real number as opposed to a complex number? Define reliable? What is your input and expect versus actual output? – Automate This Oct 02 '13 at 21:52
  • It´s just that variable tipe double can not be compared in vba as equal.That´s why the results of my algorithm are not exact at all. http://msdn.microsoft.com/en-us/library/ae382yt8.aspx – CreamStat Oct 02 '13 at 21:55
  • In your case Double vs Long does not matter since Mod returns integers and will eventually give you zero. – Gary's Student Oct 02 '13 at 21:57
  • 1
    why would you use mod against a double (float value?) what are you trying to calculate? Typically you would convert the double value to an Int by rounding it first, before attempting MOD on it. – LaloInDublin Oct 02 '13 at 22:08
  • For example my input p=1058.6 gives an output ci=2. However, according to the logical of the algorithm should give me ci=24. – CreamStat Oct 02 '13 at 22:13
  • To make the context more clear, p represent marks of an exam of 100 questions which gives 20 point per correct answer and -1.725 per incorrect answer and 0 points if you didn't answer. And I've proved mathematically that there is only one posible solution of corrects an incorrects questions given the input p. So ci should give the number of incorrect answers. – CreamStat Oct 02 '13 at 22:24

1 Answers1

0

If you're dealing with fractions of no more than 4 decimal places, you could try a Currency datatype. This has 4 decimal places, and uses integer calculation. Otherwise, you could use a Decimal, which is a variant assigned by:

Dim vDecimal as Variant
vDecimal = CDec(0)

which also uses integer calculation, and has up to 28 decimal places.

EDIT:

This overcomes the Mod argument rounding:

Public Function ci(p As Currency) As Long
    ci = 0
    Do Until (p + ci * CCur(1.725)) * 1000 Mod 20000 = 0
        ci = ci + 1
    Loop
End Function
Monty Wild
  • 3,981
  • 1
  • 21
  • 36
  • My input p has at the most 4 decimal places. So my problem is solved. Thanks from Peru! – CreamStat Oct 03 '13 at 02:15
  • Sorry, my function doesn´t give real results in some cases, for example: When the input p is 1173.425, ci is equal to 4, but the real value of ci according to the algortim should be 27. Any help? – CreamStat Oct 03 '13 at 04:30
  • Mod rounds its arguments. See my edit. However, I get a value of 28 as an output with an input of 1173.425 with `CI=I+1`. – Monty Wild Oct 03 '13 at 05:05
  • It's is CI=I, I've just edited. I tried your code and finally it gives the exact result I was looking for!. But, let me understand the logic of your code, you multiply by 2000 and 1000 in order to convert any currency variable into an integer, because just in that way the mod function will give the correct results given the integers factor. Am I rigth? – CreamStat Oct 03 '13 at 05:44
  • Yes: Since you are dealing with three decimal places, you need to multiply the values on each side of the Mod function by 10^3 (1000) to negate the effect of decimal truncation. – Monty Wild Oct 03 '13 at 22:01