Formula using RANK

Hi,
If I run the sample included below, it runs OK but when I
open the
workbook in Excel for Windows, the cells in cloumn E show
#VALUE!.
The formulas in each cell look OK.
If you move to one of the cells and press F2 to edit and
then arrow to
the left of the = sign and then press BACKSPACE and then
RETURN the
cell calculates OK.
Any idea what is going on here?
Thanks.

#!/usr/bin/perl -w
use strict;
use Spreadsheet::WriteExcel;
my $workbook    =
Spreadsheet::WriteExcel->new('test.xls');
my $worksheet   = $workbook->add_worksheet();
# # Simulate CSV conversion
 my $rows;
#
 while (<DATA>){
     next unless /S/;
         chomp;
      my row = split ',';
      push $rows, row
 }
# Write the data.
 my $row = 0;
#
for my $record ($rows) {
            $worksheet->write_row($row++, 0, $record);
 $worksheet->write_formula($row-1,4,"=RANK(C$row,C1:
C4)");
 }
#
__DATA__
100,200,300,400
200,300,400,100
300,400,500,600
1,2000,4,5

--~--~---------~--~----~------------~-------~--~----~
--
You received this message because you are subscribed to the
Spreadsheet::WriteExcel Google Group.

For posting and other options visit this group at:
http://groups.google.com/group/spreadsheet-writeexcel
?hl=en

You can also post by sending an email to:
http://groups.google.com/group/spreadsheet-writeexcel
?hl=en 

To unsubscribe send an email to
spreadsheet-writeexcel-unsubscribegooglegroups.com
-~----------~----~----~----~------~----~------~--~---

Hi bob

I am guessing this is one of those cases talked about a
while back where
Excel doesn't calculate the cell value before displaying
it.

