Formula sqrt to VBA conversion

[Sintek]

Hi Guys

In attached sample file I am trying to get F26 calculation...
The code does not error but the answer is an error...Any ideas

Option Explicit

Sub Test()
Dim CatSz As Long, Size As Long, Prop As Double, StdErr As Double
Size = [F23]
CatSz = [F24]
Prop = CatSz / Size
StdErr = Sqr((Prop * (1 - Prop) / Size))
End Sub

[kasan]

Hi,
What do you mean by "answer is error"? Your code gave me the same result as manual calculations with formulas.
What is the correct answer?

[Sintek]

Hi kasan

This is my result ... Office 2007, 2013
Untitled.png

[pjwhitfield]

Hi kasan

This is my result ... Office 2007, 2013
Attachment 588614

I also get the incorrect answer

[pjwhitfield]

I broke the calc down into parts (adding the following)

a = 1 - Prop
b = a / Size
c = Prop * b
StdErr = Sqr(c)

I Dimmed a,b and c as Double's

The problem seems to occur in (b), it correctly calculates a = 0.35 but then gets 0.35 / 60 as being 5.833333333E-03

? strange

[TMS]

What makes you say it's an error output?

Option Explicit

Sub Test()
Dim CatSz As Long, Size As Long, Prop As Double, StdErr As Double
Size = [F23]
CatSz = [F24]
Prop = CatSz / Size
StdErr = Sqr((Prop * (1 - Prop) / Size))

Debug.Print "1", Size, CatSz, Prop, StdErr

StdErr = Round(StdErr, 10)

Debug.Print "2", Size, CatSz, Prop, StdErr

End Sub

1              60            39            0.65          6.15765106730372E-02 
2              60            39            0.65          0.0615765107

That's not an error, just a small number.

[Sintek]

Hallo Trevor

Tx for putting me out of my misery lol...Please elaborate ...

just a small number.

Edit... Now I get it ...Only noticed that all the calculations to follow after running Main code calculates as it should ...

Small Magnitudes

For numbers with the smallest possible magnitude (closest to 0), Double variables can hold numbers as small as -4.94065645841246544E-324 for negative values and 4.94065645841246544E-324 for positive values.

[Sintek]

@ kasan...

Your code gave me the same result as manual calculations with formulas.

How did you achieve that ...

[TMS]

Well, to be exact, it's a small(ish) number with a lot of digits after the decimal point ... 6.15765106730372E-02.

Excel is limited to the number of digits it can display/handle.

If you round the result to, say, 15 digits after the decimal point, you get 0.061576510673037. That is,around six one hundredths (and a little bit)

In the code above, I rounded the result to 10 digits. It rather depends on what level of accuracy you need.

[TMS]

You're welcome. Thanks for the rep.