The code you show should work. I fleshed it out to a working example(*) and it shows the correct MAX and AVERAGE for valid cell ranges. If the cell range is empty then the result will be zero/#DIV0! like the corresponding Excel formula:

    #!/usr/bin/perl

    use strict;
    use warnings;
    use Spreadsheet::WriteExcel;
    use Spreadsheet::WriteExcel::Utility;

    my $workbook  = Spreadsheet::WriteExcel->new( 'test.xls' );
    my $worksheet = $workbook->add_worksheet();

    $worksheet->write( 'B2', 3 );
    $worksheet->write( 'B3', 5 );


    my @cols = ( 1, 2 );
    my $row  = 3;

    my $i = 1;

    for ( @cols ) {
        my $s = xl_rowcol_to_cell( 1,        $i );
        my $e = xl_rowcol_to_cell( $row - 1, $i );

        my $range = "$s:$e";
        print "$i:Range:$range\n";
        $worksheet->write_formula( $row,     $i, '=MAX(' . $range . ')
+' );
        $worksheet->write_formula( $row + 1, $i, '=AVERAGE(' . $range 
+. ')' );

        $i++;
    }


    __END__
[download]

Spreadsheet::WriteExcel does sometimes mis-parse complex formulae resulting in an invalid 0 result. However, that isn't the case here.

* You should have done this when asking the questing. It only takes a few extra lines of code.

--
John.

It seems that formulas inserted using WriteExcel can't be read by Excel Viewer. Is that a problem with the viewer or the module?
Rohit Raghunathan

Is that a problem with the viewer or the module?

A little bit of both.

Spreadsheet::WriteExcel writes formulas in the required binary RPN format but it doesn't write the result of the formula since it wouldn't be practical to calculate the result of arbitrary formulae. So instead it writes 0 as a default formula result. Excel and most other spreadsheet applications recalculate the result of formulas when the file is loaded so that generally isn't an issue.

However, Excel Viewer doesn't calculate formulae so all you see is the default 0 result.

You can work around this by explicitly specifying the calculated value of the formula at the end of the argument list:

    $worksheet->write_fromula('A1', '=2+2', $format, 4);

    # Or:

    $worksheet->write('A1', '=2+2', $format, 4);
[download]

--
John.

rohit_raghu:

Well, when you look at the spreadsheet, do the formulas you want actually make it into the workbook? Do the cells contain the data you want? Are the values anything other than zero? You provide no information and only a fragment of your code, so all I can do is ask questions. Hopefully, one or more will prove useful.

When debugging, be sure to understand the problem. Then question all your assumptions. The hard part is to recognize all the assumptions you're making.

...roboticus

When your only tool is a hammer, all problems look like your thumb.