We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Aspose.Cells: Using SetFormulaArray gives #VALUE

I'm having a problem using SetFormulaArray. Here is a snippet from the code i'm using:

f = String.Format("=AVERAGE(IF(({0}:{1}=""{2}"")*({3}:{4}>0),{3}:{4}))", _
ws.Cells(itemRow(1) + i - 1, colROWHEADING).Name, _
ws.Cells(itemRow(UBound(itemRow)) + i - 1, colROWHEADING).Name, _
rowMetricName(i), _
ws.Cells(itemRow(1) + i - 1, j).Name, _
ws.Cells(itemRow(UBound(itemRow)) + i - 1, j).Name)

ws.Cells(HEADINGROW + i, j).SetArrayFormula(f, 1, 1)

The formula rendered is:
=AVERAGE(IF((C26:C422="Instock %")*(I26:I422>0),I26:I422))

When opening the file created with this value, the cell shows #VALUE, but its formula looks ok. If I hit F2 on the cell, then type Ctrl-Shift-Enter without alterning the formula, the formula works. Why doesn't it work when I first open the file?

Hi,

Could you try the attached version/fix (4.6.0.10). If you still find the issue, kindly post your generated file here, we will check your issue soon.

Thank you.

It works fine now. Thanks for the quick response!