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?
