This article lists all the formulas that are supported in DronaHQ Studio
ABS
Returns the absolute value of a number
Input: ABS(-20)
Result: 20
ACCRINT
Returns the accrued interest for a security that pays periodic interest
Input: ACCRINT("1/1/2010", "2/1/2010", "12/31/2012",5%,100,4,0)
Result: 14.98611111
ACOS
Returns the arccosine of a number
Input: ACOS(0)
Result: 1.570796327
ACOSH
Returns the inverse hyperbolic cosine of a number
Input: ACOSH(2)
Result: 1.316957897
ACOT
Returns the arccotangent of a number
Input: ACOT(0)
Result: 1.570796327
ACOTH
Returns the hyperbolic arccotangent of a number
Input: ACOTH(2)
Result: 0.5493061443
AGGREGATE
Returns an aggregate in a list or database
Input: aggregate(1,4,LOOKUP(Stocks.in))
Result: 33
AND
Returns TRUE if all of its arguments are TRUE
Input: AND(1=1,8>7) (All true)
Result: TRUE
ARABIC
Converts a Roman number to Arabic, as a number
Input: ARABIC(“XXXIXI”)
Result: 40
ASIN
Returns the arcsine of a number
Input: ASIN(0.5)
Result: 0.5235987756
ASINH
Returns the inverse hyperbolic sine of a number
Input: asinh(2)
Result: 1.443635475
ATAN
Returns the arctangent of a number
Input: ATAN(3.14/2)
Result: 1.003655078
ATAN2
Returns the arctangent from x- and y-coordinates
Input: ATAN2(2,3)
Result: 0.9827937232
ATANH
Returns the inverse hyperbolic tangent of a number
Input: atanh(0.5)
Result: 0.5493061443
AVEDEV
Returns the average of the absolute deviations of data points from their mean
Input: avedev(1,2)
Result: 0.5
AVERAGE
Returns the average of its arguments
Input: AVERAGE(1,2,3)
Result: 2
AVERAGEA
Returns the average of its arguments, including numbers, text, and logical values
Input: AVERAGEA(LOOKUP(Stocks.in,Stocks.out))
Result: 33
AVERAGEIF
Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria
Input: AVERAGEIF(LOOKUP(Stocks.in,’>10’)
Result:
AVERAGEIFS
Returns the average (arithmetic mean) of all cells that meet multiple criteria.
Input: AVERAGEIFS(LOOKUP(Stocks.in),LOOKUP(Stocks.out),’>10’)
Result: 27.5
BASE
Converts a number into a text representation with the given radix (base)
Input: BASE(8,2,6)
Result: 1000
BESSELI
Returns the modified Bessel function In(x)
Input: BESSELI(1.5,1)
Result: 0.981666
BESSELJ
Returns the Bessel function Jn(x)
Input: BESSELJ(12,2)
Result: -0.08493
BESSELK
Returns the modified Bessel function Kn(x)
Input: BESSELK(80,5)
Result: 2.95E-36
BESSELY
Returns the Bessel function Yn(x)
Input: BESSELY(12,5)
Result: -0.22981795
BIN2DEC
Converts a binary number to decimal
Input: BIN2DEC(1000)
Result: 8
BIN2HEX
Converts a binary number to hexadecimal
Input: BIN2HEX(101,8)
Result: 5
BIN2OCT
Converts a binary number to octal
Input: BIN2OCT(1000,8)
Result: 10
BINOMDIST
Returns the individual term binomial distribution probability
Input: BINOMDIST(3,7,0.5,TRUE)
Result: 0.5
BITAND
Returns a ‘Bitwise And’ of two numbers
Input: BITAND(12,14)
Result: 12
BITXOR
Returns a bitwise ‘Exclusive Or’ of two numbers
Input: BITXOR(9,5)
Result: 12
CEILING
Rounds a number to the nearest integer or to the nearest multiple of significance
Input: CEILING(10,6)
Result: 12
CEILING.MATH
Rounds a number up, to the nearest integer or to the nearest multiple of significance
Input: CEILINGMATH(11,2,1)
Result: 12
CEILINGPRECISE
Rounds a number the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded up.
Input: CEILINGPRECISE(12.5,6.2)
Result: 18.6
CHAR
Returns the character specified by the code number
Input: CHAR(80)
Result: P
CHOOSE
Chooses a value from a list of values
Input: CHOOSE(2, “A”, “B”)
Result: B
CLEAN
Removes all nonprintable characters from text
Input: CLEAN(“AF”&CHAR(31))
Result: AF
CODE
Returns a numeric code for the first character in a text string
Input: CODE(“a”)
Result: 97
COLUMN
Returns the column number of a reference
Input: COLUMN(LOOKUP(Stocks.in,Stocks.serialnumber=5))
Result: 5
COLUMNS
Returns the number of columns in a reference
Input: COLUMNS(LOOKUP(Stocks.in,Stocks.out))
Result: 2
CONVERT
Converts a number from one measurement system to another
Input: CONVERT(1000, “g”, “kg”)
Result: 1
CORREL
Returns the correlation coefficient between two data sets
Input: CORREL(LOOKUP(Stocks.in),LOOKUP(Stocks.out))
Result: 0.400516
COS
Returns the cosine of a number
Input: COS(0)
Result: 1
COSH
Returns the hyperbolic cosine of a number
Input: COSH(3.14/3)
Result: 1.599623814
COT
Returns the hyperbolic cosine of a number
Input: COT(0.5)
Result: 1.830487722
COTH
Returns the cotangent of an angle
Input: COTH(3.14/6)
Result: 2.082168237
COUNT
Counts how many numbers are in the list of arguments
Input: COUNT(1,5,7,9)
Result: 4
COUNTA
Counts how many values are in the list of arguments
Input: COUNTA(LOOKUP(Stocks.in),LOOKUP(Stocks.out))
Result: 10
COUNTBLANK
Counts the number of blank cells within a range
Input: COUNTBLANK(LOOKUP(Stocks.in))
Result: 1
COUNTIF
Counts the number of cells within a range that meet the given criteria
Input: COUNTIF(LOOKUP(Stocks.in),’>1’)
Result: 5
CSC
Returns the cosecant of an angle
Input: CSC(3.14/2)
Result: 1.000000317
CSCH
Returns the hyperbolic cosecant of an angle
Input: CSCH(1)
Result: 0.8509181282
CUMIPMT
Returns the cumulative interest paid between two periods
Input: CUMIPMT(1.2%, 12, 100, 1, 5, 0)
Result: -5.052998806
CUMPRINC
Returns the cumulative principal paid on a loan between two periods
Input: CUMPRINC(12%, 12, 100, 1, 5, 0)
Result: -26.32417137
DATE
Returns the serial number of a particular date
Input: DATE(1969, 7, 20)
Result: 7/20/1969
DATEVALUE
Converts a date that is stored as text to a serial number.
Input: DATEVALUE(“1969-7-20”)
Result: 25403.99386574074
DAY
Converts a serial number to a day of the month
Input: DAY(“7/20/1969”)
Result: 20
DAYS
Returns the number of days between two dates
Input: DAYS(“7/24/1969”, “7/16/1969”)
Result: 8
DAYS360
Calculates the number of days between two dates based on a 360-day year
Input: DAYS360(“7/16/1969”, “7/24/1969”, 1)
Result: 8
DB
Returns the depreciation of an asset for a specified period by using the fixed-declining balance method
Input: DB(100, 50, 10, 2, 12)
Result: $6.25
DDB
Returns the depreciation of an asset for a specified period by using the double-declining balance method or some other method that you specify
Input: DDB(100, 50, 10, 2, 2.25)
Result: $17.44
DEC2BIN
Converts a decimal number to binary
Input: DEC2BIN(“100”,8)
Result: 1100100
DECIMAL
Converts a text representation of a number in a given base into a decimal number
Input: DECIMAL(“1000”,2)
Result: 8
DEGREES
Converts radians to degrees
Input: DEGREES(3.14/6)
Result: 29.98479128
DELTA
Tests whether two values are equal
Input: DELTA(8,8)
Result: 1
DEVSQ
Returns the sum of squares of deviations
Input: DEVSQ(1,2)
Result: 0.5
DOLLARDE
Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number
Input: DOLLARDE(100.1, 32)
Result: 100.3125
DOLLARFR
Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction
Input: DOLLARFR(100.125, 32)
Result: 100.04
EDATE
Returns the serial number of the date that is the indicated number of months before or after the start date
Input: EDATE(“7/20/1969”, 1)
Result: 25434.99386574074
EFFECT
Returns the effective annual interest rate
Input: EFFECT(3%, 12)
Result: 0.03041595691
EOMONTH
Returns the serial number of the last day of the month before or after a specified number of months.
Syntax: EOMONTH(start_date, months)
Input: EOMONTH(“7/20/1969”, 1)
Result: 25383.99386574074
ERF
Returns the error function
Input: ERF(-2.3, -0.7)
Result: 0.3210556296
ERFC
Returns the complementary error function
Input: ERFC(2)
Result: 0.004677734981
EVEN
Rounds a number up to the nearest even integer
Input: EVEN(135)
Result: 136
EXACT
Checks to see if two text values are identical
Input: EXACT(‘hello’, ‘goodbye’)
Result: FALSE
EXP
Returns e raised to the power of a given number
Input: EXP(5)
Result: 148.4131591
EXPONDIST
Returns the exponential distribution
Input: EXPONDIST(12,1,24)
Result:
FACT
Returns the factorial of a number
Input: FACT(5)
Result: 120
FACTDOUBLE
Returns the double factorial of a number
Input: FACTDOUBLE(5)
Result: 15
FALSE
Returns the logical value FALSE
Input: FALSE()
Result: FALSE
FDIST
Returns the F probability distribution
Input: FDIST(15.35, 7, 6)
Result: 0.001930553432
FDISTRT
Returns the F probability distribution
Input: F.DIST.RT(15.35, 7, 6)
Result: 0.001930553432
FINVRT
Returns the inverse of the F probability distribution
Input: F.INV.RT(0.42, 2, 3)
Result: 1.174597281
FINV
Returns the inverse of the F probability distribution
Input: FINV(0.42, 2, 3)
Result: 1.174597281
FISHER
Returns the Fisher transformation
Input: FISHER(0.962)
Result: 1.97206674
FISHERINV
Returns the inverse of the Fisher transformation
Input: FISHERINV(0.962)
Result: 0.7451676441
FLOOR
Rounds a number down, toward zero
Input: FLOOR(19.89,5)
Result: 15
FORECAST
Returns a value along a linear trend
Input: FORECAST(5,LOOKUP(Stocks.in),LOOKUP(Stocks.out))
Result: FORECAST(5,LOOKUP(Stocks.in),LOOKUP(Stocks.out))
FREQUENCY
Returns a frequency distribution as a vertical array
Input: FREQUENCY(LOOKUP(Stocks.in),LOOKUP(Stocks.out))
Result: 1
FV
Returns the future value of an investment
Input: FV(2%, 12, 100, 400, 0)
Result: -$1,848.51
GAMMA
Returns the Gamma function value
Input: GAMMA(2)
Result: 1
GCD
Returns the greatest common divisor
Input: GCD(LOOKUP(Stocks.in),
Result: 5
GEOMEAN
Returns the geometric mean
Input: GEOMEAN(1,2)
Result: 1.414213562
GESTEP
Tests whether a number is greater than a threshold value
Input: GESTEP(5, 2)
Result: 1
HARMEAN
Returns the harmonic mean
Input: HARMEAN(1, 2)
Result: 1.333333333
HEX2BIN
Converts a hexadecimal number to binary
Input: HEX2BIN(“F1”,8)
Result: 11110001
HEX2DEC
Converts a hexadecimal number to decimal
Input: HEX2DEC(“FFFFFFFF5B”)
Result: -165
HEX2OCT
Converts a hexadecimal number to octal
Input: HEX2OCT(377)
Result: 1567
HOUR
Returns the hour as a number from 0 to 23
Input: HOUR(TODAY())
Result: 10 (assuming that time is 10:30 AM at that given moment)
HYPGEOMDIST
Returns the hypergeometric distribution
Input: HYPGEOMDIST(4, 12, 20, 40)
Result: 0.1092430027
IF
Specifies a logical test to perform
Input: IF(2>1,TRUE,FALSE)
Result: TRUE
IMABS
Returns the absolute value (modulus) of a complex number
Input: IMABS(“1+2i”)
Result: 2.236067977
IMAGINARY
Returns the imaginary coefficient of a complex number
Input: IMAGINARY(“5+6i”)
Result: 6
IMARGUMENT
Returns the argument theta, an angle expressed in radians
Input: IMARGUMENT(“4+3i”)
Result: 0.6435011088
IMCOSH
Returns the hyperbolic cosine of a complex number
Input: IMCOSH(“4+5i”)
Result: 7.74631300740307-26.1689640538728i
IMCOT
Returns the cotangent of a complex number
Input: IMCOT(“4+3i”)
Result: 0.00490118239430447-0.999266927805902i
IMCSC
Returns the cosecant of a complex number
Input: IMCSC(“3+4i”)
Result: 0.0051744731840194+0.036275889628626i
IMDIV
Returns the quotient of two complex numbers
Input: IMDIV(“2+3i”,“8+5i”)
Result: 0.348314606741573+0.157303370786517i
IMEXP
Returns the exponential of a complex number
Input: IMEXP(“4+3i”)
Result: -54.0517588610782+7.70489137273115i
IMLN
Returns the natural logarithm of a complex number
Input: IMLN(“4+3i”)
Result: 1.6094379124341+0.643501108793284i
IMLOG10
Returns the base-10 logarithm of a complex number
Input: IMLOG10(“4-3i”)
Result: 0.698970004336019-0.279468980647547i
IMLOG2
Returns the base-2 logarithm of a complex number
Input: IMLOG2(“4+4i”)
Result: 2.5+1.1330900354568i
IMPOWER
Returns a complex number raised to an integer power
Input: IMPOWER(“4+3i”,2)
Result: 7+24i
IMPRODUCT
Returns the product of complex numbers
Input: IMPRODUCT(“4+3i”,“4+3i”)
Result: 7+24i
IMREAL
Returns the real coefficient of a complex number
Input: IMREAL(“45+67i”)
Result: 45
IMSEC
Returns the secant of a complex number
Input: IMSEC(“12+48i”)
Result: 2.4052607061286E-21-1.52940890101143E-21i
IMSECH
Returns the hyperbolic secant of a complex number
Input: IMSECH(“4+3i”)
Result: -0.0362534969158689-0.00516434460775318i
IMSIN
Returns the sine of a complex number
Input: IMSIN(“4+3i”)
Result: -7.61923172032141-6.548120040911i
IMSINH
Returns the hyperbolic sine of a complex number
Input: IMSINH(“12+12i”)
Result: 68670.6375182113-43664.906676772i
IMSUB
Returns the difference between two complex numbers
Input: IMSUB(“4+3i”,“4-3i”)
Result: 6i
IMSUM
Returns the sum of complex numbers
Input: IMSUM(“4+3i”,“4+3i”)
Result: 8+6i
IMTAN
Returns the tangent of a complex number
Input: IMTAN(“4+3i”)
Result: 0.00490825806749606+1.00070953606723i
INT
Rounds a number down to the nearest integer
Input: INT(52.63)
Result: 52
INTERCEPT
Returns the intercept of the linear regression line
Input: INTERCEPT(LOOKUP((Stocks.in),LOOKUP(Stocks.out))
Result: 27.05997
IPMT
Returns the interest payment for an investment for a given period
Input: IPMT(1%, 1, 360, 100000, 30000, 0)
Result: -$1,000.00
IRR
Returns the internal rate of return for a series of cash flows
Input: IRR(LOOKUP(Fetch_new.irr),0.01)
Result: -0.35
ISEVEN
Returns TRUE if the number is even
Input: ISEVEN(6)
Result: TRUE
ISLOGICAL
Returns TRUE if the value is a logical value
Input: ISLOGICAL(LOOKUP(Stocks.in))
Result: FALSE
ISNONTEXT
Returns TRUE if the value is not text
Input: ISNONTEXT(LOOKUP(Stocks.in))
Result: TRUE
ISNUMBER
Returns TRUE if the value is a number
Input: ISNUMBER(889)
Result: TRUE
ISODD
Returns TRUE if the number is odd
Input: ISODD(52)
Result: FALSE
ISTEXT
Returns TRUE if the value is text
Input: ISTEXT(“qwe”)
Result: TRUE
ISOWEEKNUM
Returns the number of the ISO week number of the year for a given date
Input: ISOWEEKNUM(“7/20/1969”)
Result: 29
ISPMT
Calculates the interest paid during a specific period of an investment
Input: ISPMT(15%, 2, 5, 1000)
Result: -90
KURT
Returns the kurtosis of a data set
Input: KURT(1,2,3,4)
Result: -1.2
LARGE
Returns the k-th largest value in a data set
Input: LARGE(LOOKUP(Stocks.in),1)
Result: 60
LCM
Returns the least common multiple
Input: lcm(1,5,9,86)
Result: 3870
LINEST
Returns the parameters of a linear trend
Input: LINEST(LOOKUP(Stocks.in),LOOKUP(Stocks.out),TRUE,FALSE)
Result: 0.20482866
LN
Returns the natural logarithm of a number
Input: LN(2)
Result: 0.6931471806
LOG
Returns the logarithm of a number to a specified base
Input: LOG(5,10)
Result: 0.6989700043
LOG10
Returns the base-10 logarithm of a number
Input: LOG10(2)
Result: 0.3010299957
LOGEST
Returns the parameters of an exponential trend
Input: LOGEST(LOOKUP(Stocks.in),LOOKUP(Stocks.out),TRUE,FALSE)
Result: 1.007655
LOGNORMDIST
Returns the cumulative lognormal distribution
Input: LOGNORMDIST(4, 4, 6)
Result: 0.3315570972
LOGNORMINV
Returns the inverse of the lognormal cumulative distribution
Input: LOGNORMINV(0.5,2,0.3)
Result: 7.389056
LOWER
Converts text to lowercase
Input: LOWER(“QWEWEQWE”)
Result: qweweqwe
MAX
Returns the maximum value in a list of arguments
Input: MAX(1,2,3,4,5)
Result: 5
MAXA
Returns the maximum value in a list of arguments, including numbers, text, and logical values
Input: MAXA(2,LOOKUP(Stocks.in),LOOKUP(Stocks.out))
Result: 200
MEDIAN
Returns the median of the given numbers
Input: MEDIAN(LOOKUP(Stocks.in),LOOKUP(Stocks.out))
Result: 20
MID
Returns a specific number of characters from a text string starting at the position you specify
Input: MID(“Today is Wednesday”,6,20)
Result: is Wednesday
MIN
Returns the minimum value in a list of arguments
Input: MIN(LOOKUP(Stocks.in))
Result: 5
MINUTE
Converts a serial number to a minute
Input: MINUTE(TODAY())
Result: 30 (assuming for the sake of example that time is 10:30 AM at that given moment)
MIRR
Returns the internal rate of return where positive and negative cash flows are financed at different rates
Input: MIRR(LOOKUP(Fetch_new.irr),2%,8%)
Result: -0.26
MOD
Returns the remainder from division
Input: MOD(10,4)
Result: 2
MODEMULT
Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data
Input: MODEMULT(100,LOOKUP(Stocks.in),LOOKUP(Stocks.out))
Result: 100
MODESNGL
Returns the most common value in a data set
Input: MODESNGL(100,LOOKUP(Stocks.in),LOOKUP(Stocks.out))
Result: 100
MONTH
Converts a serial number to a month
Input: MONTH(“7/20/1969”)
Result: 7
Also,
Input: MONTH(78866)
Result: 12
MROUND
Returns a number rounded to the desired multiple
Input: MROUND(21, 14)
Result: 28
MULTINOMIAL
Returns the multinomial of a set of numbers
Input: MULTINOMIAL(1,2)
Result: 3
NEGBINOMDIST
Returns the negative binomial distribution
Input: NEGBINOMDIST(4, 2, 0.1)
Result: 0.032805
NETWORKDAYS
Returns the number of whole workdays between two dates
Syntax: NETWORKDAYS(start_date, end_date, [holidays])
Input: NETWORKDAYS(“7/16/1969”,“7/24/1969”,LOOKUP(FormulaTest.date)
Result: 3
NOMINAL
Returns the annual nominal interest rate
Input: NOMINAL(4%, 12)
Result: 0.03928487739
NOW
Returns the current date and time.
Input: NOW()
Result: Wed Jan 09 2019 19:42:16 GMT+0530 (India Standard Time)
OCT2BIN
Converts an octal number to binary
Input: OCT2BIN(37,5)
Result: 11111
OCT2DEC
Converts an octal number to decimal
Input: OCT2DEC(37,5)
Result: 0001F
OCT2HEX
Converts an octal number to hexadecimal
Input: OCT2HEX(37,5)
Result: 0001F
ODD
Rounds a number up to the nearest odd integer
Input: odd(9998)
Result: 9999
OR
Returns TRUE if any argument is TRUE
Input: OR(2=6,5>6)
Result: FALSE
PMT
Returns the periodic payment for an annuity
Input: PMT(10%/12,20*12,2500000)
Result: -$24,125.54
POWER
Returns the result of a number raised to a power
Input: power(2,-3)
Result: 0.125
PRODUCT
Multiplies its arguments
Input: PRODUCT(1,2,3,4,5)
Result: 120
PROPER
Capitalizes the first letter in each word of a text value
Input: PROPER(“new year”)
Result: New Year
RANKAVG
Returns the rank of a number in a list of numbers
Input: RANKAVG(10,LOOKUP(Stocks.out),TRUE)
Result: 2
RIGHT
Returns the rightmost characters from a text value
Input: RIGHT(“Today is Wednesday”,10)
Result: Wednesday
ROMAN
Converts an Arabic numeral to roman, as text
Input: roman(50,4)
Result: L
ROUND
Rounds a number to a specified number of digits
Input: round(12.456829,5)
Result: 12.45683
ROUNDDOWN
Rounds a number down, toward zero
Input: ROUNDDOWN(16.657567567,5)
Result: 16.65756
ROUNDUP
Rounds a number up, away from zero
Input: ROUNDUP(99.02,1)
Result: 99.1
SEARCH
Finds one text value within another (not case-sensitive)
Input: SEARCH(“ghh”,“rklklghhyug”,2)
Result: 6
SEC
Returns the secant of an angle
Input: SEC(3.14)
Result: -1.000001268
SECH
Returns the hyperbolic secant of an angle
Input: SECH(9)
Result: 0.0002468196044
SECOND
Converts a serial number to a second
Input: SECOND(“11:40:59 AM”)
Result: 59
SERIESSUM
Returns the sum of a power series based on the formula
Input: SERIESSUM(2,3,1,5)
Result: 40
SIN
Returns the sine of the given angle
Input: SIN(90)
Result: 0.8939966636
SINH
Returns the hyperbolic sine of a number
Input: SINH(5)
Result: 74.20321058
STANDARDIZE
Returns a normalized value
Input: STANDARDIZE(90,56,7.9)
Result: 4.303797468
STDEVS
Estimates standard deviation based on a sample
Input: STDEVS(1,5,7,8)
Result:
STDEVA
Estimates standard deviation based on a sample, including numbers, text, and logical values
Input: STDEVA(1,5,7,6)
Result: 2.62995564
STDEVP
Calculates standard deviation based on the entire population
Input: STDEVP(1,5,7,6)
Result: 2.277608395
STDEVPA
Calculates standard deviation based on the entire population, including numbers, text, and logical values
Input: STDEVPA(1,5,7,8)
Result: 2.680951324
SUM
Adds its arguments
Input: SUM(1,2,3)
Result: 6
SUMIF
Adds the cells specified by a given criteria
Input: SUMIF({1,2,3,4,5},"<5")
Result: 10
SUMSQ
Returns the sum of the squares of the arguments
Input: SUMSQ(453,545)
Result: 502234
T
Converts its arguments to text
Input: T(“QWErty”)
Result: QWErty
TAN
Returns the tangent of a number
Input: TAN(PI/2)
Result: 16331200000000000
TANH
Returns the hyperbolic tangent of a number
Input: TANH(1)
Result: 0.761594156
TIME
Returns the serial number of a particular time
Input: TIME(11, 40, 59)
Result: 0.486793981481481
TIMEVALUE
Converts a time in the form of text to a serial number
Input: TIMEVALUE(TODAY())
Result: 0.538912037037037
TODAY
Returns the Current or Today’s date
Input: TODAY()
Result: 2020-07-22T08:00:25.406Z
TRIM
Removes spaces from text
Input: TRIM(“lorem ipsum”)
Result: lorem ipsum
TRUE
Returns the logical value TRUE
Input: NOT(TRUE)
Result: FALSE
TRUNC
Truncates a number to an integer
Input: TRUNC(3.141592654, 2)
Result: 3.14
UNICODE
Returns the number (code point) that corresponds to the first character of the text
Input: UNICODE(“Alphabet”)
Result: 65
UNIQUE
Returns a list of unique values in a list or range
Input: UNIQUE(LOOKUP(Stocks.in))
UPPER
Converts text to uppercase
Input: upper(“werewrrw”)
Result: WEREWRRW
WEEKDAY
Converts a serial number to a day of the week where the return type specifies the first day of the week. For example: 2 means weekdays are numbered as Numbers 1 (Monday) through 7 (Sunday). 3 means weekdays are numbered as Numbers 0(Monday) through 6 (Sunday).
Input: WEEKDAY(“7/20/2020”, 2)
Result: 4
WEEKNUM
Converts a serial number to a number representing where the week number is numerically within a year
Input: WEEKNUM(“8/20/2020”, 1)
Result: 34
Input: WEEKNUM(7774577)
Result: 4
XNPV
Returns the net present value for a schedule of cash flows that is not necessarily periodic
Input: XNPV(8%,LOOKUP(Stocks.in),LOOKUP(Stocks.out))
Result:
XOR
Returns a logical exclusive OR of all arguments
Input: XOR(1=2, 3>5) (All false)
Result: FALSE
YEAR
Converts a serial number to a year
Input: YEAR(1999)
Result: 1905
YEARFRAC
Returns the year fraction representing the number of whole days between start_date and end_date
Input: YEARFRAC(“7/16/1969”, “7/16/2019”, 1)
Result: 50