Ask Your Question
0

VAR, STDEV, CHISQ.INV

asked 2015-07-05 18:39:32 +0100

rpcaldeira gravatar image

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
edit retag flag offensive close merge delete

Comments

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

doug gravatar imagedoug ( 2015-07-06 02:35:56 +0100 )edit

Hey Doug, I did not understand your question :/

rpcaldeira gravatar imagerpcaldeira ( 2015-07-06 11:14:24 +0100 )edit

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?

doug gravatar imagedoug ( 2015-07-06 13:06:04 +0100 )edit

No, a TRUE value returns -1 :/

rpcaldeira gravatar imagerpcaldeira ( 2015-07-07 11:55:04 +0100 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2015-07-16 05:51:38 +0100

doug gravatar image

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.

edit flag offensive delete link more
0

answered 2015-07-16 09:00:03 +0100

pierre-yves samyn gravatar image
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-07-05 18:39:32 +0100

Seen: 221 times

Last updated: Jul 16 '15