# Thread: MS Excel Writer - formula problems

1. Senior Member
Join Date
Feb 2011
Posts
840

## MS Excel Writer - formula problems

So, new week, new problems needing help =p I'm playing now with formulas on Excel Writer output... but I can't seem to get it right.

First few problems were kinda dumb, actually... MS in PT-BR uses ; instead of , to separate arguments inside functions, but that was easy to find out. Translation of functions from pt-br to en was also easy to find... but now I'm getting a strange message, but searching google for it is returning me almost only this source code.

Formulas used:
Code:
```VLOOKUP("100",\$B:\$F,5,0) - VLOOKUP("105",\$B:\$F,5,0)

ROUND(VLOOKUP("100",PR!B:F,5,0),2)

IF(VLOOKUP("960",'ATIVO PERMANENTE - LIMITE'!B:D,3,0)>=0,0,ABS(VLOOKUP("960",'ATIVO PERMANENTE - LIMITE'!B:D,3,0)))

VLOOKUP("720",\$B:\$F,5,0) + VLOOKUP("800",\$B:\$F,5,0) + VLOOKUP("810",\$B:\$F,5,0) + VLOOKUP("820",\$B:\$F,5,0) + VLOOKUP("830",\$B:\$F,5,0) + VLOOKUP("840",\$B:\$F,5,0) + VLOOKUP("850",\$B:\$F,5,0) + VLOOKUP("860",\$B:\$F,5,0) + VLOOKUP("870",\$B:\$F,5,0) + VLOOKUP("880",\$B:\$F,5,0)

VLOOKUP("720",PEPR!\$B:\$K,10,0)

VLOOKUP("870",POPR!B15:N49,12,0)

VLOOKUP("100",\$B:\$F,5,0)*0.11/VLOOKUP("900",\$B:\$F,5,0)

VLOOKUP("890",RBAN!B7:H26,7,0)

```
Errors received, running (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy):
Code:
```ERROR : Unexpected errorERROR : java.lang.IllegalStateException: evaluation stack not empty
ERROR :     at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:541)
ERROR :     at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288)
ERROR :     at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:702)
ERROR :     at org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:51)
ERROR :     at org.apache.poi.ss.formula.LazyAreaEval.getRelativeValue(LazyAreaEval.java:51)
ERROR :     at org.apache.poi.ss.formula.eval.AreaEvalBase.getValue(AreaEvalBase.java:109)
ERROR :     at org.apache.poi.ss.formula.functions.LookupUtils\$ColumnVector.getItem(LookupUtils.java:99)
ERROR :     at org.apache.poi.ss.formula.functions.Vlookup.evaluate(Vlookup.java:61)
ERROR :     at org.apache.poi.ss.formula.functions.Var3or4ArgFunction.evaluate(Var3or4ArgFunction.java:36)
ERROR :     at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:132)
ERROR :     at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:525)
ERROR :     at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288)
ERROR :     at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateReference(WorkbookEvaluator.java:702)
ERROR :     at org.apache.poi.ss.formula.SheetRefEvaluator.getEvalForCell(SheetRefEvaluator.java:51)
ERROR :     at org.apache.poi.ss.formula.LazyAreaEval.getRelativeValue(LazyAreaEval.java:51)
ERROR :     at org.apache.poi.ss.formula.eval.AreaEvalBase.getValue(AreaEvalBase.java:109)
ERROR :     at org.apache.poi.ss.formula.functions.LookupUtils\$ColumnVector.getItem(LookupUtils.java:99)
ERROR :     at org.apache.poi.ss.formula.functions.Vlookup.evaluate(Vlookup.java:61)
ERROR :     at org.apache.poi.ss.formula.functions.Var3or4ArgFunction.evaluate(Var3or4ArgFunction.java:36)
ERROR :     at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:132)
ERROR :     at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:525)
ERROR :     at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288)
ERROR :     at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:230)
ERROR :     at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCellValue(XSSFFormulaEvaluator.java:264)
ERROR :     at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCell(XSSFFormulaEvaluator.java:151)
ERROR :     at org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.recalculateAllWorkbookFormulas(ExcelWriterStep.java:240)
ERROR :     at org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.closeOutputFile(ExcelWriterStep.java:217)
ERROR :     at org.pentaho.di.trans.steps.excelwriter.ExcelWriterStep.processRow(ExcelWriterStep.java:172)

2. Senior Member
Join Date
Feb 2011
Posts
840
wow, 9 months later I got stuck with this problem again and still no answer =( bump?

3. Junior Member
Join Date
Apr 2013
Posts
15
Hello, you could try using the latest apache POI. This may solve your problem.

http://poi.apache.org/index.html

4. Senior Member
Join Date
Feb 2011
Posts
840
latest, you mean, dev/unstable? coz PDI 5 already comes with POI version 3.9

anyway... tried 3.10 beta 2 just now, I'm still getting the dreaded "evaluation stack not empty" error.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•