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