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();
On May 2, 5:44 pm, Ben Bullock
gmail.com> 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?
