Missing parameter in LINEST

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Missing parameter in LINEST

Regina Henschel
Hi all,

the draft ODF1.2 spec defines the syntax
LINEST( Array knownY [ ; [ Array knownX ] [ ; Logical Const = TRUE() [ ;
Logical Stats = FALSE() ] ] ] )

With this syntax the formula
=LINEST(B2:B6;A2:A6;;TRUE())
is not allowed, but currently it gives no error (That is issue106118).

And the formula
=LINEST(B2:B6;;TRUE();TRUE())
is allowed, but currently gives an error.

I have tried to detect this cases using the method IsMissing():
In interpr5.cxx
void ScInterpreter::CalulateRGPRKP(BOOL _bRKP)
{
     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er",
"ScInterpreter::CheckMatrix" );
     BYTE nParamCount = GetByte();
     if ( !MustHaveParamCount( nParamCount, 1, 4 ) )
         return;
     BOOL bConstant, bStats;
     if (nParamCount == 4)
         bStats = GetBool();
     else
         bStats = FALSE;
     if (nParamCount >= 3)
     {
         if (IsMissing()) // empty third parameter is not allowed
         {
             PushIllegalParameter();
             return;
         }
         else
             bConstant = GetBool();
     }
     else
         bConstant = TRUE;
     ScMatrixRef pMatX;
     ScMatrixRef pMatY;
     if (nParamCount >= 2)
     {// In ODF1.2 empty second parameter is allowed
         if (IsMissing())
         {
             Pop();
             pMatX = NULL;
         }
         else
             pMatX = GetMatrix();
     }

The method IsMissing() works so far, that the then-case is reached. But
I get an error popup
        ConvertMatrixParameters: not a push
        From File C:/DEV300m84my/sc/source/core/tool/interpr4.cxx at Line 1444

If I insert a DBG_ERRORFILE() as first statement in the then-case, then
I notice that first the popup "not a push" comes and after it, when I
ignore it, my own message comes up.

If I ignore the messages, the calculation finishes correctly.

Can you please teach me what to do?

Kind regards
Regina



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Missing parameter in LINEST

Eike Rathke-6
Hi Regina,

Sorry for not having answered your previous mail about whether someone
was already working on LINEST. No one is. Thanks for taking that.

On Tuesday, 2010-08-03 20:27:56 +0200, Regina Henschel wrote:

> the draft ODF1.2 spec defines the syntax
> LINEST( Array knownY [ ; [ Array knownX ] [ ; Logical Const = TRUE()
> [ ; Logical Stats = FALSE() ] ] ] )
>
> With this syntax the formula
> =LINEST(B2:B6;A2:A6;;TRUE())
> is not allowed, but currently it gives no error (That is issue106118).

The interpreter allows these cases because in Excel the missing
parameter is allowed at most places and that's needed for imported
documents. When writing to ODFF we'd need to handle that in
formula/source/core/api/token.cxx methods
MissingConvention::isRewriteNeeded() and
FormulaMissingContext::AddMissing()

For ODFF there may be a general approach necessary that normally
rewrites a missing parameter and allows missing parameters only for
specific functions and parameters. Best approach probably would be to
have a table of OpCodes and parameters. This should not be your concern
now, we'll have to do that later though.


> And the formula
> =LINEST(B2:B6;;TRUE();TRUE())
> is allowed, but currently gives an error.
>
> I have tried to detect this cases using the method IsMissing():
> In interpr5.cxx
> [...]
>     if (nParamCount >= 2)
>     {// In ODF1.2 empty second parameter is allowed
>         if (IsMissing())
>         {
>             Pop();
>             pMatX = NULL;
>         }
>         else
>             pMatX = GetMatrix();
>     }
>
> The method IsMissing() works so far, that the then-case is reached.
> But I get an error popup
> ConvertMatrixParameters: not a push
> From File C:/DEV300m84my/sc/source/core/tool/interpr4.cxx at Line 1444
ConvertMatrixParameters() isn't prepared for missings.. at line 1442
change

        if ( p->GetOpCode() != ocPush )

to

        if ( p->GetOpCode() != ocPush && p->GetOpCode() != ocMissing )

that should do (untested).

  Eike

--
 OOo Calc core developer. Number formatter stricken i18n transpositionizer.
 Signature key 0x87F8D412 : 2F58 5236 DB02 F335 8304  7D6C 65C9 F9B5 87F8 D412
 OpenOffice.org Engineering at Oracle: http://blogs.sun.com/GullFOSS
 --
 Please don't send mail to the old [hidden email] account that I used for mailing
 lists, it will phase out shortly. Use [hidden email] instead. Thanks.

