4

This question follows a first question : Excel::Writer::XLSX adds an unexpected @ in formula

I am writing a formula to an xlsx file using Excel::Writer::XLSX

While I am using _xlfn, I do not have the expected result when I try to write a complex formula. Here is a code showing the issue:

use Excel::Writer::XLSX;
my $workbook  = Excel::Writer::XLSX->new( "test.xlsx" );
my $worksheet = $workbook->add_worksheet();
$worksheet->write( 'B1', "5");
$worksheet->write( 'A1', "4");
$worksheet->write( 'A2', "5");
$worksheet->write( 'A3', "3");
$worksheet->write( 'A4', "4");
$worksheet->write( 'A5', "6");
$worksheet->write( 'A7', "=_xlfn.STDEV.P(IF((A1:A5<B1),A1:A5))");
$workbook ->close();

Then the cell A7 contains: =STDEV.P(IF((@A1:A5<B1),A1:A5))

And because of the @, the cell shows #NAME? instead of the result of the formula.

Does anybody know how to remove this unexpected @ ?

toolic
  • 57,801
  • 17
  • 75
  • 117
Jean-Marc
  • 133
  • 1
  • 9
  • I'm leaving this here because I had no idea what the `@` could. Seems like a newish Excel feature, and something is going wrong. It's probably a _structured reference_: https://support.microsoft.com/en-us/office/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e – simbabque Nov 05 '20 at 13:46
  • 3
    Guessing it's the [implicit intersection operator](https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34) actually. – BigBen Nov 05 '20 at 14:00
  • 3
    Don't know anything about `xlsxwriter` but since this should be an array formula to begin with, maybe use: `"{=_xlfn.STDEV.P(IF((A1:A5 – JvdV Nov 05 '20 at 14:26

1 Answers1

4

Thanks JvdV, the solution is indeed to use {}

use Excel::Writer::XLSX;
my $workbook  = Excel::Writer::XLSX->new( "test.xlsx" );
my $worksheet = $workbook->add_worksheet();
$worksheet->write( 'B1', "5");
$worksheet->write( 'A1', "4");
$worksheet->write( 'A2', "5");
$worksheet->write( 'A3', "3");
$worksheet->write( 'A4', "4");
$worksheet->write( 'A5', "6");
$worksheet->write( 'A7', "{=_xlfn.STDEV.P(IF((A1:A5<B1),A1:A5))}");
$workbook ->close();
Jean-Marc
  • 133
  • 1
  • 9