VAR, STDEV, CHISQ.INV

[rpcaldeira]

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 = result
end function

[doug]

is that value the equivalent of TRUE via a type mismatch?

[rpcaldeira]

Hey Doug, I did not understand your question

[doug]

like, if I passed a binary TRUE value into a DOUBLE variable, I was wondering if it would look like that, like, the lowest value available in the DOUBLE variable higher than zero. Maybe the error message is objecting to an Empty value being passed to the function?

[rpcaldeira]

No, a TRUE value returns -1

[doug]

Both code blocks are incomplete. That said, code block 1 is affected by, and your result is consistent with, a long-documented bug. I suggest calculating these values manually in the macro. I reproduce your results by adding to the beginning of your block 1:

Sheets =  ThisComponent.Sheets
Sheet = Sheets.getByName("Sheet1")
oRange = Sheet.getCellRangeByPosition(0, 0, 0, 6)

Where 0 is the first row or column, and filling in data in those blocks.

Additionally, in LO 5.0.0.2 I must change the constants for .callFunction to ( SbxLONG ) meaning: a number. Based on the list here and trial and error, it appears that 2 is SUM, 4 is AVERAGE, 5 is MAX and so on to 9 for STDEV and 11 for VAR. Using 9 and 11 I replicate your results, with is consistent with the bug report linked above, i.e., variance = oRange.computeFunction(11)

For block 2, simply running the macro does not pass any variables to the function, which are required (errno, and degree) and I cannot supply those values so the description or code block is incomplete and does not run as posted.

[PYS]

Hi

See also tdf#46119 (not fixed) and tdf#72474 (resolved)

Regards