31.3 Excel and Origin Functions ComparisonExcel-Origin-Functions-Comp
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
|
A1:C0 or join(A:C)
|
A:C
|
| A range of cells
|
A1:C5
|
A1:C5
|
| Multiple ranges of cells
|
join(A1:B5,C7:D10)
|
(A1:B5,C7:D10)
|
| 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
|
$A$1:$C$5
|
$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
|
| Returns the average of a range
|
mean(A1:A6)1
|
average(A1:A6)
|
| Add values in cells
|
total(A1:A3) 1
sum(A1:A3) 2
|
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
|
if(A1<98.6, A1-98.6, NA())
or
(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
|
cell(4,2) or wcol(2)[4]
|
index(A:D, 4, 2) or indirect(address(2,4))
|
| Returns the count of values that satisfied the condition
|
countif(A,"GMC")
|
countif(A:A,"GMC")
|
| Returns the sum of values that satisfied the condition
|
sumif(A,">0")
|
sumif(A:A,">0")
|
| Returns a random number between 0 and 1 from a uniform distribution
|
rnd() or ran()
|
RAND()
|
Date and Time
Different Functions
| Description
|
Origin Cell Notation
|
Excel Notation
|
| Take day, month and year values in column A, B and C and combine them to form a date.
|
date(A, B, C)1
Note: set column property as date for proper date-string display
|
date(A:A,B:B,C:C)
|
| Take hour, minute and second values in column A, B and C and combine them to form a time.
|
time(A, B, C)1
Note: set column property as time for proper time-string display
|
time(A:A,B:B,C:C)
|
| Takes date-time string of specified format and returns a Julian day
|
date("1/1/2008")1
Note: Origin used different date-time system from Excel. If you want to have the same returned value as Excel, use date("1/1/2008")-2415018
|
datevalue("1/1/2008")
|
| Take the date in column A, row 1 represent in supported text format and return the Juplian 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 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")
|
Same Notation
| Description
|
Origin & Excel Notation
|
| Returns current date and time
|
now()
|
| Returns current date
|
today()
|
| Set column property as date for proper date-string display or you can use date2str to convert the date-value to date-string. e.g. date2str(today(), "MM/dd/yyyy")$
|
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
|
| Returns the base 10 equivalent to the hexadecimal value
|
hex2dec(A2) 1
|
Information
Different Functions
| Description
|
Origin Cell Notation
|
Excel Notation
|
| Check if the value in cell A1 is a number
|
istext(A1)>01
|
isnumber(A1)
|
| Check if the cell is empty
|
isempty(A1) 1
|
isblank(A1)
|
Same Notation
| Description
|
Origin & Excel Notation
|
| Returns 1 for missing value, else 0
|
isna(A1)1
|
| Check if the value in cell A1 is a Text
|
istext(A1)1
|
| Check if the cell contains cell formula or not
|
isFormula(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
|
Same Notation
| Description
|
Origin & Excel Notation
|
| Returns the exclusive-or of inputs
|
xor(3<4,4<5)
|
| Returns the error value
|
NA()
|
Lookup and Reference
| Description
|
Origin Cell Notation
|
Excel Notation
|
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()
|
| Returns the column number of the given column reference.
|
j1
or
colnum(col(B))1
|
column()
or
column(B1)
|
| 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 for a given number of items.
|
combine(4,3)1
|
combin(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)=-11
|
int(1.23)=1
int(-1.23)=-2
|
| Rounds a number up to the nearest integer or, optionally, to the nearest multiple of significance.
|
ceil(-2.9714, -2)=; //-41
ceil(-2.9714, 2)=; //-2
|
ceiling.math(-2.9714, 2, -1) //-4
cceiling.math(-2.9714, 2) //-2
|
| Round a number down to the nearest integer or to the nearest multiple of significance.
|
floor(-24.4,5)=; //-251
floor(-24.4,-5)=; //-20
|
floor.math(-24.4,5) //-25
floor.math(-24.4,5,-1) //-20
|
| 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
|
| Returns the number of combinations with repetitions for a given number of items.
|
combina(4,3)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
|
| 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)=-11
|
| 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 average of a range
|
mean(A1:A6)1
|
average(A1:A6)
|
| Returns the standard deviation of a sample (use n-1 in the calculation)
|
stddev(A2:A11)1
|
stdev.s(A2:A11)
|
| Returns the standard deviation of a population(use n in the calculation)
|
stddevp(A2:A11)1
|
stdev.p(A2:A11)
|
| Returns the uncorrected sum of squares
|
Ss(A,0)1
|
sumsq(A:A)
|
| 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[, option])1
|
count(A:A) //count containing number
CountA(A:A) //counts non-empty cells
CountBlank(A:A) //counts empty cells
|
| Returns the count of values that satisfied the condition
|
countif(A,">1")1
|
countif(A:A,">1")
|
| Counts the number of values that satisfies multiple specified conditions
|
countif(A,"A>1&&B>4")1
|
countifs(A:A,">1",B:B,">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 slope of the linear regression
|
slope(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
|
| 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,len(A1)-1)$)$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$)$
|
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)
|
|