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?


Could you try the attached version/fix ( 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!