writeFormula adding an "@" symbol between formulas

[wwyws0000]

Hi, I'm trying to write a formula to the spreadsheet that takes the sum of absolute values of a range (say, cell A2:A11).

I.e, the desired formula is SUM(ABS(A2:A11))

Expected Behavior

Using writeFormula(), I expected the resulting formula to be exactly that

Actual Behavior

However, the actual formula in the resulting Excel spreadsheet is =SUM(ABS(@ A2:A10))

There's a "@" that came out of nowhere and it's messing up the calculation

This is likely due to interactions with Excel's spill/dynamic array features.

Steps to Reproduce the Problem

wb <- createWorkbook()
addWorksheet(wb,'Test')
testMat <- matrix(rep(10,10),10,1)
writeData(wb,'Test',testMat)

testFormula <- 'SUM(ABS(A2:A11))'
writeFormula(wb,'Test',testFormula,startCol = 1,startRow = 13)

saveWorkbook(wb,'test.xlsx',overwrite = T)

sessionInfo()

R version 3.6.2 (2019-12-12) Platform: x86_64-w64-mingw32/x64 (64-bit) Running under: Windows 10 x64 (build 17763)

openxlsx_4.2.2

Excel: Microsoft Office 365 ProPlus

[ycphs]

Could you please create this also at ycphs/openxlsx?

I am maintaining the active fork of the package.

[wwyws0000]

Yep I submitted another post there. Thank you!