# 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)
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)