attachment0 (200 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Missing parameter in LINEST

Regina Henschel
Hello Eike,

Eike Rathke schrieb:

> Hi Regina,
>
> Sorry for not having answered your previous mail about whether someone
> was already working on LINEST. No one is. Thanks for taking that.
>
> On Tuesday, 2010-08-03 20:27:56 +0200, Regina Henschel wrote:
>
>> the draft ODF1.2 spec defines the syntax
>> LINEST( Array knownY [ ; [ Array knownX ] [ ; Logical Const = TRUE()
>> [ ; Logical Stats = FALSE() ] ] ] )
>>
>> With this syntax the formula
>> =LINEST(B2:B6;A2:A6;;TRUE())
>> is not allowed, but currently it gives no error (That is issue106118).
>
> The interpreter allows these cases because in Excel the missing
> parameter is allowed at most places and that's needed for imported
> documents. When writing to ODFF we'd need to handle that in
> formula/source/core/api/token.cxx methods
> MissingConvention::isRewriteNeeded() and
> FormulaMissingContext::AddMissing()

I think, that the interpreter should not allow invalid (in respect to
ODF) formulas, but complain about wrong syntax. Instead the import
filter should add the missing parameter. The third parameter has an ODF
default value "true" and Excel calculates with this missing parameter as
if it is "true". Therefore the Excel import filter should add a "true".
Issue 106118 is about the fact, that our interpreter does not default to
"true" but to "false". From a technical view it is no problem to set it
to "true" in the LINEST method, but I think it would be the wrong way.

>
> For ODFF there may be a general approach necessary that normally
> rewrites a missing parameter and allows missing parameters only for
> specific functions and parameters. Best approach probably would be to
> have a table of OpCodes and parameters. This should not be your concern
> now, we'll have to do that later though.
>
>
>> And the formula
>> =LINEST(B2:B6;;TRUE();TRUE())
>> is allowed, but currently gives an error.
>>
>> I have tried to detect this cases using the method IsMissing():
>> In interpr5.cxx
>> [...]
>>      if (nParamCount>= 2)
>>      {// In ODF1.2 empty second parameter is allowed
>>          if (IsMissing())
>>          {
>>              Pop();
>>              pMatX = NULL;
>>          }
>>          else
>>              pMatX = GetMatrix();
>>      }
>>
>> The method IsMissing() works so far, that the then-case is reached.
>> But I get an error popup
>> ConvertMatrixParameters: not a push
>> From File C:/DEV300m84my/sc/source/core/tool/interpr4.cxx at Line 1444
>
> ConvertMatrixParameters() isn't prepared for missings.. at line 1442
> change
>
>          if ( p->GetOpCode() != ocPush )
>
> to
>
>          if ( p->GetOpCode() != ocPush&&  p->GetOpCode() != ocMissing )
>
> that should do (untested).

I have changed it and it works for the LINEST method. Is this a valid
solution or might it breaks something in other places?

Kind regards
Regina

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Missing parameter in LINEST

Eike Rathke-6
Hi Regina,

On Thursday, 2010-08-05 15:26:07 +0200, Regina Henschel wrote:

> >The interpreter allows these cases because in Excel the missing
> >parameter is allowed at most places and that's needed for imported
> >documents.
>
> I think, that the interpreter should not allow invalid (in respect
> to ODF) formulas, but complain about wrong syntax. Instead the
> import filter should add the missing parameter.

Well, yes, of course you're right, in theory ;-)  However, with 2 or
3 import filters just for the different Excel file formats this would be
even more work than keeping the interpreter flexible and assuring
correct syntax for ODFF storage.

Or, Daniel, in case you're reading this, what is your opinion?

It would be an easier step to default missing parameters during formula
compilation, but that would work only for UI, PODF and hopefully OOXML
imports, not for binary file formats.

> The third parameter has an ODF default value "true" and Excel
> calculates with this missing parameter as if it is "true". Therefore
> the Excel import filter should add a "true". Issue 106118 is about the
> fact, that our interpreter does not default to "true" but to "false".
> From a technical view it is no problem to set it to "true" in the
> LINEST method, but I think it would be the wrong way.

We already do that in some places, see usage of
ScInterpreter::GetDoubleWithDefault(), here it would be
::rtl::math::approxFloor( GetDoubleWithDefault( 1.0))
We should actually introduce a ScInterpreter::GetBoolWithDefault() for
these cases..


> >ConvertMatrixParameters() isn't prepared for missings.. at line 1442
> >change
> >
> >         if ( p->GetOpCode() != ocPush )
> >
> >to
> >
> >         if ( p->GetOpCode() != ocPush&&  p->GetOpCode() != ocMissing )
> >
> >that should do (untested).
>
> I have changed it and it works for the LINEST method. Is this a
> valid solution or might it breaks something in other places?
This is a valid general solution. A missing parameter doesn't have to be
converted, which is handled in the switch case svMissing that follows.

  Eike

--
 OOo Calc core developer. Number formatter stricken i18n transpositionizer.
 Signature key 0x87F8D412 : 2F58 5236 DB02 F335 8304  7D6C 65C9 F9B5 87F8 D412
 OpenOffice.org Engineering at Oracle: http://blogs.sun.com/GullFOSS
 --
 Please don't send mail to the old [hidden email] account that I used for mailing
 lists, it will phase out shortly. Use [hidden email] instead. Thanks.

attachment0 (200 bytes) Download Attachment