So, TREND() isn't implemented. Why don't I get a NotImplementedException then ?
I'll see if I can knock up a quick implementation to contribute. On 24 November 2015 at 13:43, Tom Chiverton <[email protected]> wrote: > It looks like TREND() is causing the error. > > The two lookups inside it run find by themselves through POI, so the > SMALL/LARGE and COUNTIF must all be working too. > > I admit to not having seen this syntax of IF({1,0},... before. Is TREND > not implemented, or could I try a different syntax for the look up ? > > Tom > > On 24 November 2015 at 13:02, Tom Chiverton <[email protected]> > wrote: > >> Great plan, and I can dump out the values as I go too. >> >> I have narrowed (at least one of the errors) down to formula's of the >> pattern below. I think it returns an exact match from the VLOOKUP if one >> exists, otherwise it takes the immediate before and after rows and looks it >> up via TREND. >> >> Is some of this maybe not implemented in POI yet ? >> >> IF( ISNUMBER(MATCH(isk,$A$4:$A$105,0)), >> VLOOKUP(isk,$A$4:$B$105,B2,0), >> TREND( >> IF({1,0}, >> >> VLOOKUP(SMALL($A$4:$A$105,COUNTIF($A$4:$A$105,"<"&isk)),$A$4:$B$105,B2,0), >> >> VLOOKUP(LARGE($A$4:$A$105,COUNTIF($A$4:$A$105,">"&isk)),$A$4:$B$105,B2,0)), >> IF({1,0}, >> SMALL($A$4:$A$105,COUNTIF($A$4:$A$105,"<"&isk)), >> LARGE($A$4:$A$105,COUNTIF($A$4:$A$105,">"&isk)) >> ), >> isk >> ) >> ) >> >> On 24 November 2015 at 12:01, Nick Burch <[email protected]> wrote: >> >>> On Tue, 24 Nov 2015, Tom Chiverton wrote: >>> >>>> I am trying to evaluate all the formula's in an Excel file. There are >>>> about >>>> a dozen sheets, with several tens of formula on each, all driven by a >>>> few >>>> input fields on the first sheet. >>>> This all works fine in Excel 365 itself. >>>> >>>> However, when I try and run it via the latest POI, >>>> evaluateAllFormulaCells() is throwing "Unexpected ptg class >>>> (org.apache.poi.ss.formula.ptg.ArrayPtg)". >>>> >>>> What's the best way to track this down ? >>>> >>> >>> Do the same logic as that method does - loop over the sheets, then the >>> rows, then the cells, and evaluate each cell one at a time. Use that to >>> identify which cell, and hence which formula is the problem >>> >>> Nick >>> >>> --------------------------------------------------------------------- >>> To unsubscribe, e-mail: [email protected] >>> For additional commands, e-mail: [email protected] >>> >>> >> >> >> -- >> Tom >> > > > > -- > Tom > -- Tom
