VAR, STDEV, CHISQ.INV

users
#1

Hello guys, I’m having some problems with the VAR and STDEV function, when
executing these lines of code. The average is well calculated but the
remaining two return very low, unrealistic and equal values
(6.93018471335974E-310).

To add to a bad situation, I can’t execute the function CHISQINV as stated
in the second code box below. It gives out the error:

BASIC runtime error.
An exception occurred
Type: com.sun.star.lang.IllegalArgumentException
Message: .

Any help is deeply appreciated!

Thanks,
Rui

====================== CODE BLOCK 1 ======================oRange =
xSheet.getCellRangeByPosition(7, 1, 7, rangeLim)'Media (funciona)
average = oRange.computeFunction(com.sun.star.sheet.GeneralFunction.AVERAGE)
print average'Variancia (ver)
variance = oRange.computeFunction(com.sun.star.sheet.GeneralFunction.VAR)
print variance'StdDev (ver)
stdDev = oRange.computeFunction(com.sun.star.sheet.GeneralFunction.STDEV)
print stdDev

====================== CODE BLOCK 2 ======================
function CHISQINV (erro, degree) dim oFunction as variant
  oFunction = createUnoService("com.sun.star.sheet.FunctionAccess")
  dim aArgument(1 to 2) as variant
  dim result as double aArgument(1)=erro
  aArgument(2)=degree result = oFunction.callFunction("CHISQ.INV",
aArgument()) CHISQINV = resultend function

#2

That value is 2^-1027 - which is probably the smallest floating-point value your system can represent without underflow.

Have you compared the results produced from your data by equivalent normal formulae in a spreadsheet cell?

Do you mean CHISQ.INV and not CHISQINV?

Brian Barker

#3

Hey and thanks for your fast answer. This is the data set:

  0.000001 0.000001 0.000001 0.000001 0.000001 0.000001 0.000001
0.000001 0.000001 0.000001 0.000001 0.000001 0.000001 0.000001
0.000002 0.000003 0.999997 0.999997 0.999997 0.999997 0.999998
1.000001 1.999997

Using the formula in the spreadsheet it gives the value: 0.328061419 (VAR)
and 0.5727664611 (STDEV)

And yes, i do mean CHISQ.INV :slight_smile:

Thanks, Rui.

Cumprimentos,
Rui Pedro Caldeira

#4

Sounds to me like normal floating number behavior. I know someone is trying
to document this and explain why it happens (it's a technical reason that
goes beyond my skills) but this seems entirely normal behavior given the
constraints of programming languages generally and hardware in particular.

Best,
Joel

P.S. You can see similar issues in other spreadsheet software. This seems
to give some indication as to the reasons why:
https://support.microsoft.com/en-us/kb/78113 (yes I know it's MSO
link...just explaining the reason why floating number isn't entirely
accurate all the time)

#5

Guess what 1f*5/5==1f (Java; 1 AS float multiplied by 5 divided by 5 is equals to 1 as a float)
Everyone not studying computer science won't understand this at first glance (and Google has the best explanations) but with Math.abs((1f/5*5)-1)<0.0001 you do not have a workaround, but the way it has to be done.... (Die not read the thread, just want to explain that you cannot = on floats)

#6

Joel,

just read the MS-Excel explanation you are referring to. Great explanation.
Thanks for pointing to this.

Rob.

#7

Thank you for all your help!

My best,
Rui

Cumprimentos,
Rui Pedro Caldeira