31.3 Excel and Origin Functions Comparison


Tables below comparing common Excel and Origin functions which can be used in setting cell values

Data References

Both Excel and Origin enable user to drag and drop to extend formulas across rows or columns. It introduces two type of references, Relative and Absolute reference. Relative references change when you drag and drop the cell to other rows or columns. While absolute references keeps to be a constant.

Notes: When you copy a cell formula to other cells, column and row references are adjusted in Excel. They are not adjusted in Origin.
Relative Data References
Description Origin Cell Notation Excel Notation
Reference to the 1st row of column A A1 A1
Entire column A
A A:A
Multiple columns Must use range notation for multiple columns (e.g. A1:C5). A:C
A range of cells A1:C5 A1:C5
Reference to A1 in first sheet in the same book 1!A1

Sheet1!A1

Sheet1!A1
Reference to A1 in first sheet in the another book, Book1 [Book1]1!A1

[Book1]Sheet1!A1

[Book1]Sheet1!A1
Absolute Data References
Description Origin Cell Notation Excel Notation
Reference to the 1st row of column A $A$1 $A$1
Entire column A A $A:$A
A range of cells Origin does not support absolute reference for a range across columns $A$1:$C$5
Reference to A1 in first sheet in the same book 1!$A$1

Sheet1!$A$1

Sheet1!$A$1
Reference to A1 in first sheet in the another book, Book1 [Book1]1!$A$1

[Book1]Sheet1!$A$1

[Book1]Sheet1!$A$1


Text Data References
Origin Cell Notation Excel Notation
Reference to a cell with text or returned text values in Origin should be followed by a $-sign

Example:

A1$
left(A1$, 2)$
References to a cell with text or numeric values are used in the same way

Example:

A1
left(A1, 2)

The "$" is used in Origin formulas in two very different ways. If the "$" precedes a column or row reference, it functions to make an absolute column or row reference. If the "$" is appended to a column or row reference, it functions to return the value stored in a string variable.

Most Popular Functions

Description Origin Cell Notation Excel Notation
Add values in cells total(A1:A3) 1 sum(A1:A3)
Returns the row-wise sum of a values in columns A to C, D to G, and F. sum(A:C, D:G, F) =SUM(A1:C1, D1:G1, F1)
Return one value if a condition is true and another value if it's false (A1<=98.6)?(A1-98.6):Na()1 if(A1<98.6, A1-98.6, NA())
Concatenate strings in column B, row 1 and column C, row 1. B1$ + C1$ B1 & C1

concatenate(B1, C1)

Concatenate strings in column A and B A$ + B$ A1 & B1 or concatenate(A1, B1), then drag to auto fill to all cells of the output column
Looks up 2 in column A, and returns the value from column B that is in the same row. lookup(2, A, B)$1 lookup(2,A:A,B:B )
Looks up value in E1 in column B and return the value from column D that is in the same row lookup(E1, B, D)1 vlookup(E1, B:D, 3)
Searches for 3.5 in column A and returns the position. index(3.5, A)1 match(3.5,A:A )
Takes date-time string of specified format and returns Julian day value. date("1/1/2008")1 datevalue("1/1/2008")
Finds the number of days between the end date (3/15/11) and end date (2/1/11). date("3/15/11")-date("2/1/11") days("3/15/11","2/1/11")
Locate one text string within a second text string and return the starting position find(B10, "M")1 find("M", B10)
Find the value find the value of given row and column wcol(2)[4] index(A:D, 4, 2)

Date and Time

Different Functions
Description Origin Cell Notation Excel Notation
Take day, month and year values in column A, B and C and return the corresponding date string. date2str(date(A,B,C),"dd/MM/yyyy")$1, 2 date(A:A,B:B,C:C)
Takes date-time string of specified format and returns a date serial number recognized by Excel (which is Origin Julian day value subtracted by 2415018to). date("1/1/2008")-24150181 datevalue("1/1/2008")
Take the date in column A, row 1 represent in supported text format and return the day day(date("2/18/2012 13:15:12"))1, 2 day("7/18/2011 13:15:12")
Returns the day number between of two dates date("7/18/2012")-date("3/15/2011")1 days("2/1/12","3/15/11")
Returns the hour value of a date-time string hour(date("7/18/2011 13:15:12"))1 hour("7/18/2011 13:15:12")
Returns the minute value of a date-time string minute(date("7/18/2011 13:15:12"))1 minute("7/18/2011 13:15:12")
Returns the month value of a date-time string month(date("7/18/2011 13:15:12"))1 month("7/18/2011 13:15:12")
Returns current date-time string of specified format date2str(now(),"MM/dd/yyyy HH:mm:ss")$1, 2 now()
Returns the Second value of current time second(date("7/18/2011 13:15:12"))1 second("7/18/2011 13:15:12")
Returns the Julian-day value of given time in the format(HH:mm:ss) time("13:15:12")1 timevalue("13:15:12")
Returns current date string of specified format date2str(today(),"MM/dd/yyyy")$1, 2 today()


