Per cent change formula give different results - which should be used?

[i_excel]

The following expressions of per cent change are mathematically equivalent:
x/y - 1 and (x-y)/y

However, they give slightly different results in excel, which complicates figure checking.

Does anyone know which is preferable?

i_excel

 

[pvr928]

Hi i_excel

Can you give an example where you get different results?

Cheers

pvr928

 

[i_excel]

Hey pvr928

For example:
x= 0.067 and y =0.066

i_excel

 

[pvr928]

Hi @i_excel

Yes - I see what you mean. I tried it out with VBA and the two formula were equal:

Option Explicit
Sub TestForPrecision()
Dim x As Variant
Dim y As Variant
x = 0.067
y = 0.066
x = CDec(x)
y = CDec(y)
Sheet3.Range("D2") = x / y - 1
Sheet3.Range("D3") = (x - y) / y
End Sub

I got the inspiration to use CDec() from this article: https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel , although I achieved the same result dimensioning each of x and y as Double and then not using the CDec() function at all.

Is this an option?

Cheers

pvr928