Array Formulas

Formulas Name Formula Syntax
ARRAY_CONSTRAIN =ARRAY_CONSTRAI(input_range, num_rows, num_cols)
FREQUENCY =FREQUENCY(data, classes)
GROWTH =GROWTH(known_data_y, [known_data_x], [new_data_x], [b])
LINEST =LINEST(known_data_y, [known_data_x], [calculate_b], [verbose])
LOGEST =LOGEST(known_data_y, [known_data_x], [b], [verbose])
MDETERM =MDETERM(square_matrix)
MINVERSE =MINVERSE(square_matrix)
MMULT =MMULT(matrix1, matrix2)
SUMPRODUCT =SUMPRODUCT(array1, [array2, …])
TRANSPOSE =TRANSPOSE(array_or_range)
TREND =TREND(known_data_y, [known_data_x], [new_data_x], [b])

Database Formulas

Formulas Name Formula Syntax
DAVERAGE =DAVERAGE(database, field, criteria)
DCOUNT =DCOUNT(database, field, criteria)
DCOUNTA =DCOUNTA(database, field, criteria)
DGET =DGET(database, field, criteria)
DMAX =DMAX(database, field, criteria)
DMIN =DMIN(database, field, criteria)
DPRODUCT =DPRODUCT(database, field, criteria)
DSTDEV =DSTDEV(database, field, criteria)
DSUM =DSUM(database, field, criteria)
DVAR =DVAR(database, field, criteria)

Date Formulas

Formulas Name Formula Syntax
DATE =DATE(year, month, day)
DATEDIF =DATEDIF(start_date, end_date, unit)
DATEVALUE =DATEVALUE(date_string)
DAY =DAY(date)
DAYS =DAYS(end_date, start_date)
DAYS360 =DAYS360(start_date, end_date, [method])
EDATE =EDATE(start_date, months)
EOMONTH =EOMONTH(start_date, months)
HOUR =HOUR(time)
MINUTE =MINUTE(time)
MONTH =MONTH(date)
NETWORKDAYS =NETWORKDAYS(start_date, end_date, [holidays])
NETWORKDAYS.INTL =NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
NOW =NOW()
SECOND =SECOND(time)
TIME =TIME(hour, minute, second)
TIMEVALUE =TIMEVALUE(time_string)
TODAY =TODAY()
WEEKDAY =WEEKDAY(date, [type])
WEEKNUM =WEEKNUM(date, [type])
WORKDAY =WORKDAY(start_date, num_days, [holidays])
WORKDAY.INTL =WORKDAY.INTL(start_date, num_days, [weekend], [holidays])
YEAR =YEAR(date)

Engineering Formulas

Formulas Name Formula Syntax
BIN2DEC =BIN2DEC(signed_binary_number)
BIN2HEX =BIN2HEX(signed_binary_number, [significant_digits])
BIN2OCT =BIN2OCT(signed_binary_number, [significant_digits])
BITAND =BITAND(value1, value2)
BITLSHIFT =BITLSHIFT(value, shift_amount)
BITOR =BITOR(value1, value2)
BITRSHIFT =BITRSHIFT(value, shift_amount)
BITXOR =BITXOR(value1, value2)
COMPLEX =COMPLEX(real_part, imaginary_part, [suffix])
DEC2BIN =DEC2BIN(decimal_number, [significant_digits])
DEC2HEX =DEC2HEX(decimal_number, [significant_digits])
DEC2OCT =DEC2OCT(decimal_number, [significant_digits])
DELTA =DELTA(number1, [number2])
ERF =ERF(lower_bound, [upper_bound])
ERF.PRECISE =ERF.PRECISE(lower_bound, [upper_bound])
GESTEP =GESTEP(value, [step])
HEX2BIN =HEX2BIN(signed_hexadecimal_number, [significant_digits])
HEX2DEC =HEX2DEC(signed_hexadecimal_number)
HEX2OCT =HEX2OCT(signed_hexadecimal_number, [significant_digits])
IMABS =IMABS(number)
IMAGINARY =IMAGINARY(complex_number)
IMARGUMENT =IMARGUMENT(number)
IMCOS =IMCOS(number)

Filter Formulas

Formulas Name Formula Syntax
FILTER =FILTER(range, condition1, [condition2])
SORT =SORT(range, sort_column, is_ascending, [sort_column2], [is_ascending2])
SORTN =SORTN(range, [n], [display_ties_mode], {sort_column1, is_ascending1], …)
UNIQUE =UNIQUE(range)

