# Perl OLE Excel - STDEVA function

Discussion in 'Perl Misc' started by Slickuser, May 3, 2008.

1. ### SlickuserGuest

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

Slickuser, May 3, 2008

2. ### Ben BullockGuest

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?

Ben Bullock, May 3, 2008

3. ### SlickuserGuest

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

On May 2, 5:44 pm, Ben Bullock <> wrote:
> 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, May 3, 2008