# Perl OLE Excel - STDEVA function

#### Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

•  Subject
• Author
• Posted on
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-> = qq ;
\$i++;
}

## Re: Perl OLE Excel - STDEVA function

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

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

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

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

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

## Re: Perl OLE Excel - STDEVA function

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-> = 1;
\$Excel-> = 1;

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-> = int(rand(900000));
\$range2-> = 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-> = \$cell_value;
print "Excel says: ",\$Range->Value,"\n";

\$i++;
}

\$Workbook -> Save();