Financial Formulas

Formulas Name Formula Syntax
DB =DB(cost, salvage, life, period, [month])
DDB =DDB(cost, salvage, life, period, [factor])
DISC =DISC(settlement, maturity, price, redemption, [day_count_convention])
DOLLARDE =DOLLARDE(fractional_price, unit)
DOLLARFR =DOLLARFR(decimal_price, unit)
DURATION =DURATION(settlement, maturity, rate, yield, frequency, [day_count_convention])
EFFECT =EFFECT(nominal_rate, periods_per_year)
FV =FV(rate, number_of_periods, payment_amount, [present_value], [end_or_beginning])
FVSCHEDULE =FVSCHEDULE(principal, rate_schedule)
INTRATE =INTRATE(buy_date, sell_date, buy_price, sell_price, [day_count_convention])
IPMT =IPMT(rate, period, number_of_periods, present_value, [future_value], [end_or_beginning])
IRR =IRR(cashflow_amounts, [rate_guess])
ISPMT =ISPMT(rate, period, number_of_periods, present_value)
MIRR =MIRR(cashflow_amounts, financing_rate, reinvestment_return_rate)
NOMINAL =NOMINAL(effective_rate, periods_per_year)
NPER =NPER(rate, payment_amount, present_value, [future_value], [end_or_beginning])
NPV =NPV(discount, cashflow1, [cashflow2, …])
PMT =PMT(rate, number_of_periods, present_value, [future_value], [end_or_beginning])
PRICE =PRICE(settlement, maturity, rate, yield, redemption, frequency, [day_count_convention])
PV =PV(rate, number_of_periods, payment_amount, [future_value], [end_or_beginning])
RATE =RATE(number_of_periods, payment_per_period,present_value, [future_value], [end_or_beginning], [rate_guess])
XIRR =XIRR(cashflow_amounts, cashflow_dates, [rate_guess])
YIELD =YIELD(settlement, maturity, rate, price, redemption, frequency, [day_count_convention])

Google Formulas

Formulas Name Formula Syntax
ARRAYFORMULA =ARRAYFORMULA(array_fromula)
DETECTLANGUAGE =DETECTLANGUAGE(text_or_range)
GOOGLEFINANCE =GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date | num_days], [interval])
GOOGLETRANSLATE =GOOGLETRANSLATE(text, [source_language], [target_language])
IMAGE =IMAGE(url, [mode], [height], [width])
QUERY =QUERY(data, query, [headers])
SPARKLINE =SPARKLINE(data, [options])

Info Formulas

Formulas Name Formula Syntax
ERROR.TYPE =ERROR.TYPE(reference)
ISBLANK =ISBLANK(value)
ISDATE =ISDATE(value)
ISMAIL =ISMAIL(value)
ISERR =ISERR(value)
ISERROR =ISERROR(value)
ISFORMULA =ISFORMULA(cell)
ISLOGICAL =ISLOGICAL(value)
ISNA =ISNA(value)
ISNONTEXT =ISNOTEXT(value)
ISNUMBER =ISNUMBER(value)
ISREF =ISREF(value)
ISTEXT =ISTEXT(value)
N =N(value)
NA =NA()
TYPE =TYPE(value)
CELL =CELL(info_type, reference)

Lookup Formulas

Formulas Name Formula Syntax
AND =AND(logical_expression1, [logical_expression2, …])
FALSE =FALSE()
IF =IF(logical_expression, value_if_true, value_if_false)
IFERROR =IFERROR(value, [value_if_ error])
IFNA =IFNA(value, value_if_na)
IFS =IFS(conditiona1, value1, [condition2, value2], …)
NOT =NOT(logical_expression)
OR =OR(logical_expression1, [logical_expression2, …])
SWITCH =SWITCH(expression, case1, vaue1, [default or case2, value2], …)
TRUE =TRUE()
ADDRESS =ADDRESS(row, column, [absolute_relative_mode], [use_a1_notation], [sheet])
CHOOSE =CHOOSE(index, choice1, [choice2, …])
COLUMN =COLUMN([cell_reference])
COLUMNS =COLUMNS(range)
FORMULATEXT =FORMULATEXT(cell)
GETPIVOTDATA =GETPIVOTDATA(value_name, any_pivot_table_cell, [original_column, …], [pivot_item, …])
HLOOKUP =HLOOKUP(search_key, range, index, [is_sorted])
INDEX =INDEX(reference, [row], [column])
INDIRECT =INDIRECT(cell_reference_as_string, [is_A1_notation])
LOOKUP =LOOKUP(searh_key, search_range | search_result_array, [result_range])
MATCH =MATCH(search_key, range, [search_type])
OFFSET =OFFSSET(cell_reference, offset_rows, offset_columns, [height], [width])
ROW =ROW([cell_reference])
ROWS =ROWS(range)
VLOOKUP =VLOOKUP(search_key, range, index, [is_sorted])

