search

ycphs / openxlsx

openxlsx - a fast way to read and write complex xslx files
https://ycphs.github.io/openxlsx/
Other
221 stars 74 forks source link

Adding @ for formulas #352

Closed klar-C closed 2 years ago

klar-C commented 2 years ago

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 commented 2 years ago

Realized this is a duplicate of https://github.com/awalker89/openxlsx/issues/519 Apologies.

JanMarvin commented 2 years ago

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

klar-C commented 2 years ago

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

klar-C commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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.