Calling Excel Math Functions From PowerShell

by Doug Finke on April 5, 2011

I came across this post Calling Excel Math Functions From F# FSI and figured it would be very useful to do from PowerShell.

This can also be done a line at a time in the PowerShell command line REPL (Read Eval Print Loop). Meaning, you can create different arrays and multidimensional arrays at the command line and then type and call the Excel functions.

Plus, these are only 4 of the many Excel functions available.

If you want to find out the other Excel math functions available, type this line at the command line (run the first three lines of code up to the WorksheetFunction first):

$wf | Get-Member -MemberType method | Out-GridView

# Calling Excel Math Functions From PowerShell            

$xl        = New-Object -ComObject Excel.Application
$xlprocess = Get-Process excel            

$wf   = $xl.WorksheetFunction
$data = 1,2,3,4
$m    = ((1,2,3),(4,5,6),(7,8,9))            

Write-Host -ForegroundColor green Median
$wf.Median($data)            

Write-Host -ForegroundColor green StDev
$wf.StDev($data)            

Write-Host -ForegroundColor green Var
$wf.Var($data)            

Write-Host -ForegroundColor green MInverse
$wf.MInverse($m)            

$xl.quit()
$xlprocess | kill
# Results
Median
2.5
StDev
1.29099444873581
Var
1.66666666666667
MInverse
-4.5035996273705E+15
9.00719925474099E+15
-4.5035996273705E+15
9.007199254741E+15
-1.8014398509482E+16
9.00719925474099E+15
-4.5035996273705E+15
9.00719925474099E+15
-4.5035996273705E+15

Grab the Code

I put the code up on my GitHub PowerShell Repository.

{ 1 trackback }

Round up « Use PowerShell
04.08.11 at 4:22 pm

{ 0 comments… add one now }

Leave a Comment

You can use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>