Perl OLE Excel - STDEVA function

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

  1. Slickuser

    Slickuser Guest

    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
    #1
    1. Advertising

  2. Slickuser

    Ben Bullock Guest

    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
    #2
    1. Advertising

  3. Slickuser

    Slickuser Guest

    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 <> 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
    #3
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Patrick.O.Ige

    Ole ole

    Patrick.O.Ige, Jul 16, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    524
    Patrick.O.Ige
    Jul 16, 2006
  2. Drew Pihera
    Replies:
    0
    Views:
    666
    Drew Pihera
    Feb 4, 2004
  3. Lance Hoffmeyer
    Replies:
    0
    Views:
    273
    Lance Hoffmeyer
    Nov 17, 2003
  4. Replies:
    1
    Views:
    904
  5. TimmyD
    Replies:
    0
    Views:
    194
    TimmyD
    Apr 21, 2005
Loading...

Share This Page