use strict;
use warnings;
use Win32::OLE;

my $adrData   = "A1:B34";
my $adrSource = "C6:C37";
my $adrDest   = "D7";   #Notice the offset and the fact that I name only 1 cell
my $formula = "=SUM(A1:A3)";

my $xl = Win32::OLE->new('Excel.Application');
$xl->{EnableEvents} = 0;
$xl->{Visible} = 1;

my $wb = $xl->Workbooks->Add;
my $sht = $wb->Sheets(1);

my $rngData = $sht->Range($adrData);
for (1..$rngData->Cells->{Count}) {      #Set up some random data
                                         #Perl indexes from 0, Excel usually from 1
	$rngData->Cells($_)->{Value} = rand; #Don't use Cells in this way unless you understand it
}

my $rngSource = $sht->Range($adrSource);
$rngSource->Cells(1,1)->{Formula} = $formula;
#The next line shows you how to copy and paste. PasteSpecial is different.
#The paste range includes the copy range, but as it's a single cell, it's
#less work for the machine to overwrite than to calculate the correct range.
$rngSource->Cells(1,1)->Copy($rngSource);
for (1..$rngSource->Cells->{Count}) { #This is where we have to do that pesky regex stuff.
                                      #Without the offset it's not necessary.
    $rngSource->Cells($_)->{Formula} =~ s/\(A/\(A\$/;
	$rngSource->Cells($_)->{Formula} =~ s/:A/:A\$/;
} #Regex gurus - I'd love to know how to put both on one line

$rngSource->Copy($sht->Range($adrDest)); #Another copy/paste. If the paste range is a single
                                         #cell, Excel will DWIM, amazingly.