Evaluator resulting in "Unexpected ptg class" error message

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

Evaluator resulting in "Unexpected ptg class" error message

russsur
I have a spreadsheet that I am trying to recalculate through POI. There are a
number of formulas that are working properly, but there is one formula that
results in this error message. Here is a version of the formula that is
failing:

=SUM(COUNTIFS(QueryResults!$N$2:$N$186,{"No
Run",""},QueryResults!$F$2:$F$186,$D7,QueryResults!$Q$2:$Q$186,$B$4))

The formula works when I manually force recalculation.

Is this type of formula supported by the evaluator?

Thanks, Russ



--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html

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

Reply | Threaded
Open this post in threaded view
|

Re: Evaluator resulting in "Unexpected ptg class" error message

Greg Woolsey
That formula uses "array syntax" (the curly braces).  Until a commit to
trunk a few days ago POI didn't support any array syntax, so no, released
versions won't support that expression yet.  A custom build from current
SVN/Git trunk might evaluate it - I don't think there is very broad unit
test coverage yet for the wide range of array syntax use cases possible in
Excel.  Being brand-new, it seems quite possible there are some use cases
not yet implemented, but don't know enough about array syntax myself to
speculate.

Feel free to experiment - if you can turn your case into a unit test, we'd
be glad to incorporate it, and any patches it might uncover!

On Tue, Dec 19, 2017 at 11:17 AM russsur <[hidden email]> wrote:

> I have a spreadsheet that I am trying to recalculate through POI. There
> are a
> number of formulas that are working properly, but there is one formula that
> results in this error message. Here is a version of the formula that is
> failing:
>
> =SUM(COUNTIFS(QueryResults!$N$2:$N$186,{"No
> Run",""},QueryResults!$F$2:$F$186,$D7,QueryResults!$Q$2:$Q$186,$B$4))
>
> The formula works when I manually force recalculation.
>
> Is this type of formula supported by the evaluator?
>
> Thanks, Russ
>
>
>
> --
> Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Evaluator resulting in "Unexpected ptg class" error message

russsur
Hi Greg,

Thanks for the info. I worked around this issue by simply adding sums
together for each of the array options:

=SUM(COUNTIFS(QueryResults!$N$2:$N$186,"No
Run",QueryResults!$F$2:$F$186,$D7,QueryResults!$Q$2:$Q$186,$B$4)) +
SUM(COUNTIFS(QueryResults!$N$2:$N$186,"",QueryResults!$F$2:$F$186,$D7,QueryResults!$Q$2:$Q$186,$B$4))

The recalculation is working fine after changing this.

Thanks,

Russ



--
Sent from: http://apache-poi.1045710.n5.nabble.com/POI-User-f2280730.html

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