AutoHotkey and Excel Worksheet functions: Mean/Average, Median & Mode

Typically I like to use AutoHotkey to insert the formula into Excel.  This allows for the user to make changes (or have it update if the  data changes).   Sometimes I just want a given value based on the data at-hand.  This is when it is awesome to be able to call the Excel worksheet functions.  In the below code and video I demonstrate how to call three Summary variables of central tendency: Mean (Average), Median and Mode.   For normally distributed data these will be the same however, in the real world, data is often skewed / non-bell shaped (has kurtosis).  Make sure you review the data to decide which best “summarizes” the data.

In the video I show how I initially planned to create three separate functions (one each summary variable) however with some help from Maestrith (author of AHK Studio) I was able to write one function and pass the method as a parameter.  🙂

Here is the script for Excel worksheet functions

XL_Handle(XL,1)

MsgBox % "median " XL_WF_Averages(XL,"A1:H1","Median")
MsgBox % "average " XL_WF_Averages(XL,"A1:H1","Average")
MsgBox % "mode " XL_WF_Averages(XL,"A1:H1","Mode")
;***********Delete blank columns*******************
XL_WF_Averages(PXL,RG,Summary_Var){
	app:=PXL.Application.WorkSheetFunction
	Try val:=app[Summary_Var](PXL.Application.ActiveSheet.Range(RG))
return val
}

;***********Handle*******************
XL_Handle(ByRef PXL,Sel){
ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN ;identify the hwnd for Excel
IfEqual,Sel,1, Return, PXL:= ObjectFromWindow(hwnd,-16).application ;Handle to Excel Application
IfEqual,Sel,2, Return, PXL:= ObjectFromWindow(hwnd,-16).parent ;Handlle to active Workbook
IfEqual,Sel,3, Return, PXL:= ObjectFromWindow(hwnd,-16).activesheet ;Handle to Active Worksheet
}
;***borrowd & tweaked from Acc.ahk Standard Library*** by Sean  Updated by jethrow*****************
ObjectFromWindow(hWnd, idObject = -4){
(if Not h)?h:=DllCall("LoadLibrary","Str","oleacc","Ptr")
    If DllCall("oleacc\AccessibleObjectFromWindow","Ptr",hWnd,"UInt",idObject&=0xFFFFFFFF,"Ptr",-VarSetCapacity(IID,16)+NumPut(idObject==0xFFFFFFF0?0x46000000000000C0:0x719B3800AA000C81,NumPut(idObject==0xFFFFFFF0?0x0000000000020400:0x11CF3C3D618736E0,IID,"Int64"),"Int64"), "Ptr*", pacc)=0
    Return	ComObjEnwrap(9,pacc,1)
}

And here is the video where I walk through Excel worksheet functions