Re: Unexpected eval type (also Google Sheets question)
On Mon, 1 Dec 2014, Blake Watson wrote: Got a formula with a pair of ranges: =IFERROR(MATCH(TRUE,OFFSET(Sh1!$L$27,I20,0):'Sh1'!$L$38,0)+I20,false) Can you try breaking that down into individual bits, to see where the problem comes in? So try just the offset bit, then the match with constants, then match with offset, and finally the whole thing. We want to know which is the bit that's objecting to the error result Nick - To unsubscribe, e-mail: user-unsubscr...@poi.apache.org For additional commands, e-mail: user-h...@poi.apache.org
Re: Unexpected eval type (also Google Sheets question)
There seems to be two problems (testing this in Google Sheets): MATCH doesn't like having the page expressed on the end part of the range. This does not work: =MATCH(TRUE,Sh1!$L$27:Sh1!$L$38,0) But this does: =MATCH(TRUE,Sh1!$L$27:$L$38,0) Also, MATCH does not like having OFFSET specifying the starting part of the range. So, this (removing the sheet specification): =(MATCH(TRUE,OFFSET($L$27,I20,0):$L$38,0)) Does not work. Neither does SUM: =SUM(OFFSET($L$27,I20,0):$L$38,0) Suggesting the problem is using OFFSET. The user's goal (in case there's a better way to express it) starting from this: Reason1 True Reason2 False Reason3 False Reason4 True Reason5 True Is to populate a different list like so: Reason1 Reason4 Reason5 In other words, the TRUEs from the reason list create a new list of only applicable reasons, with no gaps. On Tue, Dec 2, 2014 at 7:11 AM, Nick Burch apa...@gagravarr.org wrote: On Mon, 1 Dec 2014, Blake Watson wrote: Got a formula with a pair of ranges: =IFERROR(MATCH(TRUE,OFFSET(Sh1!$L$27,I20,0):'Sh1'!$L$38,0)+I20,false) Can you try breaking that down into individual bits, to see where the problem comes in? So try just the offset bit, then the match with constants, then match with offset, and finally the whole thing. We want to know which is the bit that's objecting to the error result Nick - To unsubscribe, e-mail: user-unsubscr...@poi.apache.org For additional commands, e-mail: user-h...@poi.apache.org -- *Blake Watson* *PNMAC* Application Development Manager 5898 Condor Drive Moorpark, CA 93021 (805) 330.4911 x7742 blake.wat...@pnmac.com melanie.petros...@pnmac.com www.PennyMacUSA.com http://www.pennymacusa.com/
Re: Unexpected eval type (also Google Sheets question)
By all, I mean: Nick. Reminding me that I could parse the formula in a debugger and examine the results is what clued me in. On Tue, Dec 2, 2014 at 1:59 PM, Blake Watson blake.wat...@pnmac.com wrote: OK, false alarm. Everything's parsing fine. The problem was somewhere else entirely. Thanks all for the help. -- *Blake Watson* *PNMAC* Application Development Manager 5898 Condor Drive Moorpark, CA 93021 (805) 330.4911 x7742 blake.wat...@pnmac.com melanie.petros...@pnmac.com www.PennyMacUSA.com http://www.pennymacusa.com/
Re: Unexpected eval type (also Google Sheets question)
OK, false alarm. Everything's parsing fine. The problem was somewhere else entirely. Thanks all for the help.
Unexpected eval type (also Google Sheets question)
Got a formula with a pair of ranges: =IFERROR(MATCH(TRUE,OFFSET(Sh1!$L$27,I20,0):'Sh1'!$L$38,0)+I20,false) Evaluating it returns: Unexpected eval type (org.apache.poi.ss.formula.eval.ErrorEval) I found this suggesting it's a parsing problem: http://stackoverflow.com/questions/19517141/apache-poi-exception-while-calculating-formulae Which references a 5 1/2 year old fix: http://svn.apache.org/viewvc?view=revisionrevision=762250 I'm currently running on 3.11-beta2. So: Is POI having trouble parsing this? Would the referenced patch fix it? If so, why wouldn't it be in 3.11? Does Google Sheets use POI? (I ask because it errors out on the formula, too, and says Parse formula error.) Thoughts? -- *Blake Watson* *PNMAC* Application Development Manager 5898 Condor Drive Moorpark, CA 93021 (805) 330.4911 x7742 blake.wat...@pnmac.com melanie.petros...@pnmac.com www.PennyMacUSA.com http://www.pennymacusa.com/