Math Formulas

Formulas Name Formula Syntax
ABS =ABS(value)
ACOS =ACOS(value)
ACOSH =ACOSH(value)
ACOT =ACOT(value)
BASE =BASE(value, base, [min_length])
CEILING =CEILING(value, [factor])
COMBIN =COMBIN(n, k)
COMBINA =COMBINA(n, k)
COS =COS(angle)
COUNTBLANK =COUNTBLANK(range)
COUNTIF =COUNTIF(range, criterion)
COUNTIFS =COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, …])
COUNTUNIQUE =COUNTUNIQUE(value1, [value2, …])
DECIMAL =DECIMAL(value, base)
DEGREES =DEGREES(angle)
IMSQRT =IMSQRT(complex_number)
INT =INT(value)
SUMIF =SUMIF(range, criterion, [sum_range])
SUMIFS =SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, …])
MROUND =MROUND(value, factor)
POWER =POWER(base, exponent)
PRODUCT =PRODUCT(factor1, [factor2, …])
RAND =RAND()
RANDBETWEEN =RANDBETWEEN(low, high)
ROUND =ROUND(value, [places])
ROUNDDOWN =ROUNDDOWN(value, [places])
ROUNDUP =ROUNDUP(value, [places])
SUM =SUM(value1, [values2, …])

Operator Formulas

Formulas Name Formula Syntax
ADD =ADD(value1, value2)
CONCAT =CONCAT(value1, value2)
DIVIDE =DIVIDE(dividend, divisor)
EQ =EQ(value1, value2)

Statistical Formulas

Formulas Name Formula Syntax
AVERAGE =AVERAGE(value1, [value2, …])
AVERAGEIF =AVERAGEIF(criteria_range, criterion, [average_range])
AVERAGEIFS =AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, …])
CORREL =CORREL(data_y, data_x)
COUNT =COUNT(value1, [value2, …])
COUNTA =COUNTA(value1, [value2, …])
MAX =MAX(value1, [value2, …])
MAXA =MAXA(value1, value2)
MAXIFS =MAXIFS(range, criteria_range1, criterion1, [criteria_range2, criterion2], …)
MEDIAN =MEDIAN(value1, [value2, …])
MIN =MIN(value1, [value2, …])
MINIFS =MINIFS(range, criteria_range1, criterion1, [criteria_range2, criterion2], …)
MODE =MODE(value1, [value2, …])
PERCENTILE =PERCENTILE(data, percentile)

Text - Spreadsheet formulas list

Formulas Name Formula Syntax
CHAR =CHAR(table_number)
CLEAN =CLEAN(text)
CONCATENATE =CONCATENATE(string1, [string2, …])
EXACT =EXACT(string1, string2)
FIND =FIND(search_for, text_to_search, [starting_at])
JOIN =JOIN(delimiter, value_or_array1, [value_or_array2, …])
LEFT =LEFT(string, [number_of_characters])
LEN =LEN(text)
LOWER =LOWER(text)
MID =MID(string, starting_ate, extract_length)
PROPER =PROPER(text_to_capitalize)
REPLACE =REPLACE(text, position, length, new_text)
REPT =REPT(text_to_repeat, number_of_repetitions)
RIGHT =RIGHT(string, [number_of_characters])
SEARCH =SEARCH(search_for, text_to_search, [starting_at])
SUBSTITUTE =SUBSTITUTE(text_tp_search, search_for, replace_with, [occurrence_number])
TEXT =TEXT(number, format)
TRIM =TRIM(text)
UPPER =UPPER(text)
VALUE =VALUE(text)

Hope you have liked our blog on Google Spreadsheet Formulas List. Suggestions are always welcomed for the blog you looking forward to hearing from us. Like our My e-Learning Hub Facebook Page and Subscribe to our My e-Learning Hub YouTube Channel. Connect with us and Take your Office Productivity to Next Level.

Leave a comment

Main Office:

Mumbai, India

Contact Us:

[email protected]

MyeLearningHUB © 2019. All rights reserved.