Excel::Writer::XLSX adds an unexpected @ in formula (part 2)

user_3983601 0
Viewed 105

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 @ ?

writer formula xlsx perl excel
1 Answers

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();
user_39836010
Related