Adding @ for formulas

[klar-C]

Hi,

When writing formulas with named ranges, sometimes it adds an "@" in front of the name even though that's not how it was written to openxlsx.

E.g.
SUMPRODUCT((IF(NOT(ISERROR(price)),price,0))(price))
would switch to
SUMPRODUCT((IF(NOT(ISERROR(@price)),price,0))(price))

This would not happen if I don't have the "IF(NOT(..." around it.

I'm not sure whether this is Excel (when opening the file) or openxlsx.

Will add a reprex asap but maybe someone has come across this before.

[klar-C]

Realized this is a duplicate of
awalker89/openxlsx#519
Apologies.

[JanMarvin]

I've never been able to reproduce the issue. Could you check if the steps to reproduce still apply?

[klar-C]

Following his instructions under awalker89/openxlsx#519 I'm able to reproduce it. The random "@" sign shows up for me.

[klar-C]

To be clear: Doing array=T solves the issue.

But it would be good to know why it actually happens, because in other sections I'm using explicit @-signs in formulas like @RANGE_XYZ-SUM(RANGE_ABC) and there I don't think I can use the array=T clause.

[JanMarvin]

I'm rather clueless myself, there is no instruction to add @ in openxlsx, therefore the sign must be added for other unknown reasons. What should happen: the formula is written as is, though for whatever reason it is mixed up. Might be some windows problem, but could be completely unrelated.

[JanMarvin]

I did some research on the topic. The issue is caused by ABS() being an array function. Even tough Excel does not provide the curly brackets, the function itself remains an array function and is written as such. The difference compared to other array functions is in the way Excel hides this from the user. Excel applies something called a cell meta index, a specific value which picks a certain value from metadata.xml.