Logical Functions
| Function | Description |
|---|---|
| IF | Return one value if condition is TRUE, another if FALSE (e.g., pass/fail check). |
| IFERROR | Return fallback value if formula results in error (e.g., "N/A" instead of #DIV/0!). |
| AND | Check if multiple conditions are all TRUE (e.g., bonus if sales >1000 and profit >10%). |
| OR | Returns TRUE if at least one condition is TRUE (e.g., weekend = Saturday or Sunday). |
| NOT | Reverse logic (e.g., if not complete, show "Pending"). |
| IFS | Test multiple conditions without nesting many IFs (like grading systems). |
| IFNA | Catch only #N/A errors and return custom message. |
| TRUE | Return logical TRUE — often used in conditions. |
| FALSE | Return logical FALSE — often for comparisons. |
| SWITCH | Match one value against multiple — like a simplified multiple IF statement. |
| XOR | Returns TRUE if only one condition is TRUE (advanced logic testing). |
| LET | Define variables inside a formula to optimize performance. |
| LAMBDA | Create custom functions directly in Excel (no VBA needed). |
Lookup Functions
| Function | Description |
|---|---|
| VLOOKUP | Look up value in the leftmost column and return data from another column. |
| XLOOKUP | Modern replacement for VLOOKUP — more flexible, works left/right. |
| INDEX | Return value from specific row & column in a range — powerful for dynamic data. |
| MATCH | Get position of a value in a range — useful with INDEX. |
| HLOOKUP | Horizontal lookup — search in top row and return value from a row below. |
| LOOKUP | Find closest match and return corresponding value — older but flexible. |
| OFFSET | Reference a range offset from a starting cell — for dynamic ranges. |
| ROW | Return row number of a cell — often used in formulas. |
| COLUMN | Return column number of a cell. |
| ROWS | Count rows in a range — e.g., for pagination or totals. |
| COLUMNS | Count columns in a range. |
| CHOOSE | Pick a value from a list by position — handy for scenarios/alternatives. |
| INDIRECT | Convert text to a cell reference — enables dynamic referencing. |
| FORMULATEXT | Show the formula in a cell — for audits or documentation. |
| ADDRESS | Return cell reference as text — e.g., A1 for row 1, column 1. |
| GETPIVOTDATA | Extract values from a PivotTable — for reporting dashboards. |
| SORTBY | Sort a range based on values in another range — dynamic sorting. |
| XMATCH | Modern MATCH with exact and wildcard matching — faster and easier. |
| AREAS | Count areas (ranges) in a reference — used in complex named ranges. |
| FIELDVALUE | Extract field value from a data type — for linked data types. |
| RTD | Real-time data from a COM automation server — used in finance/stock updates. |
Date & Time Functions
Date & Time Functions (NOW, DATE, DATEDIF, NETWORKDAYS, etc.)
| Function | Description |
|---|---|
| NOW | Return current date & time — use in live timestamping. |
| DATE | Build a date from year, month, and day — helpful when combining fields. |
| DATEDIF | Calculate difference between dates — e.g., age in years, months. |
| NETWORKDAYS | Count working days between two dates (excludes weekends/holidays). |
| WORKDAY | Calculate future/past working date by skipping weekends/holidays. |
| WEEKDAY | Return day of the week (e.g., Mon=2) — used in scheduling logic. |
| DAY | Extract day from a date — useful for billing, due tracking. |
| MONTH | Extract month — used in monthly reports or filters. |
| YEAR | Get year from a date — often used in summary dashboards. |
| WEEKNUM | Get the week number — for production or timeline tracking. |
| TIME | Combine hour, minute, second into time — for custom timestamps. |
| HOUR | Extract hour from a time — useful for shift planning or clock-ins. |
| MINUTE | Get minutes part from time value. |
| SECOND | Return seconds part — e.g., for detailed logging. |
| DATEVALUE | Convert text to a date — for imports or text-based data. |
| TIMEVALUE | Convert text time to time value — used in cleaning data. |
| DAYS | Return total days between two dates — for tracking duration. |
| EDATE | Get date after a given number of months — used in subscriptions/billing. |
| EOMONTH | Get last day of the month — for payroll or invoicing. |
| YEARFRAC | Year difference as a fraction — often used in finance/interest calc. |
| NETWORKDAYS.INTL | More flexible NETWORKDAYS — choose weekends/holidays. |
| WORKDAY.INTL | More flexible WORKDAY — specify custom weekends/holidays. |
| DAYS360 | Days between dates using 360-day year — used in accounting/finance. |
| ISOWEEKNUM | Get ISO week number (starting from Monday). |
| EPOCHTODATE | Convert UNIX timestamp to readable date. |
Filter Functions
Filter & Sort Functions (FILTER, SORT, UNIQUE, SORTN)
| Function | Description |
|---|---|
| FILTER | Extract rows that meet specific conditions — e.g., filter only "Completed" tasks. |
| SORT | Sort data by column (ascending/descending) — e.g., sales from high to low. |
| UNIQUE | Remove duplicates — e.g., list unique customer names from transactions. |
| SORTN | Return top n items based on a column — e.g., top 3 scorers or top-selling items. |
Text Functions
Text Functions (String Manipulation & Formatting)
| Function | Description |
|---|---|
| TEXT | Format numbers or dates into a custom text format (e.g., "01-Jan-2025"). |
| TEXTJOIN | Combine multiple values from cells with a delimiter (e.g., comma). |
| TRIM | Remove extra spaces from messy imported text data. |
| UPPER | Convert names or addresses to uppercase for standardization. |
| LOWER | Change customer emails to lowercase for consistency. |
| PROPER | Capitalize each word in names (e.g., "john doe" to "John Doe"). |
| SUBSTITUTE | Replace part of a text string (e.g., replace old product codes). |
| REPLACE | Modify text within a string at a specific position. |
| LEFT | Extract area code from a phone number. |
| RIGHT | Get last four digits from a number or ID. |
| MID | Extract a substring from the middle (e.g., middle name). |
| FIND | Locate the position of a substring (e.g., find @ in email). |
| SEARCH | Similar to FIND but case-insensitive (e.g., search a word in sentence). |
| LEN | Count total number of characters in a cell. |
| VALUE | Convert text-formatted numbers into numeric values. |
| CHAR | Return character from ASCII code (e.g., CHAR(10) = line break). |
| CODE | Get ASCII code of the first character in a string. |
| CONCATENATE | Combine cells like A1+B1+C1 into one. |
| JOIN | Join values with delimiter (alternate to TEXTJOIN). |
| SPLIT | Split full name into first and last name. |
| EXACT | Check if two strings are exactly the same. |
| T | Return text part of a value, or blank if it’s numeric. |
| DOLLAR | Convert number into currency format (e.g., $120.00). |
| FIXED | Format number with fixed number of decimals. |
| REPT | Repeat a character or symbol multiple times (e.g., for star ratings). |
| ARABIC | Convert Roman numerals to Arabic numbers. |
| ROMAN | Convert numbers to Roman numerals. |
| ASC | Convert full-width characters to half-width (Japanese localization). |
| CLEAN | Remove non-printable characters (e.g., from imported data). |
| REGEXEXTRACT | Extract part of text using pattern (e.g., extract domain from email). |
| REGEXMATCH | Check if text matches a pattern (e.g., email validation). |
| REGEXREPLACE | Replace text using pattern (e.g., censor bad words). |
| LEFTB | Return first N bytes of text (used in double-byte languages). |
| RIGHTB | Return last N bytes of text. |
| MIDB | Return part of text from byte-based positions. |
| LENB | Count number of bytes instead of characters (for Asian languages). |
| UNICHAR | Returns character by Unicode number. |
| UNICODE | Get Unicode value of first character. |
Math & Trig Functions
Math & Trig Functions (Calculations & Numeric Operations)
| Function | Description |
|---|---|
| SUM | Quickly total up sales, expenses, or any numeric column. |
| SUMIF | Add values only if they meet a condition (e.g., sales > 1000). |
| SUMIFS | Sum with multiple conditions, like region and date range. |
| COUNTIF | Count how many orders exceeded $500. |
| COUNTIFS | Count how many rows match multiple conditions. |
| COUNTBLANK | Identify empty cells in a data range to ensure data completeness. |
| COUNTUNIQUE | Count unique values in a range like unique customer names. |
| ROUND | Round values to the nearest whole or decimal place for cleaner reporting. |
| ROUNDDOWN | Always round down final prices or tax values to avoid overcharges. |
| ROUNDUP | Always round up to ensure customer charges meet thresholds. |
| INT | Get whole number from a decimal (e.g., strip cents from currency). |
| TRUNC | Remove fractional digits without rounding for precise cutoff. |
| MOD | Check for even/odd numbers or repeat patterns (modulo for periodic tasks). |
| ODD | Round numbers up to the nearest odd number for schedule groupings. |
| EVEN | Round numbers up to the nearest even number for slot divisions. |
| ABS | Get positive distance between numbers (ignore sign, e.g., balance diff). |
| POWER | Raise numbers to powers (e.g., area calculation or exponential growth). |
| PRODUCT | Multiply values together, like price × quantity in invoices. |
| QUOTIENT | Divide and get only the whole number part, ideal for packaging counts. |
| SQRT | Find square root, e.g., for geometry, stats, or standard deviation prep. |
| SQRTPI | Multiply square root of PI – used in specialized geometry scenarios. |
| LOG | Logarithmic transformation in scientific and data modeling. |
| LN | Natural logarithm, often used in compound interest or exponential decay. |
| LOG10 | Base-10 logarithm for scientific scale conversions. |
| PI | Used in geometry formulas (e.g., circle circumference). |
| RADIANS | Convert degrees to radians for trigonometric calculations. |
| DEGREES | Convert radians back to degrees for reporting. |
| SIN | Calculate sine of an angle – useful in waves, geometry, physics. |
| COS | Calculate cosine – used in cycles, trigonometry, and animation. |
| TAN | Get tangent of angle – needed in slope and triangle height calculations. |
| ATAN2 | Get angle between two points in 2D space (navigation, plotting). |
| ACOS | Calculate arc cosine – find angle when cosine is known. |
| ACOSH | Hyperbolic arc cosine – advanced math modeling. |
| ACOT | Arc cotangent – used in specific trigonometric modeling. |
| ACOTH | Inverse hyperbolic cotangent – niche advanced use. |
| ASIN | Arc sine – retrieve angle when sine is known. |
| ASINH | Hyperbolic arc sine – useful in advanced calculations. |
| ATAN | Get inverse tangent – useful in geometry and plotting. |
| ATANH | Inverse hyperbolic tangent – advanced math/statistics. |
| COT | Cotangent of an angle – often used in trigonometric models. |
| COTH | Hyperbolic cotangent – used in physics or engineering. |
| CSC | Cosecant of angle – less common trigonometric function. |
| CSCH | Hyperbolic cosecant – niche scientific use. |
| SEC | Secant – reciprocal of cosine; used in trigonometry. |
| SECH | Hyperbolic secant – advanced math and engineering. |
| MROUND | Round to nearest multiple (e.g., round price to 5 or 10). |
| RAND | Generate random number between 0–1 for simulations or random selection. |
| RANDBETWEEN | Random integer between two numbers (e.g., simulate lottery, pick). |
| RANDARRAY | Generate array of random numbers for simulations or test cases. |
| ISODD | Check if a number is odd – often used in row formatting logic. |
| ISEVEN | Check if a number is even – used for alternate row logic. |
| CEILING | Round up to nearest specified multiple (e.g., shipping batch sizes). |
| CEILING.MATH | Round up with control over direction, useful in financial calculations. |
| CEILING.PRECISE | Always rounds up towards zero to a multiple – precise control. |
| FLOOR | Round down to nearest specified multiple (e.g., price tiers). |
| FLOOR.MATH | Round down with direction control, handy in accounting. |
| FLOOR.PRECISE | Always rounds down towards zero – for exact rounding needs. |
| GCD | Find greatest common divisor (e.g., for simplifying ratios or fractions). |
| LCM | Find least common multiple (e.g., resynchronizing schedules or cycles). |
| SEQUENCE | Generate ordered series (e.g., 1,2,3... for form IDs or test sets). |
| SIGN | Return -1, 0, or 1 to indicate sign of a number for directional logic. |
| EXP | Raise Euler’s number to power (e.g., growth/decay rate modeling). |
| TANH | Hyperbolic tangent – used in machine learning or stat functions. |
| SINH | Hyperbolic sine – used in scientific simulations. |
| COSH | Hyperbolic cosine – niche uses in physics or engineering. |
| SERIESSUM | Calculate power series – advanced scientific modeling. |
| GAMMALN | Log Gamma function – useful in probability distributions. |
| GAMMALN.PRECISE | Improved precision for GAMMALN – more accurate stats. |
| BASE | Convert number to another base (e.g., decimal to binary for coding). |
| DECIMAL | Convert from other base (e.g., binary to decimal for analysis). |
| IMPOWER | Raise complex number to a power – used in electrical engineering. |
| IMSQRT | Get square root of a complex number – niche math applications. |
| MULTINOMIAL | Used in probability theory for combinations. |
| MUNIT | Return identity matrix (used in linear algebra and modeling). |
| IMLN | Natural log of complex number – advanced mathematical analysis. |
| ERFC | Complementary error function – used in statistics and probability. |
| ERFC.PRECISE | More accurate version of ERFC – useful in precise scientific analysis. |
| COMBIN | Calculate combinations – useful in probability/statistics. |
| COMBINA | Combinations with repetition – planning or permutations with overlap. |
Parser Functions
Parser Functions (Data Conversion)
| Function | Description |
|---|---|
| CONVERT | Convert between measurement units (e.g., meters to feet, Celsius to Fahrenheit). |
| TO_DATE | Convert numeric serial (like 45123) to date format (e.g., "2023-07-01"). |
| TO_DOLLARS | Convert numbers into currency format with $ sign (e.g., 200 → $200.00). |
| TO_PERCENT | Convert decimal to percentage format (e.g., 0.85 → 85%). |
| TO_PURE_NUMBER | Remove formatting and return just the number (e.g., from "₹1,000" to 1000). |
| TO_TEXT | Convert a value (number, date, etc.) to plain text (e.g., 123 → "123"). |
Operator Functions
Operator Functions (Arithmetic & Comparison)
| Function | Description |
|---|---|
| ADD | Add two numbers together (e.g., base salary + bonus). |
| CONCAT | Join multiple text values into one (e.g., first name + last name). |
| DIVIDE | Divide one number by another (e.g., total cost ÷ units). |
| EQ | Check if two values are equal (e.g., test result matches expected value). |
| GT | Return TRUE if one number is greater than another (e.g., sales > target). |
| GTE | Return TRUE if a value is greater than or equal to another (e.g., >= minimum requirement). |
| ISBETWEEN | Check if a number falls between two values (e.g., score between 60 and 90). |
| LT | Return TRUE if a value is less than another (e.g., expenses < limit). |
| LTE | Return TRUE if a value is less than or equal to another. |
| MINUS | Subtract one number from another (e.g., budget - spending). |
| MULTIPLY | Multiply two numbers (e.g., unit price × quantity). |
| NE | Return TRUE if two values are not equal. |
| POW | Raise a number to a power (e.g., 3^2 = 9). |
| UMINUS | Return the negative value of a number (e.g., -profit). |
| UNARY_PERCENT | Return a value expressed as a percentage (e.g., 0.25 becomes 25%). |
| UNIQUE | Extract a list of distinct values (e.g., unique names from a dataset). |
| UPLUS | Return a number unchanged but ensure it's positive (rarely needed). |
Statistical Functions
Statistical Functions (Data Analysis & Metrics)
| Function | Description |
|---|---|
| AVERAGE | Calculate average marks, salary, expenses. |
| MEDIAN | Find the median value in a dataset (e.g., middle salary). |
| MODE | Find the most frequent value (e.g., most common grade). |
| STDEV.S | Calculate sample standard deviation for data spread. |
| STDEV | Legacy: sample standard deviation. |
| VAR.S | Sample variance for data (e.g., test score analysis). |
| VAR | Legacy: sample variance. |
| COUNT | Count numeric values (e.g., number of sales). |
| COUNTA | Count all non-empty values (e.g., filled survey fields). |
| MAX | Find highest value (e.g., max revenue). |
| MIN | Find lowest value (e.g., min expenses). |
| LARGE | Find nth largest value (e.g., 2nd highest score). |
| SMALL | Find nth smallest value (e.g., 3rd lowest sales). |
| AVERAGEIF | Average with single condition (e.g., scores > 70). |
| AVERAGEIFS | Average with multiple conditions. |
| MAXIFS | Max value with multiple conditions. |
| MINIFS | Min value with multiple conditions. |
| FORECAST.LINEAR | Predict future sales, revenue, or stock levels. |
| CORREL | Find correlation between two variables (e.g., height vs weight). |
| PEARSON | Return the Pearson correlation coefficient. |
| SLOPE | Return slope of regression line. |
| INTERCEPT | Find y-intercept of regression line. |
| RSQ | Return R-squared value to measure linear fit. |
| RANK | Rank values in a dataset (e.g., top performer). |
| RANK.EQ | Rank values with equal values assigned same rank. |
| RANK.AVG | Average ranking for ties. |
| QUARTILE.INC | Divide data into 4 equal quartiles. |
| PERCENTILE.INC | Return value at given percentile (e.g., 90th). |
| STDEVA | Standard deviation including text/logical values. |
| STDEVPA | Population standard deviation including logical/text values. |
| VARA | Sample variance including logical/text. |
| VARPA | Population variance including logical/text. |
| DEVSQ | Sum of squared deviations. |
| CONFIDENCE.T | Confidence interval using T-distribution. |
| CONFIDENCE.NORM | Confidence interval assuming normal distribution. |
| T.TEST | Compare two data sets (e.g., drug vs placebo). |
| T.DIST | T-distribution analysis. |
| T.DIST.2T | Two-tailed T-distribution. |
| T.DIST.RT | Right-tailed T-distribution. |
| T.INV | Inverse of T-distribution. |
| T.INV.2T | Inverse of two-tailed T-distribution. |
| Z.TEST | Z-test for mean comparison. |
| KURT | Measure peakedness of a distribution. |
| SKEW | Measure asymmetry of distribution. |
| STEYX | Standard error of Y estimates in regression. |
| TRIMMEAN | Mean excluding outliers. |
| GEOMEAN | Calculate geometric mean (e.g., growth rates). |
| HARMEAN | Calculate harmonic mean (e.g., ratios, rates). |
| BETA.DIST | Probability in beta distribution. |
| BETA.INV | Inverse of beta distribution. |
| BINOM.DIST | Probability in binomial distribution. |
| BINOM.INV | Inverse of binomial distribution. |
| CHISQ.TEST | Chi-square hypothesis test. |
| CHISQ.DIST.RT | Right-tailed chi-square distribution. |
| CHISQ.DIST | Chi-square distribution. |
| CHISQ.INV | Inverse of chi-square distribution. |
| CHISQ.INV.RT | Inverse of right-tailed chi-square. |
| GAMMA.DIST | Gamma distribution value. |
| GAMMA.INV | Inverse of gamma distribution. |
| LOGNORM.DIST | Log-normal distribution value. |
| LOGNORM.INV | Inverse of log-normal distribution. |
| HYPGEOM.DIST | Hypergeometric distribution. |
| WEIBULL.DIST | Weibull distribution value. |
| EXPON.DIST | Exponential distribution value. |
| F.DIST | F-distribution value. |
| F.INV | Inverse of F-distribution. |
| F.TEST | F-test for comparing variances. |
| GAUSS | Standard normal cumulative distribution. |
| PHI | Value of standard normal PDF. |
| NORM.DIST | Normal distribution value. |
| NORM.S.DIST | Standard normal distribution value. |
| NORM.INV | Inverse of normal distribution. |
| NORM.S.INV | Inverse of standard normal. |
| PERMUT | Number of permutations. |
| PERMUTATIONA | Permutations with repetitions. |
| PROB | Probability based on frequency and values. |
| PERCENTRANK.INC | Percent rank including boundaries. |
| PERCENTRANK.EXC | Percent rank excluding boundaries. |
Engineering Functions
Engineering Functions (Conversions, Binary, Complex Numbers)
| Function | Description |
|---|---|
| BIN2DEC | Convert binary sensor readings into decimal to make them readable in monitoring dashboards. |
| BIN2HEX | Translate binary memory addresses into hexadecimal format for low-level programming. |
| BIN2OCT | Convert binary to octal in digital circuit diagrams and legacy systems. |
| BITAND | Perform binary checks on feature flags or permissions within application settings. |
| BITLSHIFT | Simulate hardware-level bitwise shifts in embedded system logic or optimization. |
| BITOR | Combine permission flags using binary OR operation for access control. |
| BITRSHIFT | Right-shift bit values for efficient data compression in microcontroller design. |
| BITXOR | Build lightweight encryption methods or data validation routines using XOR logic. |
| COMPLEX | Represent electrical impedance as complex numbers in alternating current analysis (e.g., 3+4i). |
| DEC2BIN | Convert decimal inputs into binary to ensure compatibility with digital interfaces. |
| DEC2HEX | Convert decimal to hex for use in CSS color codes or memory address mapping. |
| DEC2OCT | Translate decimal to octal for use in industrial systems using octal standards. |
| DELTA | Check whether two values are equal (returns 1 or 0), often used for simple quality control validation. |
| ERF | Estimate probability range or tolerance errors in manufacturing or signal analysis. |
| ERF.PRECISE | Calculate more accurate error margins for scientific research or simulations. |
| GESTEP | Test if a sensor input or reading exceeds a given threshold (returns 0 or 1). |
| HEX2BIN | Convert hexadecimal machine-level addresses into binary for debugging. |
| HEX2DEC | Convert microcontroller output in hex format into decimal for analysis. |
| HEX2OCT | Transform hexadecimal color data or firmware settings into octal format. |
| IMABS | Find the absolute value (magnitude) of a complex number for impedance analysis. |
| IMAGINARY | Extract only the imaginary part of a complex signal for waveform study. |
| IMARGUMENT | Measure the phase angle of alternating current or wave signal. |
| IMCONJUGATE | Balance an electrical load by using the conjugate of complex impedance. |
| IMCOS | Compute the cosine of complex signals used in signal wave analysis. |
| IMCOSH | Apply hyperbolic cosine function on complex signals for energy modeling. |
| IMCOT | Evaluate cotangent for complex angles in trigonometric computations. |
| IMCOTH | Analyze wave damping in complex hyperbolic equations. |
| IMCSC | Use reciprocal of sine in waveform shaping or physics equations. |
| IMCSCH | Apply hyperbolic reciprocal in system stability analysis. |
| IMDIV | Divide two electrical loads or voltages represented as complex values. |
| IMEXP | Model signal gain by applying the exponential function to a complex number. |
| IMLOG | Determine signal attenuation or growth in logarithmic scale. |
| IMLOG10 | Evaluate power level of a complex signal using base-10 logarithms. |
| IMLOG2 | Useful in data encoding where binary (base-2) scaling is required. |
| IMPRODUCT | Multiply complex impedances in serial circuit components. |
| IMREAL | Return the real part of a signal for accurate display or further math. |
| IMSEC | Compute secant of complex phase angle in circuit signals. |
| IMSECH | Apply hyperbolic secant in high-frequency AC behavior modeling. |
| IMSIN | Calculate sine of a complex voltage, used in phasor representation. |
| IMSINH | Solve for sinusoidal waveforms in differential physics. |
| IMSUB | Subtract signal wave voltages modeled as complex numbers. |
| IMSUM | Add total voltage drop across multiple complex sources. |
| IMTAN | Tangent function for determining current phase lag. |
| IMTANH | Simulate alternating signal distortion in transmission systems. |
| OCT2BIN | Convert archived system logs stored in octal into binary. |
| OCT2DEC | Read octal-encoded inputs as decimal for modern applications. |
| OCT2HEX | Update octal system values into hex for compatibility in modern tech. |
Financial Functions
Financial Functions (Loans, Investments, Bonds, Depreciation)
| Function | Description |
|---|---|
| PMT | Calculate monthly loan EMIs for a home, car, or personal loan. |
| NPV | Assess profitability of an investment project by comparing inflows to the initial cost. |
| IRR | Calculate return rate from a series of business or investment cash flows. |
| FV | Predict future value of savings with monthly contributions and compound interest. |
| PV | Determine the current value of a future cash flow (e.g., lump sum or series of payments). |
| RATE | Find out the interest rate applied to a loan or investment. |
| NPER | Calculate how long it will take to repay a loan or reach an investment goal. |
| IPMT | Identify the interest part of a loan payment for budgeting or amortization. |
| PPMT | Identify the principal part of a loan payment in a given month. |
| CUMIPMT | Get total interest paid over a defined period of loan repayment. |
| CUMPRINC | Get total principal paid over a specified number of payment periods. |
| SLN | Apply straight-line depreciation to business assets for tax purposes. |
| DDB | Use accelerated depreciation to write off assets faster in early years. |
| DB | Use declining balance depreciation for accounting more asset value loss upfront. |
| XNPV | Calculate NPV with irregular cash flow dates (e.g., freelance invoices). |
| XIRR | Determine the return rate from cash flows with varying dates. |
| MIRR | Adjust IRR for cost of capital and reinvestment rate, useful for complex projects. |
| PDURATION | Estimate how long an investment will take to double at a given interest rate. |
| EFFECT | Compare effective annual interest rate from a nominal rate. |
| NOMINAL | Convert an effective interest rate into its nominal equivalent. |
| ACCRINT | Calculate interest earned but not yet paid for bonds between payments. |
| ACCRINTM | Determine total interest at maturity for zero-coupon bonds. |
| PRICE | Estimate bond price based on interest rates and terms. |
| PRICEDISC | Compute discounted bond price (e.g., short-term Treasury bill). |
| PRICEMAT | Determine bond price at maturity including interest. |
| YIELD | Estimate annual return from a bond that pays regular interest. |
| YIELDDISC | Determine yield on discounted securities like T-bills. |
| YIELDMAT | Estimate the return on bonds paying interest only at maturity. |
| TBILLPRICE | Compute purchase price of a Treasury bill based on its yield. |
| TBILLYIELD | Find yield on short-term government Treasury bills. |
| TBILLEQ | Convert T-bill yield into bond-equivalent yield for comparison. |
| COUPNUM | Count how many coupon payments a bond will make before maturity. |
| COUPDAYSNC | Get number of days until the next bond coupon payment. |
| COUPDAYS | Find number of days in the current bond coupon period. |
| COUPDAYBS | Calculate days from bond issue date to settlement. |
| COUPNCD | Find next scheduled bond coupon date. |
| COUPPCD | Identify last coupon payment date before settlement. |
| DURATION | Measure bond’s sensitivity to interest rate changes. |
| MDURATION | Use modified duration to analyze interest rate risk. |
| AMORLINC | Apply linear depreciation method based on French accounting standards. |
| ISPMT | Calculate simple interest over time using straight-line method. |
| DISC | Compute discount rate on a security purchased at a discount. |
| RRI | Determine equivalent interest rate for compound growth. |
| VDB | Apply variable declining depreciation with control over life and period. |
| FVSCHEDULE | Project future value with varying interest rates per period. |
| DOLLARDE | Convert bond prices from fractional to decimal format. |
| DOLLARFR | Convert bond prices from decimal to fractional format. |
| INTRATE | Compute interest rate on securities with full investment. |
| RECEIVED | Calculate total amount received from an investment at maturity. |
| SYD | Use sum-of-years' digits depreciation to reduce asset value over time. |
Array Functions
Array Functions (Matrix, Dynamic Arrays, Transformations)
| Function | Description |
|---|---|
| SUMPRODUCT | Calculate total weighted scores or expenses (e.g., sales × unit price). |
| TRANSPOSE | Switch rows and columns for better report formatting. |
| MMULT | Perform matrix multiplication for modeling financial forecasts or engineering formulas. |
| FREQUENCY | Group test scores or ages into frequency bins for analysis. |
| LINEST | Generate linear regression statistics to analyze trends. |
| TREND | Predict future values using existing data points. |
| LOGEST | Fit exponential growth models to historical data. |
| GROWTH | Forecast exponential trends like compound interest or population growth. |
| REDUCE | Aggregate data with custom logic, like computing running totals. |
| SCAN | Track progressive totals or results row-by-row. |
| MAP | Apply custom calculations to multiple arrays (e.g., apply tax logic). |
| BYROW | Perform row-wise calculations such as sum per student/test. |
| BYCOL | Perform column-wise operations such as normalize scores per subject. |
| TOCOL | Convert 2D data ranges into a single column for processing. |
| TOROW | Convert matrix or range into a single row. |
| VSTACK | Stack multiple datasets vertically, useful for appending forms or logs. |
| HSTACK | Combine columns from multiple sources side-by-side. |
| WRAPCOLS | Format a long list into a fixed number of columns (e.g., 3-column display). |
| WRAPROWS | Format a dataset into wrapped rows for reports. |
| CHOOSECOLS | Extract only specific columns from a dataset for cleaner reports. |
| CHOOSEROWS | Select specific rows based on position for sampling or slicing. |
| FLATTEN | Convert ranges into a single column list, removing structure. |
| ARRAY_CONSTRAIN | Limit array output (rows/columns) for better display or summarization. |
| MAKEARRAY | Generate a custom-shaped array with dynamic formulas. |
| MDETERM | Calculate matrix determinant for linear algebra analysis. |
| MINVERSE | Find inverse of a matrix (used in solving equations). |
| SUMX2MY2 | Statistical calculation: ∑(x² − y²), used in variance analysis. |
| SUMX2PY2 | Statistical calculation: ∑(x² + y²), often used in energy estimation. |
| SUMXMY2 | Statistical calculation: ∑(x − y)² for comparing datasets. |
Database Functions
Database Functions (Conditional Calculations & Analysis)
| Function | Description |
|---|---|
| DAVERAGE | Calculate average salary, score, or price where criteria are met. |
| DCOUNT | Count numeric records (e.g., how many employees are above age 30). |
| DCOUNTA | Count non-empty entries (e.g., how many employees have department names filled). |
| DGET | Fetch a specific record's value (e.g., get department of employee with ID = 123). |
| DMAX | Find maximum value (e.g., highest sale in a region). |
| DMIN | Find minimum value (e.g., lowest product cost in a category). |
| DPRODUCT | Multiply values conditionally (e.g., quantity × price for selected product type). |
| DSTDEV | Estimate standard deviation of scores/salaries for filtered data (sample). |
| DSTDEVP | Calculate population standard deviation (e.g., delivery times for all vendors). |
| DSUM | Sum up values that match condition (e.g., total sales for a product line). |
| DVAR | Estimate sample variance (e.g., time taken by selected employees for tasks). |
| DVARP | Calculate population variance (e.g., salary range for all employees). |
Google Functions
Google Functions (Sheets & Dynamic Data)
| Function | Description |
|---|---|
| ARRAYFORMULA | Apply formulas across a range without copying (e.g., calculate totals for entire column). |
| DETECTLANGUAGE | Automatically identify the language of a sentence (e.g., detect if text is Hindi, French, etc.). |
| GOOGLEFINANCE | Fetch real-time stock prices, financial data (e.g., get current price of AAPL stock). |
| GOOGLETRANSLATE | Translate text from one language to another (e.g., Hindi to English). |
| IMAGE | Display an image directly in a cell using a URL (e.g., insert product images). |
| QUERY | Filter, sort, and analyze data using SQL-like syntax (e.g., select only rows with sales > 500). |
| SPARKLINE | Create mini-charts inside cells to visualize trends (e.g., sales trend for each month). |
Information Functions
Information Functions (Error Checking, Validation, Cell Info)
| Function | Description |
|---|---|
| ERROR.TYPE | Identify the type of error returned by a formula (e.g., distinguish #DIV/0!, #N/A). |
| ISBLANK | Check if a cell is empty (e.g., to validate required form fields). |
| ISDATE | Check if a value is a valid date (e.g., validate DOB inputs). |
| ISEMAIL | Check if the text is a valid email format (e.g., for user form validations). |
| ISERR | Test if a formula results in an error except #N/A (e.g., check invalid math ops). |
| ISERROR | Check if a formula returns any error (e.g., use with IF to prevent breakage). |
| ISFORMULA | Determine if a cell contains a formula (e.g., for auditing data). |
| ISLOGICAL | Check if value is TRUE or FALSE (e.g., ensure a checkbox is used correctly). |
| ISNA | Check if value is #N/A (e.g., lookup didn't find result). |
| ISNONTEXT | Check if value is not text (e.g., for data cleaning). |
| ISNUMBER | Validate if value is numeric (e.g., ensure phone number or ID is a number). |
| ISREF | Check if value is a valid cell reference (used rarely, often in advanced formulas). |
| ISTEXT | Confirm if value is text (e.g., for filtering names or categories). |
| N | Convert non-numeric values to numbers (e.g., TRUE → 1, text → 0). |
| NA | Return #N/A intentionally (e.g., to show missing data or skip a value). |
| TYPE | Get the data type of a value (e.g., 1 for number, 2 for text). |
| CELL | Return info about a cell (e.g., address, content type, formatting). |
Web Functions
Web Functions (Data Import, URLs, Links)
| Function | Description |
|---|---|
| ENCODEURL | Encode text to make it URL-safe (e.g., for API links or query strings). |
| HYPERLINK | Create clickable text linking to a website or file. |
| IMPORTDATA | Import raw CSV or TSV data from a public URL (e.g., from government data portals). |
| IMPORTFEED | Import data from an RSS or ATOM feed (e.g., latest news headlines). |
| IMPORTHTML | Scrape tables or lists from public webpages (e.g., live stock data, movie listings). |
| IMPORTRANGE | Import a range of cells from another Google Sheet (e.g., fetch team reports). |
| IMPORTXML | Extract structured data (e.g., price, rating) from XML or HTML using XPath queries. |
| ISURL | Validate whether a string is a valid URL (e.g., in web forms or product links). |
Cube Functions
Cube Functions (Data Model, KPIs, Members)
| Function | Description |
|---|---|
| CUBEKPIMEMBER | Return a Key Performance Indicator (KPI) defined in the data model (e.g., profit margin). |
| CUBEMEMBER | Fetch a specific member (e.g., "Product A") from a cube hierarchy. |
| CUBEMEMBERPROPERTY | Return a property (e.g., description, color) of a cube member. |
| CUBERANKEDMEMBER | Return the nth most ranked item from a cube set (e.g., 3rd top-selling product). |
| CUBESET | Define a set of members from a cube for further analysis or filtering. |
| CUBESETCOUNT | Count the number of items in a cube set (e.g., number of regions). |
| CUBEVALUE | Return the actual value from a cube intersection (e.g., sales for Q1 in Asia). |
Need a ready workbook?
Download the full Excel file with all examples, answer keys, and printable cheatsheet.
Download the full Excel file with all examples, answer keys, and printable cheatsheet.
Found an issue? Tell us. We’ll fix it quickly.