Perl OLE Excel - STDEVA function

[Slickuser]

I try to calculate the standard deviation of average of C2:C35.
But I get random range in the worksheet G$i. It's not showing as:

D2 =STDEVA(C2:C35,D2)
D3 =STDEVA(C2:C35,D3)
.....

It's showing as:

D2 =STDEVA(C2:C35,D2)
D3 =STDEVA(C2:C31,D3)
D4 =STDEVA(C2:C33,D4)
.....

Any one know why? I tried with " " doesn't help and q { } show nothing
since it's like ' '.

Thanks.

my $lastRowStd = 35;
my $i=2;
while($i<=$lastRowStd)
{
$Range = $CurrentSheet->Range("G$i");
$Range->{Value} = qq {=STDEVA(C2:C$lastRowStd,D$i)};
$i++;
}

[Ben Bullock]

On Fri, 02 May 2008 17:25:22 -0700, Slickuser wrote:

> I try to calculate the standard deviation of average of C2:C35. But I
> get random range in the worksheet G$i. It's not showing as:
>
> D2 =STDEVA(C2:C35,D2)
> D3 =STDEVA(C2:C35,D3)
> .....
>
> It's showing as:
>
> D2 =STDEVA(C2:C35,D2)
> D3 =STDEVA(C2:C31,D3)
> D4 =STDEVA(C2:C33,D4)
> .....
>
> Any one know why? I tried with " " doesn't help and q { } show nothing
> since it's like ' '.

I don't know why, but if I had to debug this,

> Thanks.
>
> my $lastRowStd = 35;
> my $i=2;
> while($i<=$lastRowStd)
> {

my $cell_value = "=STDEVA(C2:C".$lastRowStd.",D".$i;
print "In: ",$cell_value,"\n";

> $Range = $CurrentSheet->Range("G$i"); $Range->{Value} = qq

$Range->{Value} = $cell_value;
print "Excel says: ",$Range->Value,"\n";

> {=STDEVA(C2:C$lastRowStd,D$i)}; $i++;
> }

If you try running the above, what do you get?

[Slickuser]

Thanks Ben.

It was working before. It was my sort method was messing it up.

I tested out with this full code, and my previous and it work too.

use Win32::OLE;
use warnings;
use strict;

my $Excel = Win32::OLE->new('Excel.Application', 'Quit');
$Excel->{'Visible'} = 1;
$Excel->{SheetsInNewWorkbook} = 1;

my $Workbook = $Excel->Workbooks->Add();
my $CurrentSheet = $Workbook->ActiveSheet;
my $Range;

for (my $x=1; $x<= 20; $x++) {
my $range = $Excel->Range("C".$x);
my $range2 = $Excel->Range("D".$x);
$range->{Value} = int(rand(900000));
$range2->{Value} = int(rand(900000));
}

my $lastRowStd = 20;
my $i=1;
while($i<=$lastRowStd)
{

$Range = $CurrentSheet->Range("G$i");

my $cell_value = "=STDEVA(C1:C".$lastRowStd.",D".$i.")";
print "In: ",$cell_value,"\n";

$Range->{Value} = $cell_value;
print "Excel says: ",$Range->Value,"\n";

$i++;
}

$Workbook -> Save();