John added a facility to specify the value of a formula in
the
write_formula call but you need to manually work out the
value yourself
:(

WriteExcelXML works 
 

-----Original Message-----
From: spreadsheet-writeexcelgooglegroups.com
[mailto:spreadsheet-writeexcelgooglegroups.com] On Behalf
Of bobjw
Sent: Tuesday, 18 December 2007 3:04 AM
To: Spreadsheet::WriteExcel
Subject: [Spreadsheet::WriteExcel] Formula using RANK


Hi,
If I run the sample included below, it runs OK but when I
open the
workbook in Excel for Windows, the cells in cloumn E show
#VALUE!.
The formulas in each cell look OK.
If you move to one of the cells and press F2 to edit and
then arrow to
the left of the = sign and then press BACKSPACE and then
RETURN the
cell calculates OK.
Any idea what is going on here?
Thanks.

#!/usr/bin/perl -w
use strict;
use Spreadsheet::WriteExcel;
my $workbook    =
Spreadsheet::WriteExcel->new('test.xls');
my $worksheet   = $workbook->add_worksheet();
# # Simulate CSV conversion
 my $rows;
#
 while (<DATA>){
     next unless /S/;
         chomp;
      my row = split ',';
      push $rows, row
 }
# Write the data.
 my $row = 0;
#
for my $record ($rows) {
            $worksheet->write_row($row++, 0, $record);
 $worksheet->write_formula($row-1,4,"=RANK(C$row,C1:
C4)");
 }
#
__DATA__
100,200,300,400
200,300,400,100
300,400,500,600
1,2000,4,5




--~--~---------~--~----~------------~-------~--~----~
--
You received this message because you are subscribed to the
Spreadsheet::WriteExcel Google Group.

For posting and other options visit this group at:
http://groups.google.com/group/spreadsheet-writeexcel
?hl=en

You can also post by sending an email to:
http://groups.google.com/group/spreadsheet-writeexcel
?hl=en

To unsubscribe send an email to
spreadsheet-writeexcel-unsubscribegooglegroups.com
-~----------~----~----~----~------~----~------~--~---

On Dec 17, 4:03 pm, bobjw <rjwit...reinhartfoodservice.com>
wrote:
> Hi,
> If I run the sample included below, it runs OK but when
I open the
> workbook in Excel for Windows, the cells in cloumn E
show #VALUE!.


Hi,

This is one of those formulas that doesn't get parsed
correctly. The
example below demonstrates a workaround to generate a
correctly parsed
formula via store_formula() and repeat_formula():


    #!/usr/bin/perl -w

    use strict;
    use Spreadsheet::WriteExcel;

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

    while (<DATA>){
        next unless /S/;
        chomp;
        my row = split ',';
        push $rows, row
    }


    my $row          = 0;
    my $rank_formula =
$worksheet->store_formula('=RANK(C1,C1:C4)');

    # Work around a parsing error in the formula.
    $rank_formula    = [map {s/_ref2d/_ref2dV/; $_} $rank_formula];


    for my $record ($rows) {
        $worksheet->write_row($row, 0, $record);
        $worksheet->repeat_formula($row, 4,
$rank_formula, undef,
                                   qr/^C1$/, 'C' . ($row
+1));
        $row++;
    }


    __DATA__
    100,200,300,400
    200,300,400,100
    300,400,500,600
    1,2000,4,5


I was going to commend you on your clear example program
until I
realised that it was one of mine. That works for me though.


I think I will make fixing these formula parsing errors a
priority in
the new year.

John.
--

--~--~---------~--~----~------------~-------~--~----~
--
You received this message because you are subscribed to the
Spreadsheet::WriteExcel Google Group.

For posting and other options visit this group at:
http://groups.google.com/group/spreadsheet-writeexcel
?hl=en

You can also post by sending an email to:
http://groups.google.com/group/spreadsheet-writeexcel
?hl=en 

To unsubscribe send an email to
spreadsheet-writeexcel-unsubscribegooglegroups.com
-~----------~----~----~----~------~----~------~--~---

Thanks John and thanks again for all of your work on this
module.

On Dec 17, 5:25 pm, jmcnamara <jmcnam...cpan.org> wrote:
> On Dec 17, 4:03 pm, bobjw <rjwit...reinhartfoodservice.com> wrote:
>
> > Hi,
> > If I run the sample included below, it runs OK but
when I open the
> > workbook in Excel for Windows, the cells in cloumn
E show #VALUE!.
>
> Hi,
>
> This is one of those formulas that doesn't get parsed
correctly. The
> example below demonstrates a workaround to generate a
correctly parsed
> formula via store_formula() and repeat_formula():
>
>     #!/usr/bin/perl -w
>
>     use strict;
>     use Spreadsheet::WriteExcel;
>
>     my $workbook    =
Spreadsheet::WriteExcel->new('test.xls');
>     my $worksheet   = $workbook->add_worksheet();
>     my $rows;
>
>     while (<DATA>){
>         next unless /S/;
>         chomp;
>         my row = split ',';
>         push $rows, row
>     }
>
>     my $row          = 0;
>     my $rank_formula =
$worksheet->store_formula('=RANK(C1,C1:C4)');
>
>     # Work around a parsing error in the formula.
>     $rank_formula    = [map {s/_ref2d/_ref2dV/; $_}
$rank_formula];
>
>     for my $record ($rows) {
>         $worksheet->write_row($row, 0, $record);
>         $worksheet->repeat_formula($row, 4,
$rank_formula, undef,
>                                    qr/^C1$/, 'C' .
($row +1));
>         $row++;
>     }
>
>     __DATA__
>     100,200,300,400
>     200,300,400,100
>     300,400,500,600
>     1,2000,4,5
>
> I was going to commend you on your clear example
program until I
> realised that it was one of mine. That works for me
though. 
>
> I think I will make fixing these formula parsing errors
a priority in
> the new year.
>
> John.
> --
--~--~---------~--~----~------------~-------~--~----~
--
You received this message because you are subscribed to the
Spreadsheet::WriteExcel Google Group.

For posting and other options visit this group at:
http://groups.google.com/group/spreadsheet-writeexcel
?hl=en

You can also post by sending an email to:
http://groups.google.com/group/spreadsheet-writeexcel
?hl=en 

To unsubscribe send an email to
spreadsheet-writeexcel-unsubscribegooglegroups.com
-~----------~----~----~----~------~----~------~--~---