mark4444az has asked for the wisdom of the Perl Monks concerning the following question:

I'm trying to repeat a formula in Excel using the cpan WriteExcel perl module. The formula is "=B1-B2"; I want it to go down the "C" column like this: =B1-B2, =B2-B3, =B3-B4..etc all the way down the column. While this is simple to do in Excel manually, I am trying to automate this in a perl script and having some difficulty. The line of code in question is: $worksheet1->write($row_count3, ('=B'.$line_count - 'B'.($line_count_plus_1))); $rowcount3 works fine, it basically gives the column/row number, as in C1, C2, etc. The Formula itself is just subtracting $line_count - $line_count_plus_1. My line count variables are ok, The error is in how I am writing the formula. Can anyone help?

Replies are listed 'Best First'.
Re: repeat excel formula
by kennethk (Abbot) on May 22, 2014 at 17:16 UTC 
    '=B'.$line_count - 'B'.($line_count_plus_1)

    [download]
    should be 
    '=B'.$line_count .' - B'.($line_count_plus_1)

    [download]
    or 
    "=B$line_count - B$line_count_plus_1"

    [download]
    Your minus sign is outside your quotes - you're subtracting in Perl, not Excel. This would have gotten caught if were using warnings. See Use strict warnings and diagnostics or die.

    I'd probably use sprintf instead of creating a one-off variable: 

    sprintf '=B%d - B%d', $line_count, $line_count-1

    [download]

    #11929 First ask yourself `How would I do this without a computer?' Then have the computer do it the same way.

[reply]
[d/l]
[select]
      I see, that works. I will include "use warnings". Thank you very much.
[reply]

Back to Seekers of Perl Wisdom