Free Support Forum - aspose.com

Array formula

Hi

I try to use array formula but I get an #VALUE error when I open the workbook. The formula is ok since when I edit the formula in the Excel formula bar, it recalculates correctly.

The formulas I use looks like...

r.SetArrayFormula("=SUM(IF($A$10:$A$13=4,IF($I$10:$I$13<>\"\",IF($F$10:$F$13<>-1,$I$10:$I$13*$D$10:$D$13,0),0),0))*$D9",1,1)

I attached an example. What can i do to solve this problem?

thank you

Alexandre

Could you post a file manually edited in MS Excel? I tried to edit your formula in MS Excel but get same #VALUE error.

Here it is.

I edited (ctrl+shift+enter in each cell) the formulas in column I and also in the column D (hidden column)

Thank you

Please try this attached version.

It worked for the formulas in the D column but I still have the #VALUE error in the other columns.

Thank you

Please try this v4.0.1.2.

I still have the #VALUE error.

Please try this one.

I don't have any #VALUE errors now but it doesn't calculate correctly. It seems it only takes the first cell of the range. For example in the cell I18 it should be -158 115.4, not -10 000.0

Thank you

Please try this attached version.

Thank you very much, it works!

I found a bug in the calculation of an array formula.

When I am in the column AA and after, I have a #Value error.

Thank you.

It’s a strange problem. How do you set array formulas after column AA?

I use the SetArrayFormula, I set the formula cell by cell, from the first column to the last column.

I found and fixed this problem. Please try this attached fix.

I have a new problem with the array formula in columns AA and after, but this time I have the #Value error only when the first cell of the range is empty.

I attached a sample file, in the row 12 there is some #Value errors, from column AA to BD.

Thank you

Hi,

Thanks for considering Aspose.

Well, I could not find your attachment sample file. could you please re-send your file again and which version of Aspose.Cells you are using?

Regards

Amjad Sahi

Aspose Nanjing Team

Please try this attached fix.

Thank you Laurence, it’s fixed.