Same Notation
Description Origin & Excel Notation
Returns the Julian-day value of given time in the format(HH,mm,ss) time(13,15,12)

Engineering

Different Functions
Description Origin Cell Notation Excel Notation
Takes the decimal value of a binary number and then converts it to hexadecimal dec2hex(Bin2dec(A1))$1 bin2hex(11111011)
Convert a decimal number to binary dec2bin(-100)$1 dec2bin(-100)
Convert a decimal number to hexadecimal dec2hex(-100)$1 dec2hex(-100)
Convert a hexadecimal value to binary dec2bin(hex2dec(B7))$1, 2 hex2bin(B7)
Returns the base 10 equivalent to the hexadecimal value hex(A2) 1 hex2dec(A2)


Same Notation
Description Origin & Excel Notation
Returns the decimal value of a binary number bin2Dec(A1)1
Converts a number from one measurement system to another. convert(1,"yd","m")1

Information

Different Functions
Description Origin Cell Notation Excel Notation
Check if the value in cell A1 is a number istext(A1)=>01 cell("type", A1)=>"v"
Check if the cell is empty isempty(A1) 1 isblank(A1)
Determines whether an input is a text and returns the specified values acordding to the result istext(A1)?0:11 isnumber(A1)


Same Notation
Description Origin & Excel Notation
Returns 1 for missing value, else 0 isna(A1)1
Determines whether the input is a text istext(A1)1

Logical

Different Functions
Description Origin Cell Notation Excel Notation
Returns 1 when all conditions are true. A1>1 && A2>2 and(A1>1 , A2>2)
Returns the specified value according to the conditional expression. A1>0?1:-1 if(A1>0,1,-1)
Returns the specified value if the input number is a missing value or NANUM. isna(sqrt(-4))?-1:0 iferror(SQRT(-4),-1)
Returns 1 when any condition is true. A1>1 || A2>2 or(A1>1 , A2>2)
Logical true value.
Note: In Origin, any non-0 value is regarded as true.
1 TRUE
Logical false value. 0 FALSE
Returns the exclusive-or of inputs ((3<4) + (4<5)) - int( ((3<4)+(4<5) )/2)*2 ==>0 xor(3<4,4<5) ==> FALSE


Same Notation
Description Origin & Excel Notation
Returns the error value NA()

Lookup and Reference

Description Origin Cell Notation Excel Notation
Returns the column number of the given column reference. colnum(col(B))1 column(B1)
Looks up 2 in column A, and returns the value from column B that is in the same row.
Note: In Origin, depending on the type of return value, use lookup()$ or lookup().
lookup(2, A, B)$1 lookup(2,A:A,B:B )
Returns the index of the value located in the range. list(25,col(A)[1:3])1

index(25, A1:A3)1

match(25,A1:A3,0)=2
Returns current row number i1 row()
Insert a label with hyperlink. http://www.originlab.com [Origin]1 hyperlink("http://www.originlab.com","Origin")

Math and Trigonometry

Different Functions
Description Origin Cell Notation Excel Notation
Returns the number of combinations (with repetitions) for a given number of items. combine(4+3-1,4-1)1 combina(4,3)
Returns the truncated integer of the value(s).
CAUTION!: Excel's INT function rounds down, but Origin takes the interger part. E.g., In Origin int(-1.23)=-1; but in Excel INT(-1.23)=-2.
int(1.23)=1
int(-1.23)=-1
1
int(1.23)=1
int(-1.23)=-2
Returns the logarithm of a number to the base you specify. log(8)/log(2)1 log(8, 2)
Returns the base-10 logarithm of the value(s). log(2)1 log10(2)
Returns the result of a number raised to a power. 3^2 power(3,2)
Returns the integer portion of a division. int(5/2)1 quotient(5, 2)
Returns the sum of specified dataset. total(A1:A5)1 sum(A1:A5)
Same Notation
Description Origin & Excel Notation
Returns the absolute value of the data abs(-1)1
Returns the inverse of the corresponding trigonometric function. acos(0)1
Returns the inverse hyperbolic cotangent of that number. acoth(6)1
Returns the arccotangent of the value(s). acot(2)1
Returns the arcsine of the value(s). asin(0.5)1
Returns the inverse hyperbolic sine. ASINH(10)1
Returns arctangent of the value(s). atan(1)1
Return the angle between the positive X axis and the point given by the coordinates (x, y). atan2(2,2)1
Return the inverse hyperbolic tangent of that number. atanh(0.76159416)1
Return the number of k-combinations from a given set with n elements. combine(8,2)1
Returns value of cosine of the value(s). cos(pi/3)1
Returns the cotangent of the value(s). cot(pi/6)1
Converts the radians into degrees. degrees(pi)1
Returns the exponential value of the value(s). exp(1)1
Return the factorial of a non-negative integer. fact(5.5)1
Returns the natural logarithm of the value(s). ln(10)1
Returns the integer modulus of an integer divided by anther integer. mod(13,2)1
Returns the value of pi pi
Convert degrees into radians. radians(180)1
Returns a value between 0 and 1 from a uniformly distributed sample. rnd()1
Rounds a number to a specified number of digits round(-1.99,0)1
Returns trigonometric secant of the given value(s). secant(pi/3)1
Returns the hyperbolic secant of the given value(s). sech(1)1
Returns the sign of real number sign(2)=1
sign(0)=0
sign(-2)=-1
1
Returns value of sine for the given value(s). sin(pi/4)1
Returns the hyperbolic sine of the given value(s). sinh(5)1
Returns the square root of the given value(s). sqrt(2)1
Returns value of tangent of the given value(s). tan(pi/4)1
Returns the hyperbolic tangent of the given value(s). tanh(0.5)1

