TrueData Sheets Built-In Formulas | Market Data Functions

+ K

Searching...

functions Formulas

menu_book Pages

manage_search Content Match

search_off

No results for ""

Go to Platform open_in_new

Loading Content

functions

Formula Reference

Built-In Formulas

Formula	Category	Description
â–¶ UNIQUE	Array	Remove duplicates, return unique values
Summary Remove duplicates, return unique values Parameters range Pro Tip Spills automatically. Use for building dynamic symbol lists. Result Result: 1,2,3,4,5
â–¶ RANDARRAY	Array	Fill grid with random numbers
Summary Fill grid with random numbers Parameters rows, cols, min, max Pro Tip Recalculates on every sheet change. Result Result: 3×2 random grid
â–¶ TOROW	Array	Flatten range into a single horizontal row
Summary Flatten range into a single horizontal row Parameters range Pro Tip Pair with WRAPROWS to reshape back. Result Result: 1,2,3,4,5,6
â–¶ TOCOL	Array	Flatten range into a single vertical column
Summary Flatten range into a single vertical column Parameters range Pro Tip Useful for stacking multiple ranges. Result Result: 1,2,3,4,5,6
â–¶ TEXTSPLIT	Array	Split text into cells using a delimiter
Summary Split text into cells using a delimiter Parameters text, col_delimiter Pro Tip Use a second arg to split into rows instead of columns. Result Result: TCS \| RELIANCE
â–¶ WRAPROWS	Array	Wrap a list into a 2D table by row
Summary Wrap a list into a 2D table by row Parameters range, columns_per_row Pro Tip Fills left to right. Pair with WRAPCOLS for column-first. Result Result: 3×2 grid
â–¶ WRAPCOLS	Array	Wrap a list into a 2D table by column
Summary Wrap a list into a 2D table by column Parameters range, rows_per_col Pro Tip Fills top to bottom. Opposite direction from WRAPROWS. Result Result: 2×3 grid
â–¶ TAKE	Array	Return first or last N rows from a range
Summary Return first or last N rows from a range Parameters range, rows, [cols] Pro Tip Use negative number to take from the bottom: =TAKE(A1:C6, -3) Result Result: First 3 rows
â–¶ DROP	Array	Remove first or last N rows from a range
Summary Remove first or last N rows from a range Parameters range, rows, [cols] Pro Tip Use negative number to drop from the bottom: =DROP(A1:C6, -2) Result Result: Rows 3–6
â–¶ SEQUENCE	Array	Generate sequential numbers with custom step
Summary Generate sequential numbers with custom step Parameters rows, [cols], [start], [step] Pro Tip =SEQUENCE(5) ? simple 1–5 list. Result Result: 1,4,7,10,13
â–¶ CHOOSEROWS	Array	Pick specific rows by row number
Summary Pick specific rows by row number Parameters range, row1, row2, … Pro Tip Rows can be repeated or reordered. Result Result: Rows 1,3,5
â–¶ CHOOSECOLS	Array	Pick specific columns by column number
Summary Pick specific columns by column number Parameters range, col1, col2, … Pro Tip Useful for reordering or hiding specific columns. Result Result: Col 1 and Col 3
â–¶ EXPAND	Array	Expand a range with padding to a larger size
Summary Expand a range with padding to a larger size Parameters range, rows, cols, [pad] Pro Tip Fills empty space with the pad value (default is empty string). Result Result: 4×4 block
â–¶ SORT	Array	Sort a range by a column, asc or desc
Summary Sort a range by a column, asc or desc Parameters range, [col_index], [order] Pro Tip Order: 1=ascending, -1=descending. Result Result: Sorted descending
â–¶ SORTBY	Array	Sort a range using values from another column
Summary Sort a range using values from another column Parameters range, sort_by_range, [order] Pro Tip Sort column does not need to be part of the returned result. Result Result: A sorted by B asc
â–¶ DATE	Date	Build a date from year, month, day numbers
Summary Build a date from year, month, day numbers Parameters year, month, day Pro Tip Useful when year/month/day are in separate cells. Result Result: 15-Jan-2024
â–¶ TIME	Date	Build a time value from hour, minute, second
Summary Build a time value from hour, minute, second Parameters hour, minute, second Pro Tip Returns a decimal between 0 and 1 (0=midnight, 0.5=noon). Result Result: 9:30:00 AM
â–¶ DAY	Date	Extract the day number (1–31) from a date
Summary Extract the day number (1–31) from a date Parameters date Pro Tip If A1=15-Jan-2024 ? 15. Result Result: 15
â–¶ MONTH	Date	Extract the month number (1–12) from a date
Summary Extract the month number (1–12) from a date Parameters date Pro Tip If A1=15-Jan-2024 ? 1. Result Result: 1
â–¶ YEAR	Date	Extract the 4-digit year from a date
Summary Extract the 4-digit year from a date Parameters date Pro Tip If A1=15-Jan-2024 ? 2024. Result Result: 2024
â–¶ HOUR	Date	Extract the hour (0–23) from a time value
Summary Extract the hour (0–23) from a time value Parameters time Pro Tip Works on both TIME values and full datetimes. Result Result: 9
â–¶ MINUTE	Date	Extract the minute (0–59) from a time value
Summary Extract the minute (0–59) from a time value Parameters time Pro Tip If A1=9:30 AM ? 30. Result Result: 30
â–¶ SECOND	Date	Extract the second (0–59) from a time value
Summary Extract the second (0–59) from a time value Parameters time Pro Tip If A1=9:30:45 AM ? 45. Result Result: 45
â–¶ NOW	Date	Return the current date and time
Summary Return the current date and time Parameters none Pro Tip Updates every time the sheet recalculates. Result Result: 23-Apr-2026
â–¶ DATEDIF	Date	Difference between two dates in days/months/years
Summary Difference between two dates in days/months/years Parameters start, end, unit Pro Tip Units: "D"=days, "M"=months, "Y"=years, "MD","YM" also work. Result Result: 60
â–¶ DATEVALUE	Date	Convert date text to a date serial number
Summary Convert date text to a date serial number Parameters date_text Pro Tip Format the result cell as Date to display it properly. Result Result: 45306
â–¶ DAYS	Date	Count calendar days between two dates
Summary Count calendar days between two dates Parameters end_date, start_date Pro Tip Note: end date is the FIRST argument. Result Result: 60
â–¶ DAYS360	Date	Days between dates on 360-day year basis
Summary Days between dates on 360-day year basis Parameters start_date, end_date, [method] Pro Tip Used in bond pricing. FALSE=US method, TRUE=European. Result Result: 58
â–¶ EDATE	Date	Return date exactly N months from a start date
Summary Return date exactly N months from a start date Parameters start_date, months Pro Tip Use negative months to go back: =EDATE(A1, -1) Result Result: 15-Apr-2024
â–¶ EOMONTH	Date	Return the last day of the month N months away
Summary Return the last day of the month N months away Parameters start_date, months Pro Tip =EOMONTH(A1, 1) gives last day of next month. Result Result: 31-Jan-2024
â–¶ ISOWEEKNUM	Date	ISO 8601 week number for a date
Summary ISO 8601 week number for a date Parameters date Pro Tip ISO weeks start Monday. Differs from WEEKNUM on edge dates. Result Result: 3
â–¶ NETWORKDAYS	Date	Count working days between two dates
Summary Count working days between two dates Parameters start, end, [holidays] Pro Tip Excludes Sat+Sun. Pass holiday range as 3rd arg. Result Result: 43
â–¶ NETWORKDAYS.INTL	Date	Working days with custom weekend definition
Summary Working days with custom weekend definition Parameters start, end, [weekend], [holidays] Pro Tip Code 11=Sunday only off. Code 17=Friday only off. Result Result: 48
â–¶ TIMEVALUE	Date	Convert time text to a decimal time value
Summary Convert time text to a decimal time value Parameters time_text Pro Tip 9:30 AM = 9.5/24 = 0.3958. Format as Time to display. Result Result: 0.3958
â–¶ WEEKNUM	Date	Week number of the year for a date
Summary Week number of the year for a date Parameters date, [return_type] Pro Tip Type 2=week starts Monday. Default type 1=starts Sunday. Result Result: 3
â–¶ WEEKDAY	Date	Day-of-week number for a date
Summary Day-of-week number for a date Parameters date, [type] Pro Tip Type 2: 1=Mon, 7=Sun. Use with IF to skip weekends. Result Result: 1
â–¶ WORKDAY	Date	Date that is N working days from start
Summary Date that is N working days from start Parameters start, days, [holidays] Pro Tip Use negative days to go backward. Result Result: 29-Jan-2024
â–¶ WORKDAY.INTL	Date	Working days from start with custom weekends
Summary Working days from start with custom weekends Parameters start, days, [weekend], [holidays] Pro Tip Code 11=Sunday only off. Useful for 6-day trading weeks. Result Result: 27-Jan-2024
â–¶ YEARFRAC	Date	Fraction of year between two dates
Summary Fraction of year between two dates Parameters start, end, [basis] Pro Tip Basis: 0=US 30/360, 1=Actual/Actual, 3=Actual/365. Result Result: 0.1644
â–¶ ACCRINT	Finance	Accrued interest for periodic-interest security
Summary Accrued interest for periodic-interest security Parameters issue, first_interest, settlement, rate, par, freq, [basis] Pro Tip Issue=Jan 1, Settlement=Apr 1 ? 3 months at 8% semi-annual = ?4,000. Result Result: ?4,000
â–¶ PMT	Finance	Fixed periodic payment to repay a loan
Summary Fixed periodic payment to repay a loan Parameters rate_per_period, total_periods, loan_amount Pro Tip Divide annual rate by 12 for monthly. Raw PMT is negative - wrap in ABS(). Result Result: ?9,435
â–¶ FV	Finance	Future value of an investment with periodic payments
Summary Future value of an investment with periodic payments Parameters rate, periods, payment, [pv], [type] Pro Tip Payment is negative (outflow). 10yr SIP of ?5K/month at 8% pa. Result Result: ?9,10,864
â–¶ DB	Finance	Depreciation using fixed-declining balance method
Summary Depreciation using fixed-declining balance method Parameters cost, salvage, life, period, [month] Pro Tip Rate = 1-(salvage/cost)^(1/life) ˜ 36.9%. Applied to remaining book value each year. Result Result: ?1,84,500
â–¶ DDB	Finance	Depreciation using double-declining balance
Summary Depreciation using double-declining balance Parameters cost, salvage, life, period, [factor] Pro Tip Default factor=2. Never depreciates below salvage value. Result Result: ?2,00,000
â–¶ DOLLAR	Finance	Format a number as currency text
Summary Format a number as currency text Parameters number, [decimal_places] Pro Tip Result is TEXT - cannot be used in math. Use negative decimals to round. Result Result: "$1,234.57"
â–¶ DOLLARDE	Finance	Convert fractional bond price to decimal
Summary Convert fractional bond price to decimal Parameters fractional_price, fraction_denominator Pro Tip 99.16 means 99 + 16/32 = 99.5. Common in US Treasury markets. Result Result: 99.5
â–¶ DOLLARFR	Finance	Convert decimal bond price to fractional notation
Summary Convert decimal bond price to fractional notation Parameters decimal_price, fraction_denominator Pro Tip Reverse of DOLLARDE. 0.5×32=16 ? 99.16. Result Result: 99.16
â–¶ EFFECT	Finance	Effective annual rate from nominal rate
Summary Effective annual rate from nominal rate Parameters nominal_rate, compounding_periods Pro Tip (1+0.12/12)^12-1=12.68%. More compounding ? higher effective rate. Result Result: 12.68%
â–¶ FVSCHEDULE	Finance	Future value with variable interest rates
Summary Future value with variable interest rates Parameters principal, rates_range Pro Tip 1,00,000×1.08×1.09×1.07=?1,25,971. Use for floating-rate bonds. Result Result: ?1,25,971
â–¶ IRR	Finance	Internal rate of return on a set of cash flows
Summary Internal rate of return on a set of cash flows Parameters cashflows_range, [guess] Pro Tip First value must be negative (initial outflow). Add guess if #NUM! error. Result Result: ~12.4%
â–¶ IPMT	Finance	Interest portion of a specific EMI payment
Summary Interest portion of a specific EMI payment Parameters rate, period_no, total_periods, loan Pro Tip IPMT+PPMT=total PMT. Interest portion decreases over time. Result Result: ?2,083
â–¶ ISPMT	Finance	Interest paid under straight-line principal repayment
Summary Interest paid under straight-line principal repayment Parameters rate, period_no, total_periods, loan Pro Tip Differs from IPMT: uses flat principal (÷nper), interest declines linearly. Result Result: ?2,049
â–¶ NPV	Finance	Net present value of discounted future cash flows
Summary Net present value of discounted future cash flows Parameters discount_rate, cashflow1, cashflow2, … Pro Tip Add initial investment separately as negative. If NPV>0, investment adds value. Result Result: ?45,230
â–¶ NOMINAL	Finance	Nominal rate from effective annual rate
Summary Nominal rate from effective annual rate Parameters effective_rate, compounding_periods Pro Tip Reverse of EFFECT. Use to compare products across compounding frequencies. Result Result: 12%
â–¶ NPER	Finance	Periods to pay off a loan or reach a savings goal
Summary Periods to pay off a loan or reach a savings goal Parameters rate, payment, pv, [fv], [type] Pro Tip Payment must be negative (outflow). Result Result: 60 months
â–¶ PDURATION	Finance	Periods to reach a future value (no payments)
Summary Periods to reach a future value (no payments) Parameters rate, present_value, future_value Pro Tip ln(FV/PV)÷ln(1+rate). Divide by 12 for years. Simpler than NPER for lump sum. Result Result: 104.32 months
â–¶ PPMT	Finance	Principal portion of a specific EMI payment
Summary Principal portion of a specific EMI payment Parameters rate, period_no, total_periods, loan Pro Tip PPMT+IPMT=total PMT: ?7,352+?2,083=?9,435. Build amortization schedules. Result Result: ?7,352
â–¶ PV	Finance	Present value of future payments or a lump sum
Summary Present value of future payments or a lump sum Parameters rate, periods, payment, [fv], [type] Pro Tip Reverse of FV. Payment is negative. For lump sum: =ABS(PV(0.1, 5, 0, -100000)) Result Result: ?2,59,854
â–¶ SYD	Finance	Depreciation using Sum-of-Years Digits method
Summary Depreciation using Sum-of-Years Digits method Parameters cost, salvage, life, period Pro Tip Year 1 fraction=5/15. Sum 1+2+3+4+5=15. ?4,50,000×5/15=?1,50,000. Result Result: ?1,50,000
â–¶ TBILLPRICE	Finance	Price per ?100 of a Treasury Bill
Summary Price per ?100 of a Treasury Bill Parameters settlement, maturity, discount_rate Pro Tip Settlement and maturity must be within 1 year. Price is always <100. Result Result: 98.39
â–¶ TBILLYIELD	Finance	Yield of a Treasury Bill from its market price
Summary Yield of a Treasury Bill from its market price Parameters settlement, maturity, price_per_100 Pro Tip Reverse of TBILLPRICE. Yield rises when price falls. Result Result: 6.5%
â–¶ ISBLANK	Info	Check if a cell is completely empty
Summary Check if a cell is completely empty Parameters cell_reference Pro Tip Space or empty string ("") returns FALSE - not blank. Result Result: FALSE
â–¶ ISBINARY	Info	Check if a value contains only 0s and 1s
Summary Check if a value contains only 0s and 1s Parameters value_or_cell Pro Tip If A1="1010" ? TRUE. If A1="1023" ? FALSE. Result Result: TRUE
â–¶ ISEVEN	Info	Check if a number is even
Summary Check if a number is even Parameters number_or_cell Pro Tip Decimals are truncated: =ISEVEN(4.9) ? TRUE. Result Result: TRUE
â–¶ ISODD	Info	Check if a number is odd
Summary Check if a number is odd Parameters number_or_cell Pro Tip Opposite of ISEVEN. =ISODD(3.9) ? TRUE. Result Result: FALSE
â–¶ ISNONTEXT	Info	Check if a cell does NOT contain text
Summary Check if a cell does NOT contain text Parameters value_or_cell Pro Tip Blanks return TRUE. Exact opposite of ISTEXT. Result Result: TRUE
â–¶ ISNUMBER	Info	Check if a cell contains a numeric value
Summary Check if a cell contains a numeric value Parameters value_or_cell Pro Tip "2520" stored as text returns FALSE. Dates return TRUE. Result Result: TRUE
â–¶ ISTEXT	Info	Check if a cell contains a text value
Summary Check if a cell contains a text value Parameters value_or_cell Pro Tip Numbers, dates, blanks, errors all return FALSE. Result Result: TRUE
â–¶ N	Info	Convert a value to its numeric equivalent
Summary Convert a value to its numeric equivalent Parameters value_or_cell Pro Tip TRUE?1, FALSE?0, text?0, date?serial number. Result Result: 1
â–¶ IF	Logic	Return one of two values based on a condition
Summary Return one of two values based on a condition Parameters condition, value_if_true, value_if_false Pro Tip Nest up to 64 IFs, but use IFS() for many conditions. Result Result: "Above"
â–¶ AND	Logic	TRUE only if ALL conditions are true
Summary TRUE only if ALL conditions are true Parameters condition1, condition2, … Pro Tip Even one false condition makes the whole result FALSE. Result Result: TRUE
â–¶ NOT	Logic	Reverse a logical value (TRUE?FALSE, FALSE?TRUE)
Summary Reverse a logical value (TRUE?FALSE, FALSE?TRUE) Parameters condition Pro Tip =NOT(ISBLANK(A1)) ? TRUE means the cell has data. Result Result: TRUE
â–¶ OR	Logic	TRUE if AT LEAST ONE condition is true
Summary TRUE if AT LEAST ONE condition is true Parameters condition1, condition2, … Pro Tip Returns FALSE only when every condition is false. Result Result: TRUE
â–¶ FALSE	Logic	Return the logical value FALSE
Summary Return the logical value FALSE Parameters none Pro Tip Used as argument: =VLOOKUP(A1, B:C, 2, FALSE) for exact match. Result Result: FALSE
â–¶ TRUE	Logic	Return the logical value TRUE
Summary Return the logical value TRUE Parameters none Pro Tip Numeric value of TRUE=1. =TRUE()+TRUE() ? 2. Result Result: TRUE
â–¶ INDEX	Lookup	Return value at a specific row/column in a range
Summary Return value at a specific row/column in a range Parameters range, row_number, [col_number] Pro Tip Classic: =INDEX(C:C, MATCH("TCS", A:A, 0)). Can look left, unlike VLOOKUP. Result Result: ?2520
â–¶ XMATCH	Lookup	Find position of a value - modern MATCH
Summary Find position of a value - modern MATCH Parameters search_value, search_range, [match_mode] Pro Tip Match mode 2=wildcard. Search mode -1=reverse search. Result Result: 5
â–¶ XLOOKUP	Lookup	Modern flexible replacement for VLOOKUP/HLOOKUP
Summary Modern flexible replacement for VLOOKUP/HLOOKUP Parameters search_value, search_range, return_range, [if_not_found] Pro Tip Return column can be LEFT of search column. 4th arg handles #N/A. Result Result: ?2520
â–¶ LOOKUP	Lookup	Find a value and return from same position in another range
Summary Find a value and return from same position in another range Parameters search_value, search_range, result_range Pro Tip Assumes sorted ascending. Use XLOOKUP for exact match. Result Result: "TCS"
â–¶ HLOOKUP	Lookup	Search top row of table, return value from a specified row
Summary Search top row of table, return value from a specified row Parameters search_value, table, row_number, [exact] Pro Tip Always use FALSE for exact match. XLOOKUP is the modern replacement. Result Result: ?2520
â–¶ VLOOKUP	Lookup	Search left column of table, return from specified column
Summary Search left column of table, return from specified column Parameters search_value, table, col_number, [exact] Pro Tip Search value must be in the FIRST column of range. Result Result: ?2520
â–¶ MATCH	Lookup	Find position of a value in a range
Summary Find position of a value in a range Parameters search_value, search_range, [match_type] Pro Tip Always use 0 for exact match. Pair with INDEX for two-way lookup. Result Result: 5
â–¶ CHOOSE	Lookup	Pick a value from a list by index number
Summary Pick a value from a list by index number Parameters index, value1, value2, … Pro Tip Use with WEEKDAY for day names: =CHOOSE(WEEKDAY(A1, 2), "Mon", "Tue", ...) Result Result: "F&O"
â–¶ ABS	Numbers	Absolute value - remove the negative sign
Summary Absolute value - remove the negative sign Parameters number Pro Tip Use to display PMT, IPMT, PV results as positive. Result Result: 150
â–¶ ACOS	Numbers	Arccosine (inverse cosine) of a number, in radians
Summary Arccosine (inverse cosine) of a number, in radians Parameters number (-1 to 1) Pro Tip Result is in radians. Wrap in DEGREES() to convert: =DEGREES(ACOS(0.5)) ? 60. Result Result: 1.0472 (p/3)
â–¶ ACOSH	Numbers	Inverse hyperbolic cosine of a number
Summary Inverse hyperbolic cosine of a number Parameters number (=1) Pro Tip Inverse of COSH. =ACOSH(1) ? 0. Result Result: 1.3170
â–¶ ACOT	Numbers	Arccotangent of a number, in radians
Summary Arccotangent of a number, in radians Parameters number Pro Tip Result in radians. =DEGREES(ACOT(1)) ? 45. Result Result: 0.7854 (p/4)
â–¶ ACOTH	Numbers	Inverse hyperbolic cotangent of a number
Summary Inverse hyperbolic cotangent of a number Parameters number (\|x\|>1) Pro Tip Defined only for \|x\|>1. Returns #NUM! for \|x\|=1. Result Result: 0.5493
â–¶ ADD	Numbers	Add two numbers (Google Sheets operator function)
Summary Add two numbers (Google Sheets operator function) Parameters value1, value2 Pro Tip Equivalent to =A1+B1. Useful inside ARRAYFORMULA. Result Result: 150
â–¶ ARABIC	Numbers	Convert a Roman numeral text string to a number
Summary Convert a Roman numeral text string to a number Parameters roman_numeral_text Pro Tip Reverse of ROMAN. =ARABIC("MMXXIV") ? 2024. Result Result: 14
â–¶ ASIN	Numbers	Arcsine (inverse sine) of a number, in radians
Summary Arcsine (inverse sine) of a number, in radians Parameters number (-1 to 1) Pro Tip =DEGREES(ASIN(0.5)) ? 30. Use for angle calculations. Result Result: 0.5236 (p/6)
â–¶ ASINH	Numbers	Inverse hyperbolic sine of a number
Summary Inverse hyperbolic sine of a number Parameters number Pro Tip Inverse of SINH. Works for all real numbers. Result Result: 0.8814
â–¶ ATAN	Numbers	Arctangent of a number, in radians
Summary Arctangent of a number, in radians Parameters number Pro Tip =DEGREES(ATAN(1)) ? 45. Returns result between -p/2 and p/2. Result Result: 0.7854 (p/4)
â–¶ ATAN2	Numbers	Arctangent of y/x using both coordinates (avoids divide-by-zero)
Summary Arctangent of y/x using both coordinates (avoids divide-by-zero) Parameters x_coordinate, y_coordinate Pro Tip Note: x comes FIRST. =DEGREES(ATAN2(0, 1)) ? 90. Result Result: 0.7854
â–¶ ATANH	Numbers	Inverse hyperbolic tangent of a number
Summary Inverse hyperbolic tangent of a number Parameters number (-1 to 1 exclusive) Pro Tip Inverse of TANH. Undefined at ±1, returns #NUM!. Result Result: 0.5493
â–¶ AVEDEV	Numbers	Average of absolute deviations from the mean
Summary Average of absolute deviations from the mean Parameters number1, number2, … or range Pro Tip Measures dispersion. Similar to std dev but uses absolute differences, not squares. Result Result: 3.2
â–¶ AVERAGE	Numbers	Arithmetic mean of a range
Summary Arithmetic mean of a range Parameters number1, number2, … or range Pro Tip Ignores text and blanks. AVERAGEA includes logical values. Result Result: 2520.56
â–¶ AVERAGEA	Numbers	Mean including text (=0) and logical values
Summary Mean including text (=0) and logical values Parameters value1, value2, … or range Pro Tip Text counts as 0. TRUE=1, FALSE=0. Wider than AVERAGE. Result Result: 2100
â–¶ BASE	Numbers	Convert a number to a text string in a given base
Summary Convert a number to a text string in a given base Parameters number, base, [min_length] Pro Tip Base 2=binary, 8=octal, 16=hex. Reverse is DECIMAL. Result Result: "FF"
â–¶ BINOMDIST	Numbers	Binomial distribution probability (legacy syntax)
Summary Binomial distribution probability (legacy syntax) Parameters successes, trials, probability, cumulative Pro Tip FALSE=exact probability. TRUE=cumulative (P = x). Use BINOM.DIST in newer Excel. Result Result: 0.1172
â–¶ BINOM.DIST	Numbers	Binomial distribution probability (modern syntax)
Summary Binomial distribution probability (modern syntax) Parameters successes, trials, probability, cumulative Pro Tip Same as BINOMDIST. FALSE=exact, TRUE=cumulative P(X=k). Result Result: 0.1172
â–¶ BINOM.DIST.RANGE	Numbers	Binomial probability for a range of successes
Summary Binomial probability for a range of successes Parameters trials, probability, lower, [upper] Pro Tip Gives P(lower = X = upper) in one call. No need to subtract two BINOM.DIST results. Result Result: 0.5683
â–¶ BINOM.INV	Numbers	Smallest k where cumulative binomial = target probability
Summary Smallest k where cumulative binomial = target probability Parameters trials, probability, alpha Pro Tip Inverse of BINOM.DIST. Use for quality control thresholds. Result Result: 7
â–¶ BITAND	Numbers	Bitwise AND of two non-negative integers
Summary Bitwise AND of two non-negative integers Parameters number1, number2 Pro Tip 12=1100, 10=1010, AND=1000=8. Useful in flag/permission logic. Result Result: 8
â–¶ BITLSHIFT	Numbers	Shift a number left by N bits (multiply by 2^N)
Summary Shift a number left by N bits (multiply by 2^N) Parameters number, shift_amount Pro Tip 4 << 2 = 4×4 = 16. Equivalent to =number * POWER(2, shift_amount). Result Result: 16
â–¶ BITOR	Numbers	Bitwise OR of two non-negative integers
Summary Bitwise OR of two non-negative integers Parameters number1, number2 Pro Tip 12=1100, 10=1010, OR=1110=14. Result Result: 14
â–¶ BITRSHIFT	Numbers	Shift a number right by N bits (divide by 2^N)
Summary Shift a number right by N bits (divide by 2^N) Parameters number, shift_amount Pro Tip 16 >> 2 = 16÷4 = 4. Opposite of BITLSHIFT. Result Result: 4
â–¶ BITXOR	Numbers	Bitwise XOR (exclusive OR) of two integers
Summary Bitwise XOR (exclusive OR) of two integers Parameters number1, number2 Pro Tip 12=1100, 10=1010, XOR=0110=6. Bits differ ? 1, same ? 0. Result Result: 6
â–¶ CEILING	Numbers	Round up to nearest multiple of significance
Summary Round up to nearest multiple of significance Parameters number, significance Pro Tip =CEILING(2.5, 0.5) ? 3. Always rounds away from zero. Result Result: 5
â–¶ COMBIN	Numbers	Number of combinations (n choose k)
Summary Number of combinations (n choose k) Parameters n, k Pro Tip Order does not matter. Repetition not allowed. Result Result: 120
â–¶ COMBINA	Numbers	Combinations with repetition allowed
Summary Combinations with repetition allowed Parameters n, k Pro Tip (n+k-1) choose k. Use when same item can be chosen multiple times. Result Result: 10
â–¶ COMPLEX	Numbers	Create a complex number from real and imaginary parts
Summary Create a complex number from real and imaginary parts Parameters real, imaginary, [suffix] Pro Tip Default suffix is "i". Use "j" for engineering notation. Returns text. Result Result: "3+4i"
â–¶ CORREL	Numbers	Correlation coefficient between two data sets
Summary Correlation coefficient between two data sets Parameters array1, array2 Pro Tip 1=perfect positive, -1=perfect negative, 0=no correlation. Result Result: 0.89
â–¶ COS	Numbers	Cosine of an angle given in radians
Summary Cosine of an angle given in radians Parameters angle_in_radians Pro Tip Convert degrees first: =COS(RADIANS(60)) ? 0.5. Result Result: 0.5
â–¶ COSH	Numbers	Hyperbolic cosine of a number
Summary Hyperbolic cosine of a number Parameters number Pro Tip =COSH(0) ? 1. Always = 1. Inverse is ACOSH. Result Result: 1.5431
â–¶ COT	Numbers	Cotangent of an angle in radians
Summary Cotangent of an angle in radians Parameters angle_in_radians Pro Tip =1/TAN(x). =COT(RADIANS(45)) ? 1. Result Result: 1
â–¶ COTH	Numbers	Hyperbolic cotangent of a number
Summary Hyperbolic cotangent of a number Parameters number (?0) Pro Tip =COSH(x)/SINH(x). Undefined at 0, returns #DIV/0!. Result Result: 1.0373
â–¶ COUNT	Numbers	Count cells containing numbers
Summary Count cells containing numbers Parameters value1, value2, … or range Pro Tip Ignores text and blanks. Use COUNTA for all non-empty. Result Result: 15
â–¶ COUNTA	Numbers	Count all non-empty cells (any type)
Summary Count all non-empty cells (any type) Parameters value1, value2, … or range Pro Tip Includes text, numbers, errors, logicals. Only skips blanks. Result Result: 18
â–¶ COUNTBLANK	Numbers	Count empty cells in a range
Summary Count empty cells in a range Parameters range Pro Tip Cells with "" (empty string) are also counted as blank. Result Result: 2
â–¶ COVAR	Numbers	Population covariance between two data sets (legacy)
Summary Population covariance between two data sets (legacy) Parameters array1, array2 Pro Tip Divides by N. Use COVARIANCE.P or COVARIANCE.S in modern Excel. Result Result: 12.4
â–¶ COVARIANCE.P	Numbers	Population covariance (modern syntax)
Summary Population covariance (modern syntax) Parameters array1, array2 Pro Tip Same result as COVAR. Preferred in newer Excel versions. Result Result: 12.4
â–¶ COVARIANCE.S	Numbers	Sample covariance between two data sets
Summary Sample covariance between two data sets Parameters array1, array2 Pro Tip Divides by N-1. Use when data is a sample, not full population. Result Result: 13.8
â–¶ CSC	Numbers	Cosecant of an angle in radians (1/sin)
Summary Cosecant of an angle in radians (1/sin) Parameters angle_in_radians Pro Tip =1/SIN(x). =CSC(RADIANS(30)) ? 2. Undefined at multiples of p. Result Result: 2
â–¶ CSCH	Numbers	Hyperbolic cosecant of a number
Summary Hyperbolic cosecant of a number Parameters number (?0) Pro Tip =1/SINH(x). Undefined at 0. Result Result: 0.8509
â–¶ DEC2BIN	Numbers	Convert decimal integer to binary text
Summary Convert decimal integer to binary text Parameters number, [places] Pro Tip Max input: 511 (positive) or -512 (negative). Use places to zero-pad. Result Result: "1010"
â–¶ DEC2HEX	Numbers	Convert decimal integer to hexadecimal text
Summary Convert decimal integer to hexadecimal text Parameters number, [places] Pro Tip =DEC2HEX(255, 4) ? '00FF'. Useful for color codes. Result Result: "FF"
â–¶ DEC2OCT	Numbers	Convert decimal integer to octal text
Summary Convert decimal integer to octal text Parameters number, [places] Pro Tip Max input: 536870911. Use for Unix file permission values. Result Result: "10"
â–¶ DECIMAL	Numbers	Convert a number string in any base to decimal
Summary Convert a number string in any base to decimal Parameters text, base Pro Tip Reverse of BASE. =DECIMAL("1010", 2) ? 10. Result Result: 255
â–¶ DEGREES	Numbers	Convert radians to degrees
Summary Convert radians to degrees Parameters angle_in_radians Pro Tip =DEGREES(PI()/4) ? 45. Reverse is RADIANS. Result Result: 180
â–¶ DELTA	Numbers	Test whether two numbers are equal (returns 1 or 0)
Summary Test whether two numbers are equal (returns 1 or 0) Parameters number1, [number2] Pro Tip Returns 1 if equal, 0 if not. Default number2 is 0. Useful in SUMPRODUCT arrays. Result Result: 1
â–¶ DEVSQ	Numbers	Sum of squared deviations from the mean
Summary Sum of squared deviations from the mean Parameters number1, number2, … or range Pro Tip Building block for variance. =DEVSQ(range)/(COUNT(range)-1) = VAR. Result Result: 243.6
â–¶ DIVIDE	Numbers	Divide two numbers (Google Sheets operator function)
Summary Divide two numbers (Google Sheets operator function) Parameters dividend, divisor Pro Tip Equivalent to =10/4. Useful inside ARRAYFORMULA. Result Result: 2.5
â–¶ EQ	Numbers	Check if two values are equal (returns TRUE/FALSE)
Summary Check if two values are equal (returns TRUE/FALSE) Parameters value1, value2 Pro Tip Same as =A1=B1. Useful inside ARRAYFORMULA for element-wise comparison. Result Result: TRUE
â–¶ ERF	Numbers	Error function - integral of Gaussian distribution
Summary Error function - integral of Gaussian distribution Parameters lower, [upper] Pro Tip ERF(1)=84.27% - probability within 1s of mean in normal distribution. Result Result: 0.8427
â–¶ ERFC	Numbers	Complementary error function (1 - ERF)
Summary Complementary error function (1 - ERF) Parameters lower Pro Tip =1-ERF(x). Useful in statistics and signal processing. Result Result: 0.1573
â–¶ EVEN	Numbers	Round a number up to the nearest even integer
Summary Round a number up to the nearest even integer Parameters number Pro Tip =EVEN(2.1) ? 4. Negative: =EVEN(-3) ? -4. Rounds away from zero. Result Result: 4
â–¶ EXP	Numbers	e raised to the power of a number
Summary e raised to the power of a number Parameters number Pro Tip EXP(0)=1. Inverse is LN(). =EXP(LN(x))=x. Result Result: 2.7183
â–¶ FACT	Numbers	Factorial of a number (n!)
Summary Factorial of a number (n!) Parameters integer =0 Pro Tip 5!=5×4×3×2×1=120. FACT(0)=1. Max n=170. Result Result: 120
â–¶ FACTDOUBLE	Numbers	Double factorial of a number (n!!)
Summary Double factorial of a number (n!!) Parameters integer =-1 Pro Tip 7!!=7×5×3×1=105. For even n: product of even numbers down to 2. Result Result: 105
â–¶ FISHER	Numbers	Fisher transformation of a correlation coefficient
Summary Fisher transformation of a correlation coefficient Parameters correlation (-1 to 1) Pro Tip Converts r to z for hypothesis testing. =0.5×LN((1+x)/(1-x)). Result Result: 0.5493
â–¶ FISHERINV	Numbers	Inverse Fisher transformation - convert z back to r
Summary Inverse Fisher transformation - convert z back to r Parameters z_value Pro Tip Reverse of FISHER. Recover correlation from z-score. Result Result: 0.5
â–¶ FLOOR	Numbers	Round down to the nearest multiple of significance
Summary Round down to the nearest multiple of significance Parameters number, significance Pro Tip =FLOOR(2.7, 0.5) ? 2.5. Always rounds toward zero. Result Result: 4
â–¶ GAMMA	Numbers	Gamma function value for a number
Summary Gamma function value for a number Parameters number Pro Tip For positive integers, GAMMA(n) = (n-1)!. GAMMA(5)=4!=24. Result Result: 24
â–¶ GCD	Numbers	Greatest common divisor
Summary Greatest common divisor Parameters number1, number2, … Pro Tip =GCD(12, 8, 6) ? 2. Works with up to 255 numbers. Result Result: 4
â–¶ GEOMEAN	Numbers	Geometric mean of a set of positive numbers
Summary Geometric mean of a set of positive numbers Parameters number1, number2, … or range Pro Tip Use for growth rates: =GEOMEAN(1.08, 1.09, 1.07) ? average growth. Result Result: 2.6
â–¶ GESTEP	Numbers	Test whether a number is = a step value (returns 1 or 0)
Summary Test whether a number is = a step value (returns 1 or 0) Parameters number, [step] Pro Tip Returns 1 if number=step, else 0. Default step=0. Use in SUMPRODUCT filters. Result Result: 1
â–¶ GT	Numbers	Test if first value is greater than second (returns TRUE/FALSE)
Summary Test if first value is greater than second (returns TRUE/FALSE) Parameters value1, value2 Pro Tip Equivalent to =A1>B1. For use inside ARRAYFORMULA. Result Result: TRUE
â–¶ GTE	Numbers	Test if first value is greater than or equal to second
Summary Test if first value is greater than or equal to second Parameters value1, value2 Pro Tip Equivalent to =A1>=B1. For use inside ARRAYFORMULA. Result Result: TRUE
â–¶ HARMEAN	Numbers	Harmonic mean of a set of positive numbers
Summary Harmonic mean of a set of positive numbers Parameters number1, number2, … or range Pro Tip n ÷ S(1/x?). Use for averaging rates (speed, P/E ratios). Result Result: 3
â–¶ HEX2BIN	Numbers	Convert a hexadecimal text to binary
Summary Convert a hexadecimal text to binary Parameters hex_text, [places] Pro Tip Max 2 hex digits. Use places to zero-pad the result. Result Result: "1010"
â–¶ HEX2DEC	Numbers	Convert a hexadecimal text to decimal integer
Summary Convert a hexadecimal text to decimal integer Parameters hex_text Pro Tip Up to 10 hex characters. Handles negative via two's complement. Result Result: 255
â–¶ HEX2OCT	Numbers	Convert a hexadecimal text to octal
Summary Convert a hexadecimal text to octal Parameters hex_text, [places] Pro Tip Max 10 hex digits. Useful for cross-base debugging. Result Result: "12"
â–¶ IMABS	Numbers	Absolute value (modulus) of a complex number
Summary Absolute value (modulus) of a complex number Parameters complex_text Pro Tip v(3²+4²)=5. The magnitude of the complex number. Result Result: 5
â–¶ IMAGINARY	Numbers	Extract the imaginary coefficient of a complex number
Summary Extract the imaginary coefficient of a complex number Parameters complex_text Pro Tip Returns the b in a+bi. Pair with IMREAL for the real part. Result Result: 4
â–¶ IMCONJUGATE	Numbers	Complex conjugate - flip the sign of imaginary part
Summary Complex conjugate - flip the sign of imaginary part Parameters complex_text Pro Tip Used in complex division and signal processing. Result Result: "3-4i"
â–¶ IMCOS	Numbers	Cosine of a complex number
Summary Cosine of a complex number Parameters complex_text Pro Tip Extends cosine to complex domain. Result Result: "2.0327-3.0519i"
â–¶ IMCOSH	Numbers	Hyperbolic cosine of a complex number
Summary Hyperbolic cosine of a complex number Parameters complex_text Pro Tip Extends COSH to complex numbers. Result Result: "-0.6422+1.0686i"
â–¶ IMCOT	Numbers	Cotangent of a complex number
Summary Cotangent of a complex number Parameters complex_text Pro Tip =IMDIV(IMCOS(z), IMSIN(z)). Result Result: complex
â–¶ IMCSC	Numbers	Cosecant of a complex number (1/sin)
Summary Cosecant of a complex number (1/sin) Parameters complex_text Pro Tip =IMDIV(1, IMSIN(z)). Result Result: complex
â–¶ IMCSCH	Numbers	Hyperbolic cosecant of a complex number
Summary Hyperbolic cosecant of a complex number Parameters complex_text Pro Tip =IMDIV(1, IMSINH(z)). Result Result: complex
â–¶ IMDIV	Numbers	Divide one complex number by another
Summary Divide one complex number by another Parameters complex_numerator, complex_denominator Pro Tip (3+4i)÷(1+2i). Multiply by conjugate internally. Result Result: "2.2-0.4i"
â–¶ IMEXP	Numbers	Exponential of a complex number (e^z)
Summary Exponential of a complex number (e^z) Parameters complex_text Pro Tip Euler: e^(a+bi)=e^a×(cos b + i sin b). Result Result: "-1.1312+2.4718i"
â–¶ IMLN	Numbers	Natural logarithm of a complex number
Summary Natural logarithm of a complex number Parameters complex_text Pro Tip ln\|z\| + i×arg(z). Inverse of IMEXP. Result Result: "1.6094+0.9273i"
â–¶ IMPOWER	Numbers	Raise a complex number to a power
Summary Raise a complex number to a power Parameters complex_text, power Pro Tip (1+i)²=1+2i-1=2i. Result Result: "0+2i"
â–¶ IMPRODUCT	Numbers	Product of multiple complex numbers
Summary Product of multiple complex numbers Parameters complex1, complex2, … Pro Tip Multiplies all complex numbers together. Up to 255 arguments. Result Result: "-5+10i"
â–¶ IMREAL	Numbers	Extract the real coefficient of a complex number
Summary Extract the real coefficient of a complex number Parameters complex_text Pro Tip Returns the a in a+bi. Pair with IMAGINARY for the imaginary part. Result Result: 3
â–¶ IMSEC	Numbers	Secant of a complex number (1/cos)
Summary Secant of a complex number (1/cos) Parameters complex_text Pro Tip =IMDIV(1, IMCOS(z)). Result Result: complex
â–¶ IMSECH	Numbers	Hyperbolic secant of a complex number
Summary Hyperbolic secant of a complex number Parameters complex_text Pro Tip =IMDIV(1, IMCOSH(z)). Result Result: complex
â–¶ IMSIN	Numbers	Sine of a complex number
Summary Sine of a complex number Parameters complex_text Pro Tip Extends sine to complex domain. Result Result: "3.1658+1.9596i"
â–¶ IMSINH	Numbers	Hyperbolic sine of a complex number
Summary Hyperbolic sine of a complex number Parameters complex_text Pro Tip Extends SINH to complex numbers. Result Result: complex
â–¶ IMSQRT	Numbers	Square root of a complex number
Summary Square root of a complex number Parameters complex_text Pro Tip Principal square root. (2+i)²=4+4i-1=3+4i. Result Result: "2+i"
â–¶ IMSUB	Numbers	Subtract one complex number from another
Summary Subtract one complex number from another Parameters complex1, complex2 Pro Tip (5+3i)-(2+i)=3+2i. Result Result: "3+2i"
â–¶ IMSUM	Numbers	Sum of multiple complex numbers
Summary Sum of multiple complex numbers Parameters complex1, complex2, … Pro Tip Adds real and imaginary parts separately. Up to 255 arguments. Result Result: "4+6i"
â–¶ IMTAN	Numbers	Tangent of a complex number
Summary Tangent of a complex number Parameters complex_text Pro Tip =IMDIV(IMSIN(z), IMCOS(z)). Result Result: complex
â–¶ INT	Numbers	Round a number DOWN to the nearest integer
Summary Round a number DOWN to the nearest integer Parameters number Pro Tip Always rounds toward negative infinity: =INT(-4.1) ? -5. Result Result: 4
â–¶ LARGE	Numbers	Return the Nth largest value in a range
Summary Return the Nth largest value in a range Parameters range, n Pro Tip =LARGE(B:B, 1)=MAX. =LARGE(B:B, 2) is 2nd largest. Result Result: 2519
â–¶ LN	Numbers	Natural logarithm (base e)
Summary Natural logarithm (base e) Parameters number (>0) Pro Tip LN(e)=1. Inverse is EXP(). =LN(1)=0. Result Result: 1
â–¶ LOG	Numbers	Logarithm to a specified base
Summary Logarithm to a specified base Parameters number, [base] Pro Tip Default base is 10. LOG(1000, 10)=3. LOG(8, 2)=3. Result Result: 3
â–¶ LOG10	Numbers	Base-10 logarithm
Summary Base-10 logarithm Parameters number (>0) Pro Tip Same as =LOG(number, 10). LOG10(100)=2. Result Result: 3
â–¶ LT	Numbers	Test if first value is less than second (returns TRUE/FALSE)
Summary Test if first value is less than second (returns TRUE/FALSE) Parameters value1, value2 Pro Tip Equivalent to =A1<B1. For use inside ARRAYFORMULA. Result Result: TRUE
â–¶ LTE	Numbers	Test if first value is less than or equal to second
Summary Test if first value is less than or equal to second Parameters value1, value2 Pro Tip Equivalent to =A1<=B1. For use inside ARRAYFORMULA. Result Result: TRUE
â–¶ MAX	Numbers	Largest value in a range
Summary Largest value in a range Parameters number1, number2, … or range Pro Tip Ignores text and blanks. Use MAXA to include logicals. Result Result: 2527
â–¶ MEDIAN	Numbers	Middle value of a sorted data set
Summary Middle value of a sorted data set Parameters number1, number2, … or range Pro Tip For even count, averages the two middle values. Result Result: 2519
â–¶ MIN	Numbers	Smallest value in a range
Summary Smallest value in a range Parameters number1, number2, … or range Pro Tip Ignores text and blanks. Use MINA to include logicals. Result Result: 2510
â–¶ MINUS	Numbers	Subtract two numbers (Google Sheets operator function)
Summary Subtract two numbers (Google Sheets operator function) Parameters value1, value2 Pro Tip Equivalent to =10-4. Useful inside ARRAYFORMULA. Result Result: 6
â–¶ MOD	Numbers	Remainder after dividing by a divisor
Summary Remainder after dividing by a divisor Parameters number, divisor Pro Tip Same sign as divisor. Use for alternating rows, even/odd logic. Result Result: 1
â–¶ MROUND	Numbers	Round to the nearest multiple of a number
Summary Round to the nearest multiple of a number Parameters number, multiple Pro Tip =MROUND(18, 5) ? 20. =MROUND(23, 10) ? 20. Result Result: 15
â–¶ MULTINOMIAL	Numbers	Multinomial coefficient - (sum)! ÷ (n1!×n2!×…)
Summary Multinomial coefficient - (sum)! ÷ (n1!×n2!×…) Parameters number1, number2, … Pro Tip (2+3+4)!÷(2!×3!×4!)=9!÷(2×6×24)=1260. Used in combinatorics. Result Result: 1260
â–¶ MULTIPLY	Numbers	Multiply two numbers (Google Sheets operator function)
Summary Multiply two numbers (Google Sheets operator function) Parameters value1, value2 Pro Tip Equivalent to =A1*B1. Useful inside ARRAYFORMULA. Result Result: 150
â–¶ NE	Numbers	Test if two values are not equal (returns TRUE/FALSE)
Summary Test if two values are not equal (returns TRUE/FALSE) Parameters value1, value2 Pro Tip Equivalent to =A1<>B1. For use inside ARRAYFORMULA. Result Result: TRUE
â–¶ OCT2BIN	Numbers	Convert an octal text to binary
Summary Convert an octal text to binary Parameters octal_text, [places] Pro Tip Max 3 octal digits. Use places to zero-pad. Result Result: "1010"
â–¶ OCT2DEC	Numbers	Convert an octal text to decimal integer
Summary Convert an octal text to decimal integer Parameters octal_text Pro Tip Up to 10 octal digits. Result Result: 8
â–¶ OCT2HEX	Numbers	Convert an octal text to hexadecimal
Summary Convert an octal text to hexadecimal Parameters octal_text, [places] Pro Tip Up to 10 octal digits. Result Result: "F"
â–¶ ODD	Numbers	Round a number up to the nearest odd integer
Summary Round a number up to the nearest odd integer Parameters number Pro Tip =ODD(3.2) ? 5. Negative: =ODD(-2) ? -3. Rounds away from zero. Result Result: 3
â–¶ PERCENTILE	Numbers	Kth percentile of a data set
Summary Kth percentile of a data set Parameters range, k (0–1) Pro Tip 0.9=90th percentile. Interpolates between values. Result Result: 2525
â–¶ PERCENTILE.INC	Numbers	Kth percentile - inclusive of 0 and 1 (modern syntax)
Summary Kth percentile - inclusive of 0 and 1 (modern syntax) Parameters range, k (0–1 inclusive) Pro Tip Same as PERCENTILE. INC means k=0 and k=1 are valid. Result Result: 2525
â–¶ PERCENTILE.EXC	Numbers	Kth percentile - exclusive of 0 and 1
Summary Kth percentile - exclusive of 0 and 1 Parameters range, k (0–1 exclusive) Pro Tip Excludes endpoints. k must be > 0 and < 1. Slightly stricter than .INC. Result Result: 2526
â–¶ PERMUT	Numbers	Number of permutations (ordered arrangements)
Summary Number of permutations (ordered arrangements) Parameters n, k Pro Tip 10×9×8=720. Order matters. Compare to COMBIN where order does not matter. Result Result: 720
â–¶ PI	Numbers	Return the value of p (3.14159…)
Summary Return the value of p (3.14159…) Parameters none Pro Tip Use in trig formulas. =2PI()r gives circumference. Result Result: 3.14159
â–¶ POW	Numbers	Raise a number to a power (Google Sheets alias for POWER)
Summary Raise a number to a power (Google Sheets alias for POWER) Parameters base, exponent Pro Tip Same as POWER and the ^ operator. =POW(4, 0.5) ? 2 (square root). Result Result: 1024
â–¶ POWER	Numbers	Raise a number to a power
Summary Raise a number to a power Parameters base, exponent Pro Tip =POWER(2, 0.5)=v2. Same as the ^ operator. Result Result: 1024
â–¶ PRODUCT	Numbers	Multiply all numbers in a range together
Summary Multiply all numbers in a range together Parameters number1, number2, … or range Pro Tip PRODUCT({1.08, 1.09, 1.07})=1.25971 - same as FVSCHEDULE logic. Result Result: 3.2M
â–¶ QUARTILE	Numbers	Quartile of a data set (Q1, Q2, Q3) - legacy syntax
Summary Quartile of a data set (Q1, Q2, Q3) - legacy syntax Parameters range, quart (0–4) Pro Tip 0=MIN, 1=Q1, 2=MEDIAN, 3=Q3, 4=MAX. Use QUARTILE.INC in modern Excel. Result Result: 2514
â–¶ QUARTILE.INC	Numbers	Quartile - inclusive of 0 and 4 (modern syntax)
Summary Quartile - inclusive of 0 and 4 (modern syntax) Parameters range, quart (0–4) Pro Tip Same as QUARTILE. Preferred in newer Excel versions. Result Result: 2514
â–¶ QUARTILE.EXC	Numbers	Quartile - exclusive (quart must be 1, 2, or 3)
Summary Quartile - exclusive (quart must be 1, 2, or 3) Parameters range, quart (1–3) Pro Tip Excludes endpoints 0 and 4. Slightly different interpolation from .INC. Result Result: 2513
â–¶ QUOTIENT	Numbers	Integer part of a division (discard remainder)
Summary Integer part of a division (discard remainder) Parameters numerator, denominator Pro Tip =INT(10/3) for positive numbers. 10÷3=3 remainder 1. Result Result: 3
â–¶ RADIANS	Numbers	Convert degrees to radians
Summary Convert degrees to radians Parameters angle_in_degrees Pro Tip =RADIANS(90)=p/2. Reverse is DEGREES. Result Result: 3.14159 (p)
â–¶ RAND	Numbers	Random decimal number between 0 and 1
Summary Random decimal number between 0 and 1 Parameters none Pro Tip Recalculates on every sheet change. Multiply to scale range. Result Result: 0.7483
â–¶ RANDBETWEEN	Numbers	Random integer between two bounds (inclusive)
Summary Random integer between two bounds (inclusive) Parameters bottom, top Pro Tip Recalculates on change. Use for random test data. Result Result: 73
â–¶ ROMAN	Numbers	Convert an integer to Roman numeral text
Summary Convert an integer to Roman numeral text Parameters number, [form] Pro Tip Form 0=classic, 4=simplified. Max number: 3999. Result Result: "MMXXIV"
â–¶ ROUND	Numbers	Round to a specified number of decimal places
Summary Round to a specified number of decimal places Parameters number, num_digits Pro Tip Positive=decimal places, 0=integer, negative=tens/hundreds. Result Result: 3.14
â–¶ ROUNDDOWN	Numbers	Round toward zero to N decimal places
Summary Round toward zero to N decimal places Parameters number, num_digits Pro Tip Always truncates toward zero, regardless of value. Result Result: 3
â–¶ ROUNDUP	Numbers	Round away from zero to N decimal places
Summary Round away from zero to N decimal places Parameters number, num_digits Pro Tip Always rounds away from zero, regardless of value. Result Result: 4
â–¶ SEC	Numbers	Secant of an angle in radians (1/cos)
Summary Secant of an angle in radians (1/cos) Parameters angle_in_radians Pro Tip =1/COS(x). =SEC(RADIANS(60)) ? 2. Result Result: 1
â–¶ SECH	Numbers	Hyperbolic secant of a number
Summary Hyperbolic secant of a number Parameters number Pro Tip =1/COSH(x). =SECH(0)=1. Always between 0 and 1. Result Result: 1
â–¶ SIGN	Numbers	Return sign of a number: 1, 0, or -1
Summary Return sign of a number: 1, 0, or -1 Parameters number Pro Tip Use to flip a value: =SIGN(A1)*ABS(A1). Result Result: -1
â–¶ SIN	Numbers	Sine of an angle given in radians
Summary Sine of an angle given in radians Parameters angle_in_radians Pro Tip =SIN(RADIANS(30)) ? 0.5. Convert degrees first. Result Result: 1
â–¶ SINH	Numbers	Hyperbolic sine of a number
Summary Hyperbolic sine of a number Parameters number Pro Tip =SINH(0) ? 0. Inverse is ASINH. Result Result: 1.1752
â–¶ SMALL	Numbers	Return the Nth smallest value in a range
Summary Return the Nth smallest value in a range Parameters range, n Pro Tip =SMALL(B:B, 1)=MIN. Use to find 2nd, 3rd lowest prices. Result Result: 2511
â–¶ SQRT	Numbers	Square root of a number
Summary Square root of a number Parameters number (=0) Pro Tip SQRT of negative returns #NUM!. Use ABS first if needed. Result Result: 12
â–¶ SQRTPI	Numbers	Square root of (number × p)
Summary Square root of (number × p) Parameters number (=0) Pro Tip =SQRT(PI()*n). Shortcut for vp calculations. Result Result: 1.7725
â–¶ STDEV	Numbers	Sample standard deviation
Summary Sample standard deviation Parameters number1, number2, … or range Pro Tip Divides by N-1. Use STDEV.P for whole population. Result Result: 5.2
â–¶ STDEV.S	Numbers	Sample standard deviation (modern syntax)
Summary Sample standard deviation (modern syntax) Parameters number1, number2, … or range Pro Tip Same as STDEV. Modern preferred form. Divides by N-1. Result Result: 5.2
â–¶ STDEV.P	Numbers	Population standard deviation (modern syntax)
Summary Population standard deviation (modern syntax) Parameters number1, number2, … or range Pro Tip Divides by N. Use when you have the entire population. Result Result: 4.9
â–¶ STDEVA	Numbers	Sample std dev including text (=0) and logicals
Summary Sample std dev including text (=0) and logicals Parameters value1, value2, … or range Pro Tip Text=0, TRUE=1, FALSE=0. Use when range has mixed data types. Result Result: 5.5
â–¶ STDEVP	Numbers	Population std dev - legacy syntax
Summary Population std dev - legacy syntax Parameters number1, number2, … or range Pro Tip Same as STDEV.P. Use STDEV.P in newer Excel versions. Result Result: 4.9
â–¶ STDEVPA	Numbers	Population std dev including text and logicals
Summary Population std dev including text and logicals Parameters value1, value2, … or range Pro Tip Population version of STDEVA. Text=0, TRUE=1, FALSE=0. Result Result: 5.1
â–¶ STEYX	Numbers	Standard error of predicted y values in a regression
Summary Standard error of predicted y values in a regression Parameters y_values, x_values Pro Tip Measures scatter of actual y around the regression line. Lower = better fit. Result Result: 2.1
â–¶ SUBTOTAL	Numbers	Aggregate that ignores hidden/filtered rows
Summary Aggregate that ignores hidden/filtered rows Parameters function_num, range Pro Tip Func 9=SUM, 1=AVERAGE, 2=COUNT. Use for filtered lists. Result Result: 25200
â–¶ SUM	Numbers	Sum all numbers in a range
Summary Sum all numbers in a range Parameters number1, number2, … or range Pro Tip Most used formula. =SUM(B:B) sums entire column. Ignores text. Result Result: 25205
â–¶ SUMPRODUCT	Numbers	Sum of products of corresponding range elements
Summary Sum of products of corresponding range elements Parameters array1, array2, … Pro Tip Use as a powerful SUMIF: =SUMPRODUCT((A1:A10="TCS")*B1:B10). Result Result: 12400
â–¶ SUMSQ	Numbers	Sum of squares of all values
Summary Sum of squares of all values Parameters number1, number2, … or range Pro Tip 3²+4²=9+16=25. Used in variance calculations. Result Result: 25
â–¶ SUMX2MY2	Numbers	Sum of (x²-y²) for corresponding elements
Summary Sum of (x²-y²) for corresponding elements Parameters array_x, array_y Pro Tip =SUMPRODUCT(B1:B5^2 - C1:C5^2). Useful in statistical analysis. Result Result: 120
â–¶ SUMX2PY2	Numbers	Sum of (x²+y²) for corresponding elements
Summary Sum of (x²+y²) for corresponding elements Parameters array_x, array_y Pro Tip =SUMPRODUCT(B1:B5^2 + C1:C5^2). Used in distance calculations. Result Result: 540
â–¶ SUMXMY2	Numbers	Sum of (x-y)² for corresponding elements
Summary Sum of (x-y)² for corresponding elements Parameters array_x, array_y Pro Tip =SUMPRODUCT((B1:B5-C1:C5)^2). Building block for sample variance. Result Result: 30
â–¶ TAN	Numbers	Tangent of an angle in radians
Summary Tangent of an angle in radians Parameters angle_in_radians Pro Tip =TAN(RADIANS(45)) ? 1. Undefined at 90° (=PI()/2). Result Result: 1
â–¶ TANH	Numbers	Hyperbolic tangent of a number
Summary Hyperbolic tangent of a number Parameters number Pro Tip Output always between -1 and 1. Inverse is ATANH. Result Result: 0.7616
â–¶ TRUNC	Numbers	Truncate to integer by removing decimal part
Summary Truncate to integer by removing decimal part Parameters number, [num_digits] Pro Tip Unlike INT, TRUNC always truncates toward zero: =TRUNC(-4.9) ? -4. Result Result: 4
â–¶ VAR	Numbers	Sample variance - legacy syntax
Summary Sample variance - legacy syntax Parameters number1, number2, … or range Pro Tip Standard deviation squared. Divides by N-1. Use VAR.S in modern Excel. Result Result: 27.04
â–¶ VAR.S	Numbers	Sample variance - modern syntax
Summary Sample variance - modern syntax Parameters number1, number2, … or range Pro Tip Same as VAR. Preferred modern form. Divides by N-1. Result Result: 27.04
â–¶ VAR.P	Numbers	Population variance - modern syntax
Summary Population variance - modern syntax Parameters number1, number2, … or range Pro Tip Divides by N. Use when you have the full population. Result Result: 24.01
â–¶ VARA	Numbers	Sample variance including text (=0) and logicals
Summary Sample variance including text (=0) and logicals Parameters value1, value2, … or range Pro Tip Text=0, TRUE=1, FALSE=0. Use when range may have non-numeric entries. Result Result: 28.1
â–¶ VARP	Numbers	Population variance - legacy syntax
Summary Population variance - legacy syntax Parameters number1, number2, … or range Pro Tip Same as VAR.P. Use VAR.P in newer Excel versions. Result Result: 24.01
â–¶ VARPA	Numbers	Population variance including text and logicals
Summary Population variance including text and logicals Parameters value1, value2, … or range Pro Tip Population version of VARA. Text=0, TRUE=1, FALSE=0. Result Result: 25.2
â–¶ WEIBULL	Numbers	Weibull distribution - legacy syntax
Summary Weibull distribution - legacy syntax Parameters x, alpha, beta, cumulative Pro Tip Same as WEIBULL.DIST. Use WEIBULL.DIST in modern Excel. Result Result: 0.9877
â–¶ WEIBULL.DIST	Numbers	Weibull distribution - modern syntax
Summary Weibull distribution - modern syntax Parameters x, alpha, beta, cumulative Pro Tip Used in reliability and survival analysis. TRUE=cumulative, FALSE=PDF. Result Result: 0.9877
â–¶ CONCATENATE	Text	Join multiple text strings into one
Summary Join multiple text strings into one Parameters text1, text2, … Pro Tip Shortcut: =A1&" - "&B1. Use TEXTJOIN for ranges with delimiter. Result Result: "TCS - NSE"
â–¶ LEFT	Text	Extract N characters from the start of text
Summary Extract N characters from the start of text Parameters text, num_characters Pro Tip If A1="RELIANCE" ? "REL". Pair with FIND for dynamic extraction. Result Result: "REL"
â–¶ MID	Text	Extract N characters from the middle of text
Summary Extract N characters from the middle of text Parameters text, start_position, num_characters Pro Tip Start position is 1-based. If A1="NIFTY24500CE" ? "24500". Result Result: "24500"
â–¶ RIGHT	Text	Extract N characters from the end of text
Summary Extract N characters from the end of text Parameters text, num_characters Pro Tip If A1="NIFTY24500CE" ? "CE". Useful for CE/PE option type. Result Result: "CE"
â–¶ LOWER	Text	Convert text to all lowercase
Summary Convert text to all lowercase Parameters text Pro Tip Use for case-insensitive comparisons: =LOWER(A1)=LOWER(B1). Result Result: "tcs.nse"
â–¶ UPPER	Text	Convert text to ALL CAPS
Summary Convert text to ALL CAPS Parameters text Pro Tip Always normalise symbols to uppercase before using live formulas. Result Result: "RELIANCE"
â–¶ PROPER	Text	Capitalise first letter of every word
Summary Capitalise first letter of every word Parameters text Pro Tip Use for company or client names in reports. Result Result: "Tata Motors Ltd"
â–¶ TRIM	Text	Remove leading, trailing, and extra spaces
Summary Remove leading, trailing, and extra spaces Parameters text Pro Tip Essential after CSV imports. CHAR(160) non-breaking space needs SUBSTITUTE. Result Result: "TCS"
â–¶ LEN	Text	Count total characters including spaces
Summary Count total characters including spaces Parameters text Pro Tip If A1="NIFTY24500CE" ? 12. Use to detect hidden spaces. Result Result: 12
â–¶ CHAR	Text	Return character for a given ASCII code
Summary Return character for a given ASCII code Parameters number (1–255) Pro Tip CHAR(10)=newline, CHAR(32)=space, CHAR(160)=non-breaking space. Result Result: Line break
â–¶ CLEAN	Text	Remove non-printable characters (ASCII 0–31)
Summary Remove non-printable characters (ASCII 0–31) Parameters text Pro Tip Combine with TRIM for full clean: =TRIM(CLEAN(A1)). Result Result: "TCS"
â–¶ CODE	Text	ASCII code of the first character in text
Summary ASCII code of the first character in text Parameters text Pro Tip If A1="TCS" ? 84 (code for "T"). Reverse of CHAR. Result Result: 84
â–¶ EXACT	Text	Case-sensitive comparison of two strings
Summary Case-sensitive comparison of two strings Parameters text1, text2 Pro Tip Unlike =, EXACT treats "TCS" and "tcs" as different. Result Result: FALSE
â–¶ FIND	Text	Position of text in a string (case-sensitive)
Summary Position of text in a string (case-sensitive) Parameters find_text, within_text, [start] Pro Tip Returns error if not found - wrap in IFERROR. Use SEARCH for case-insensitive. Result Result: 4
â–¶ FIXED	Text	Round and format a number as text with commas
Summary Round and format a number as text with commas Parameters number, [decimal_places], [no_commas] Pro Tip Result is TEXT. Use TRUE for no_commas to suppress separators. Result Result: "1,234,567.89"
â–¶ NUMBERVALUE	Text	Convert number text with custom separators to number
Summary Convert number text with custom separators to number Parameters text, [decimal_sep], [group_sep] Pro Tip Handles European format (comma=decimal). Unlike VALUE(), you specify separators. Result Result: 1234.56
â–¶ REGEXEXTRACT	Text	Extract first substring matching a regex pattern
Summary Extract first substring matching a regex pattern Parameters text, regex_pattern Pro Tip If A1="NIFTY24500CE" ? "24500". Wrap in IFERROR for no-match. Result Result: "24500"
â–¶ REGEXMATCH	Text	Test if text matches a regex pattern
Summary Test if text matches a regex pattern Parameters text, regex_pattern Pro Tip "CE$" means ends with CE. "^" for starts with. Use inside IF. Result Result: TRUE
â–¶ REGEXREPLACE	Text	Replace all regex matches with new text
Summary Replace all regex matches with new text Parameters text, pattern, replacement Pro Tip Use "" to delete matches. More powerful than SUBSTITUTE for patterns. Result Result: "NIFTYCE"
â–¶ REPT	Text	Repeat text N times
Summary Repeat text N times Parameters text, number_of_times Pro Tip Use =REPT("D", A1/100) for simple in-cell bar charts. Result Result: "DDDDDDDDDD"
â–¶ SEARCH	Text	Position of text in string (case-insensitive, wildcards ok)
Summary Position of text in string (case-insensitive, wildcards ok) Parameters find_text, within_text, [start] Pro Tip Case-insensitive. Supports * and ? wildcards. Wrap in IFERROR. Result Result: 5
â–¶ SUBSTITUTE	Text	Replace all occurrences of text with new text
Summary Replace all occurrences of text with new text Parameters text, old_text, new_text, [occurrence] Pro Tip Case-sensitive. 4th arg replaces only Nth occurrence. Result Result: "TCS"
â–¶ T	Text	Return text if value is text, or empty string ("")
Summary Return text if value is text, or empty string ("") Parameters value_or_cell Pro Tip Numbers/blanks return "". Silently filters non-text values. Result Result: "TCS"
â–¶ JOIN	Text	Join a range into one string with a delimiter
Summary Join a range into one string with a delimiter Parameters delimiter, range Pro Tip Google Sheets only. Excel equivalent: =TEXTJOIN(", ", TRUE, A1:A5). Result Result: "TCS, INFY, RELIANCE…"
â–¶ REPLACE	Text	Replace characters at a known position with new text
Summary Replace characters at a known position with new text Parameters text, start, num_chars, new_text Pro Tip Use when you know the position. Use SUBSTITUTE when you know the content. Result Result: "TCS.BSE"
â–¶ ARRAYTOTEXT	Text	Convert an array or range to a text string
Summary Convert an array or range to a text string Parameters array, [format] Pro Tip Format 0=concise, 1=strict with braces. Excel 365 only. Result Result: "TCS, INFY, RELIANCE"
â–¶ SUMIF	Numbers	Sums cells that meet a single criteria
Summary Sums cells that meet a single criteria Parameters range, criteria, [sum_range] Pro Tip Wildcards work too: =SUMIF(A2:A7, "App*", C2:C7) sums amounts for items starting with "App" (Result: 120) Result Result: 170 (amounts in C2:C7 greater than 50)
â–¶ SUMIFS	Numbers	Sums cells that meet multiple criteria across multiple ranges
Summary Sums cells that meet multiple criteria across multiple ranges Parameters sum_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...] Pro Tip Unlike SUMIF, sum_range always comes first: =SUMIFS(C2:C7, A2:A7, "Apple", B2:B7, "Fruit") (Result: 120) Result Result: 220 (Category = "Fruit" AND Amount > 40)
â–¶ COUNTIF	Numbers	Counts cells that satisfy a single criteria
Summary Counts cells that satisfy a single criteria Parameters range, criteria Pro Tip Wildcards work too: =COUNTIF(A2:A7, "B*") counts items starting with "B" (Result: 2: Banana, Broccoli) Result Result: 4 (rows where Category = "Fruit")
â–¶ COUNTIFS	Numbers	Counts cells that satisfy multiple conditions simultaneously
Summary Counts cells that satisfy multiple conditions simultaneously Parameters criteria_range1, criteria1, [criteria_range2, criteria2, ...] Pro Tip Add more range/criteria pairs to narrow further: =COUNTIFS(B2:B7, "Veg", C2:C7, ">=40") (Result: 2) Result Result: 2 (Category = "Fruit" AND Amount < 60: Apple 50, Banana 30)
â–¶ AVERAGEIF	Numbers	Calculates the average (arithmetic mean) of cells that meet a given condition
Summary Calculates the average (arithmetic mean) of cells that meet a given condition Parameters range, criteria, [average_range] Pro Tip Use a separate average_range to average one column based on another: =AVERAGEIF(B2:B7, "Fruit", C2:C7) (Result: 62.5) Result Result: 76.67 (average of amounts greater than 50)
â–¶ AVERAGEIFS	Numbers	Calculates the average of cells that meet multiple criteria
Summary Calculates the average of cells that meet multiple criteria Parameters average_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...] Pro Tip Like SUMIFS, average_range always comes first, followed by criteria pairs Result Result: 73.33 (average of Fruits with Amount >= 50)
â–¶ MAXIFS	Numbers	Returns the maximum value among cells specified by a given set of conditions
Summary Returns the maximum value among cells specified by a given set of conditions Parameters max_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...] Pro Tip Change the criteria to switch categories: =MAXIFS(C2:C7, B2:B7, "Veg") (Result: 60) Result Result: 100 (highest price among all Fruits)
â–¶ MINIFS	Numbers	Returns the minimum value among cells specified by a given set of conditions
Summary Returns the minimum value among cells specified by a given set of conditions Parameters min_range, criteria_range1, criteria1, [criteria_range2, criteria2, ...] Pro Tip Change the criteria to switch categories: =MINIFS(C2:C7, B2:B7, "Veg") (Result: 40) Result Result: 30 (lowest price among all Fruits)

Sheets by Truedata

Empowering traders and analysts with high-performance real-time market data directly within their spreadsheets.

Formula Reference

Built-In Formulas

Resources

Support