Perl OLE Excel - STDEVA function

S

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++;
}
 
B

Ben Bullock

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,904
Latest member
HealthyVisionsCBDPrice

Latest Threads

Top