MedCalc.net
 
Home medcalc.net
Menu

Spreadsheet functions

This appendix lists only the most commonly used functions. For a full overview of all functions that are available in MedCalc, visit www.medcalc.org.

Mathematical functions

  • ABS(x) returns the absolute value of the number x.
  • EXP(x) returns the natural exponential of x:2.718281828x. EXP is the inverse function of the LN function.
  • LN(x) returns the natural logarithm of the positive number x to the base e (e=2.718281828). The argument x must be greater than 0. LN is the inverse function of EXP.
  • LOG(x) returns the logarithm of the positive number x in the base 10. The argument x must be greater than 0.
  • POWER(n,p) returns n raised to the power p.
  • RAND(x) returns a computer-generated random number (a) when x < 1 the result is a number between 0 and 1, or (b) when x >1 then the result is a number between 1 and x. The function will return a different value every time the spreadsheet is recalculated.
  • RANDNORM(m,s) returns a random number from a Normal distribution with mean m and standard deviation s.
  • ROUND(x,n) rounds the number x to the number of digits n. If n is negative, then x is rounded to the left of the decimal point.
  • SIGN(x) returns a number that indicates the sign x: -1 if x is negative; 0 if x equals 0; or 1 if xis positive.
  • SQRT(x) returns the square root of thepositive number x. SQRT(x) = x^(1/2). If the argument xof the function is negative, then the function returns an error (missing value).

Trigonometric functions

  • ACOS(x) returns the arccosine of x. The arccosine function is the inverse function of the cosine function and calculates the angle for a given cosine. X must be in the [-1..1] range. The result is an angle expressed in radians. To convert from radians to degrees, use the DEGREES() function.
  • ASIN(x) returns the arcsine of x. The arcsine function is the inverse function of the sine function and calculates the angle for a given sine. X must be in the range [-1..1]. The resultis an angle expressed in radians. To convert from radians to degrees, use the DEGREES() function.
  • ATAN(x) returns the arctangent of x.The arctangent function is the inverse function of the tangent function and calculates the angle for a given tangent. The result is an angle expressed in radians. To convert from radians to degrees, use the DEGREES() function.
  • ATAN2(y,x) returns the arc tangent of the two numbers x and y. It is similar to calculating the arc tangent of y/ x, except that the signs of both arguments are used to determine the quadrant of the result. The result is an angle expressed in radians. To convert from radians to degrees, use the DEGREES() function.
  • COS(x) returns the cosine of an angle x.The result range is [-1..1]. The angle x must be expressed in radians. To convert an angle expressed in degrees to radians, use the RADIANS() function.
  • DEGREES(x) converts an angle xexpressed in radians to degrees. The relation between the 2 units is as follows: 2 x Pi radians = 360 degrees.
  • RADIANS(x) converts an angle x expressed in degrees to radians.
  • SIN(x) returns the sine of the angle x. The result range is [-1..1]. The angle x must be expressed in radians. To convert an angle expressed in degrees to radians, use the RADIANS()function.
  • TAN(x) returns the tangent of the angle x. The angle x must be expressed in radians. To convert an angle expressed in degrees to radians, use the RADIANS() function.

Date and time functions

  • DATE(dnr) returns the date corresponding with the serial date number dnr expressed as a string. DATE(DATEVALUE(A1)+7) returns "6.9.96" when cell A1 contains the date string 30.8.96 or "30/8/96" (dateformat DD.MM.YY)
  • DATEFRAC(date) converts date into a fractional year-number. The integer part of this number is the year, and the decimal fraction ranges from 0.0 to 0.99..., representing the dates 01 Jan to 31 Dec. DATEFRAC("01.07.2000") returns 2000.5
  • DATEVALUE(str) returns the serial date number for the date expressed in the string str. DATEVALUE("10.12.88") returns 68645 if the date format is DD.MM.YY.
  • DAY(date) returns the day of the month of date. Date can either be a serial date number or a date string. DAY("23.08.88") returns 23(date format DD.MM.YY)
  • MONTH(date) returns the month of the year of date. Date can either be a serial date number or a date string. MONTH("23.08.88") returns 8 (date format DD.MM.YY)
  • TIMEFRAC(time) converts time into fractional time-number. The integer part of this number is the hour, and the decimal fraction ranges from 0.0 to 0.99, representing the minutes 0:00 to 0:59. TIMEFRAC("12:30") returns 12.5.
  • TIMEVALUE(str) converts a time string (in"hh:mm" format) to a serial time number. A serial time number is a number ranging from 0.00 to 1.00 representing the time 0:00 to 24:00. TIMEVALUE("06:30") returns 0.27084.
  • WEEKDAY(date) returns the weekday number of date. Days are numbered from Monday (=1) to Sunday (=7). WEEKDAY("05.07.56") returns 4, this is the 4th day of the week (=Thursday).
  • WEEKNUM(date) returns the number of the week of the year of date. Date can either be a serial date number or a date string. WEEKNUM("25.12.98") returns 52.
  • YEAR(date) returns the year of date. Date can either be a serial date number or a date string. YEAR("23.08.88") returns 88 (dateformat DD.MM.YY)

Logical functions

  • AND(condition1,condition2,...) returns 1 (=TRUE) if all of its arguments are TRUE. If one of the arguments, either condition1, condition2, etc. is FALSE (or 0), the function returns 0 (=FALSE). If one of the conditions cannot be evaluated (for example because of missing data), the function returns an error.
  • IF(condition,x,y) returns x if the condition is TRUE(=1), but returns y if the condition is FALSE (=0).
  • NOT(x) reverses the value of its argument x. If x is 0 or FALSE then NOT(x) returns 1 (= TRUE). If x is 1 or TRUE then NOT(x)returns 0 (= FALSE).
  • OR(condition1,condition2,...) returns 1 (=TRUE) if at least one of its arguments is TRUE (or 1). If one of the conditions cannot be evaluated (for example because of missing data), the function returns an error.