# Excel Functions Mapped to Igor Pro

The code snippets here map some common Excel functions into their Igor Pro equivalents. They include some non-standard functions that may also be of general use to those transitioning from Excel to Igor Pro.

Feel free to suggest others to add to the list.

### Standard Function Mappings

Each of these take a wave or waves as input and return a single value equivalent to the respective Excel function of the same name.

Function average(ww)
wave ww
variable id

return (mean(ww))
end

Function stdev(ww)
wave ww

return (sqrt(variance(ww)))
end

Function slope(wwy,wwx)
wave wwy, wwx

make/n=2/FREE fit_results
CurveFit/Q/NTHR=0 line,  kwCWave=fit_results,  wwy /X=wwx

killwaves/Z W_sigma

return (fit_results[1])
end

Function intercept(wwy,wwx)
wave wwy, wwx

make/n=2/FREE fit_results
CurveFit/Q/NTHR=0 line,  kwCWave=fit_results,  wwy /X=wwx

killwaves/Z W_sigma

return (fit_results[0])
end

// LINEST takes only a y-wave and an x-wave
// ltype = 0 means force intercept to ZERO
// stats = 1 means only slope + intercept, otherwise all values
// optional how=1 means no printing (values are in W_coef and W_sigma waves)
// values are printed in history area by default
//      slope                        intercept
// slope regression      intercept regression

Function linest(wwy,wwx,ltype,stats,[how])
wave wwy, wwx
variable ltype,stats, how

if (ParamIsDefault(how))
how = 0
endif

string theResult

switch(ltype)
case 0:
K0 = 0
CurveFit/Q/H="10"/NTHR=0 line  wwy /X=wwx
break
case 1:
CurveFit/Q/NTHR=0 line  wwy /X=wwx
break
endswitch

wave W_coef
wave W_sigma

if (stats==0)
W_sigma=NaN
endif

switch(how)
case 0:
sprintf theResult "%g\t%g\r%g\t%g" W_coef[1], W_coef[0], W_sigma[1], W_sigma[0]
print theResult
break
default:
break
endswitch

return 0

end

### Non-Standard Functions

// Create a Row + Column "Spreadsheet-Like" Table
// This makes a matrix wave and opens it as though it is a spreadsheet table
// name - string name to generate a matrix wave
// rows, cols - number of rows and columns in matrix
// Example: rcdata("ralph",3,4) creates a 3x4 matrix wave named "ralph"

Function rcdata(name,rows,cols)
string name
variable rows, cols

string pStr = ""

if (exists(name)==1)
do
sprintf pStr, "The data (matrix) %s already exists. Please choose a different name.", name
prompt name, pStr
DoPrompt "Rename Data", name
while(exists(name)==1)
endif

make/O/N=(rows,cols) \$name
edit \$name

return 0
end

// Calculate Averages (Down Columns or Across Rows) in a "Spreadsheet-Like" Table
// This calculates the averages down a column or across a row in matrix wave and opens the resultant wave
// name - string name of existing matrix wave
// [rows] - optional 0 or 1 value to express that calculations are to be across rows (down columns is default)
// Example: rcaverage("ralph",rows=1) creates a vector wave named "rcave_ralph" with averages across rows in "ralph"

Function rcaverage(name,[rows])
string name
variable rows

variable rc, cc
string aname

if (exists(name)==0)
return -1
endif

sprintf aname, "rcave_%s",name

rc = dimsize(\$name,0)
cc = dimsize(\$name,1)

if(ParamIsDefault(rows))
make/O/N=(1,cc) \$aname
else
make/O/N=(rc,1) \$aname
endif

if (strlen(WinList(aname,";","WIN:2"))==0)
edit/N=\$aname \$aname
else
DoWindow/F \$aname
endif

return 0
end

Forum

Support

Gallery