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)
        DoAlert 0, "No data exist"
        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

Igor Pro 9

Learn More

Igor XOP Toolkit

Learn More

Igor NIDAQ Tools MX

Learn More