JXL solve #VALUE problem

Question

I want to use AVERAGE function, but when I have a reference cell which happens to be a label I get #VALUE as the output.

I have attached the sample piece of code for what I am trying to do:

String filename = "C:\\input.xls";     
WorkbookSettings ws = new WorkbookSettings();      
ws.setLocale(new Locale("en", "EN"));      
WritableWorkbook workbook = Workbook.createWorkbook(new File(filename), ws);     
WritableSheet s1 = workbook.createSheet("Output", 0);     
s1.addCell(new Number(1,2,6));      
s1.addCell(new Number(3, 1, 6));       
s1.addCell(new Number(3, 2, 1));      
s1.addCell(new Number(3, 3, 6));      
s1.addCell(new Label(3, 4, ""));      
Formula formula = new 
 Formula(3,5,"AVERAGE(Output!D1,Output!D2,Output!D3,Output!D4,Output!D5)");      
s1.addCell(formula);  
workbook.write();
workbook.close();

I cannot convert the empty cell to 0 since the AVG value will change.

JAR USED

jxl-2.6.jar

In real time instead of Label the value will be used based on a formula

IF(Some-cell-reference="","",some-Value)

However; when I try to edit the cell with key F2 it changes its execution plan and I get the correct output .

Is there any solution available for this....

Expected solution :

To make the cell as empty but change the cell format so that #VALUE is not returned.

This post is slightly related to

JXL #VALUE problem with reference to other sheet

Answer 1

Ok, the first problem I see is that the numbers you're adding in are in column "C" not "D". Maybe you already have numbers in "D", so it's not a problem, but something to consider.

I think the big problem is the way that jxl works the spreadsheet, this other questioner thought that their named range wasn't created before their formula was inserted, so they had it only work when they went "into" the cell, as you did. So it may be that your "Output" sheet is not initializing so there's no valid reference for the Output! references.

Since you're also inserting the Formula in the same sheet, then dump the sheet references and write your line like this:

Formula formula = new Formula(3,5,"AVERAGE(D1:D5)");