Statistics

Different Functions
Description Origin Cell Notation Excel Notation
Returns the uncorrected sum of squares rms(A)^21count(A)1 sumsq(A:A)
Calculate the mean of values that satisfies a specified condition. averageif(A, "col(A) > 5")1 averageif(A:A, "> 5")
Calculate the mean of values that satisfies a specified condition averageif(A, "col(A) > 5 && col(B) < 10")1 averageifs(A:A,A:A, ">5",B:B, "< 10")
Returns the beta distribution. betacdf(1, 2, 2)1 beta.dist(1,2,2,TRUE)
Returns the inverse of the cumulative distribution function for a specified beta distribution. betainv(0.8975362,8,10)1 beta.inv(0.8975362,8,10)
Returns the chi-squared distribution. chi2cdf(0.5,1)1 chisq.dist(0.5,1,TRUE)
Returns the inverse of the left-tailed probability of the chi-squared distribution. chi2inv(0.93,1)1 chisq.inv(0.93,1)
Returns the number of elements in the vector, size of the vector.
Note Excel will exclude missing values but Origin will not
count(A)1 count(A:A)
Returns the count of values that satisfied the condition countif(A,"col(A)>1")1 countif(A:A,">1")
Counts the number of values that satisfies multiple specified conditions countif(A,"col(A)>1&&col(A)>4")1 countifs(A:A,">1",A:A,">4")
Returns the covariance between two datasets cov(A, B)1 covariance.s(A:A,B:B)
Returns the inverse of the F probability distribution. finv(A2, A3, A4)1 f.inv(A2,A3,A4)
Returns the lognormal distribution of x logncdf(4,3.5,1.2)1 lognorm.dist(4,3.5,1.2,TRUE)
Returns the inverse of the lognormal cumulative distribution function of x logninv(0.039084,3.5,1.2)1 lognorm.inv(A2, A3, A4)
Inverse of cumulative distribution functions for Normal distribution, returns the probability value norminv(0.9)=1.28155156554461 NORMINV(0.9,0,1)=1.281551566
Returns the square of correlation coefficient between two datasets correl(A,B)^21 rsq(A:A,B:B )
Returns the moving slopes at each point with a specified window width. movslope(A,B)1 slope(B:B,A:A)
Returns the left-tailed inverse of the Student's t-distribution. tinv(0.75,2)1 t.inv(0.75,2)
Returns 0.5 less than the standard normal cumulative distribution prob(2)/21 gauss(2)
Same Notation
Description Origin & Excel Notation
Return the average of a range mean(A1:A6)1
Returns the gamma function value of 2.5 (1.329) gamma(2.5)1
Returns the natural logarithm of the gamma function, Γ(x). gammaln(4)1
Returns kurtosis of a dataset. kurt(A)1
Return the maximum value from a set of values. max(A1: C8)1
Returns the minimum value in a list of arguments min(A1:C8)1
Returns the skewness of a distribution. skew(A1:A4)1

Text

Description Origin Cell Notation Excel Notation
Connect strings as one and return the result string. B1$ + C1$ concatenate(B1,C1)
Returns the leftmost characters from a text value left(A1$, 2)$1 left(A1,2)
Returns the number of characters of a string. len(A1$) or len(A1)1 len(A1)
Returns the string that has been converted to lowercase. lower(A1$)$1 lower(A1)
Returns the extracted characters by specifying the start character and number of the subsequent characters mid(A1$, 2, 3)$1 mid(A1,2,3)
Capitalizes the first letter in each word of a text value upper(left(A1$,1)$)$+lower(right(A1$,2)$)$1 proper(A1)
Returns the leftmost characters from a text value right(A1$, 2)$1 right(A1,2)
Returns the position of the string and return 1 for none found search(A1$, "margin")1 search("margin",A1)
Returns the string that has been converted to uppercase. upper(A1$)$1 upper(A1)
Same Notation
Description Origin & Excel Notation
Convert a string to a number. Options for specifying decimal and numeric group separators allow override of OS regional settings. NumberValue(string [, string Decimal, string Group])1

Miscellaneous

Description Origin Cell Notation Excel Notation
Assign values to variables and use the values in an expression. LET(t,if(A$==B$,1,0),t*500)
LET(aa,left(A,1),bb,left(B,2), aa$+ "-" + bb$)
LET(t,IF(A1=B1,1,0),t*500)