Top 129 Excel Formulas
Top 129 Excel Formulas

Top 129 Excel Formulas

Unleash Your Inner Excel Guru: A Comprehensive Guide to 129 Essential Formulas

Microsoft Excel: it’s more than just a spreadsheet. It’s a powerful data analysis tool, a project management hub, and even a rudimentary database, all rolled into one. However, the true power of Excel lies in its vast library of formulas. While mastering every single function might seem daunting, understanding a core set of formulas can dramatically improve your efficiency and unlock new analytical capabilities.

This comprehensive guide aims to equip you with a strong foundation by exploring 129 essential Excel formulas, categorized for easier learning and application. We’ll cover everything from basic arithmetic to advanced statistical analysis, financial modeling, and logical operations. Consider this your ultimate cheat sheet for Excel mastery.

I. Basic Math & Statistical Formulas:

  • =SUM(number1, [number2], ...): Adds all the numbers in a range of cells.
    • Example: =SUM(A1:A10) – Adds the values in cells A1 through A10.
    • Use Case: Calculating total sales, expenses, or scores.
  • =AVERAGE(number1, [number2], ...): Calculates the arithmetic mean of a range of numbers.
    • Example: =AVERAGE(B1:B20) – Calculates the average of the values in cells B1 through B20.
    • Use Case: Finding the average test score, monthly sales, or temperature.
  • =MEDIAN(number1, [number2], ...): Finds the middle value in a set of numbers. Useful when you have outliers that skew the average.
    • Example: =MEDIAN(C1:C15) – Finds the median value in cells C1 through C15.
    • Use Case: Determining the “typical” income when some individuals have extremely high earnings.
  • =MIN(number1, [number2], ...): Returns the smallest number in a set of values.
    • Example: =MIN(D1:D30) – Finds the smallest value in cells D1 through D30.
    • Use Case: Identifying the lowest price, minimum temperature, or smallest quantity.
  • =MAX(number1, [number2], ...): Returns the largest number in a set of values.
    • Example: =MAX(E1:E12) – Finds the largest value in cells E1 through E12.
    • Use Case: Identifying the highest score, maximum temperature, or largest sale.
  • =COUNT(value1, [value2], ...): Counts the number of cells in a range that contain numbers.
    • Example: =COUNT(F1:F25) – Counts the number of cells with numerical values in cells F1 through F25.
    • Use Case: Determining how many students took a test, or how many orders contain a numerical quantity.
  • =COUNTA(value1, [value2], ...): Counts the number of cells in a range that are not empty.
    • Example: =COUNTA(G1:G18) – Counts the number of non-empty cells in cells G1 through G18.
    • Use Case: Counting the number of participants who filled out a survey, or the number of products listed in a catalog.
  • =PRODUCT(number1, [number2], ...): Multiplies all the numbers given as arguments.
    • Example: =PRODUCT(H1:H5) – Multiplies the values in cells H1 through H5.
    • Use Case: Calculating total revenue based on price and quantity, or compounding interest.
  • =STDEV.S(number1, [number2], ...): Calculates the sample standard deviation. Measures the spread of data around the mean (assuming the data is a sample of a larger population).
    • Example: =STDEV.S(I1:I20) – Calculates the sample standard deviation of the values in cells I1 through I20.
    • Use Case: Assessing the volatility of stock prices, or the consistency of manufacturing processes.
  • =VAR.S(number1, [number2], ...): Calculates the sample variance. The square of the standard deviation, also measuring data spread (sample).
    • Example: =VAR.S(J1:J15) – Calculates the sample variance of the values in cells J1 through J15.
    • Use Case: Similar to STDEV.S, used in statistical analysis to understand data variability.
  • =ROUND(number, num_digits): Rounds a number to a specified number of digits.
    • Example: =ROUND(K1, 2) – Rounds the value in cell K1 to 2 decimal places.
    • Use Case: Presenting financial data with appropriate precision, or simplifying calculations.
  • =ROUNDUP(number, num_digits): Rounds a number up, away from zero, to a specified number of digits.
    • Example: =ROUNDUP(L1, 0) – Rounds the value in cell L1 up to the nearest whole number.
    • Use Case: Calculating the number of items needed to fulfill an order, ensuring you have enough.
  • =ROUNDDOWN(number, num_digits): Rounds a number down, toward zero, to a specified number of digits.
    • Example: =ROUNDDOWN(M1, 0) – Rounds the value in cell M1 down to the nearest whole number.
    • Use Case: Calculating the number of complete units that can be produced with a given amount of material.
  • =INT(number): Returns the integer part of a number by truncating the decimal portion.
    • Example: =INT(N1) – Returns the integer part of the value in cell N1.
    • Use Case: Extracting the whole number portion of a calculation, e.g., years of service.
  • =MOD(number, divisor): Returns the remainder after a number is divided by a divisor.
    • Example: =MOD(O1, 7) – Returns the remainder when the value in cell O1 is divided by 7.
    • Use Case: Determining if a number is even or odd, or calculating recurring schedules.
  • =ABS(number): Returns the absolute value of a number.
    • Example: =ABS(P1) – Returns the absolute value of the value in cell P1.
    • Use Case: Calculating the difference between two values regardless of order.
  • =SQRT(number): Returns the square root of a number.
    • Example: =SQRT(Q1) – Returns the square root of the value in cell Q1.
    • Use Case: Geometric calculations, or statistical analysis.
  • =POWER(number, power): Returns the result of a number raised to a power.
    • Example: =POWER(R1, 3) – Returns the value in cell R1 raised to the power of 3 (cubed).
    • Use Case: Calculating compound interest, or exponential growth.
  • =RAND(): Returns a random number greater than or equal to 0 and less than 1. Volatile (changes every time the worksheet is calculated).
    • Example: =RAND() – Generates a random number.
    • Use Case: Simulating random events, or selecting a random sample.
  • =RANDBETWEEN(bottom, top): Returns a random integer between the specified bottom and top numbers (inclusive). Volatile.
    • Example: =RANDBETWEEN(1, 100) – Generates a random integer between 1 and 100.
    • Use Case: Generating lottery numbers, or creating random test questions.
  • =COUNTBLANK(range): Counts the number of empty cells in a specified range.
    • Example: =COUNTBLANK(S1:S30) – Counts the number of empty cells in cells S1 through S30.
    • Use Case: Identifying missing data in a dataset, or tracking task completion.
  • =LARGE(array, k): Returns the k-th largest value in a data set.
    • Example: =LARGE(T1:T10, 3) – Returns the 3rd largest value in cells T1 through T10.
    • Use Case: Finding the top performers, or identifying outliers.
  • =SMALL(array, k): Returns the k-th smallest value in a data set.
    • Example: =SMALL(U1:U10, 2) – Returns the 2nd smallest value in cells U1 through U10.
    • Use Case: Finding the worst performers, or identifying the lowest costs.
  • =RANK.EQ(number, ref, [order]): Returns the rank of a number in a list of numbers. [order] is optional: 0 (or omitted) for descending order, 1 for ascending. If there are ties, it returns the highest rank.
    • Example: =RANK.EQ(V1, V1:V20, 0) – Returns the rank of the value in cell V1 within the range V1:V20, in descending order.
    • Use Case: Ranking students based on test scores, or ranking products based on sales.
  • =PERCENTILE.INC(array, k): Returns the k-th percentile of values in a range (inclusive). k must be between 0 and 1.
    • Example: =PERCENTILE.INC(W1:W100, 0.25) – Returns the 25th percentile of the values in cells W1 through W100.
    • Use Case: Determining the threshold for the top 10% of performers, or analyzing income distribution.
  • =QUARTILE.INC(array, quart): Returns the quartile of a data set (inclusive). quart can be 0 (minimum value), 1 (1st quartile/25th percentile), 2 (median/50th percentile), 3 (3rd quartile/75th percentile), or 4 (maximum value).
    • Example: =QUARTILE.INC(X1:X50, 1) – Returns the first quartile (25th percentile) of the values in cells X1 through X50.
    • Use Case: Understanding the distribution of data, identifying the range of typical values.
  • =MODE.SNGL(number1, [number2], ...): Returns the most frequently occurring number in a data set.
    • Example: =MODE.SNGL(Y1:Y30) – Returns the most frequent value in cells Y1 through Y30.
    • Use Case: Identifying the most popular product, or the most common response in a survey.

II. Logical Formulas:

  • =IF(logical_test, value_if_true, value_if_false): Performs a logical test and returns one value if the test is TRUE and another value if the test is FALSE.
    • Example: =IF(A1>10, "Yes", "No") – Returns “Yes” if the value in A1 is greater than 10, otherwise returns “No”.
    • Use Case: Grading students based on scores, or determining if a product is in stock.
  • =AND(logical1, [logical2], ...): Returns TRUE if all of its arguments are TRUE.
    • Example: =AND(A1>0, B1<100) – Returns TRUE if the value in A1 is greater than 0 AND the value in B1 is less than 100.
    • Use Case: Verifying multiple conditions are met before performing an action.
  • =OR(logical1, [logical2], ...): Returns TRUE if any of its arguments are TRUE.
    • Example: =OR(A1="Red", B1="Blue") – Returns TRUE if the value in A1 is “Red” OR the value in B1 is “Blue”.
    • Use Case: Checking if at least one condition is met.
  • =NOT(logical): Reverses the logic of its argument. Returns TRUE if the argument is FALSE, and FALSE if the argument is TRUE.
    • Example: =NOT(A1=B1) – Returns TRUE if the value in A1 is not equal to the value in B1.
    • Use Case: Inverting a logical condition.
  • =IFERROR(value, value_if_error): Returns a specified value if a formula evaluates to an error; otherwise, returns the result of the formula.
    • Example: =IFERROR(A1/B1, 0) – Returns the result of A1 divided by B1, but if the result is an error (e.g., division by zero), it returns 0.
    • Use Case: Handling potential errors in calculations, preventing the display of error messages.
  • =IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...): Checks multiple conditions and returns a value corresponding to the first TRUE condition. A more concise way to write nested IF statements (Excel 2016 and later).
    • Example: =IFS(A1>90, "A", A1>80, "B", A1>70, "C", TRUE, "D") – Assigns a grade based on the score in A1. If A1 is greater than 90, returns “A”; if greater than 80, returns “B”; if greater than 70, returns “C”; otherwise, returns “D”. The TRUE at the end acts as a catch-all, similar to an ELSE in a traditional IF statement.
    • Use Case: Assigning grades, classifying data into multiple categories.

III. Text Formulas:

  • =LEFT(text, [num_chars]): Returns the specified number of characters from the beginning of a text string.
    • Example: =LEFT(A1, 3) – Returns the first 3 characters of the text in cell A1.
    • Use Case: Extracting the first few characters of a code, name, or address.
  • =RIGHT(text, [num_chars]): Returns the specified number of characters from the end of a text string.
    • Example: =RIGHT(A1, 4) – Returns the last 4 characters of the text in cell A1.
    • Use Case: Extracting the last few digits of a phone number, or the file extension from a filename.
  • =MID(text, start_num, num_chars): Returns a specified number of characters from a text string, starting at a specified position.
    • Example: =MID(A1, 2, 5) – Returns 5 characters from the text in cell A1, starting at the 2nd character.
    • Use Case: Extracting a specific portion of a code, name, or address.
  • =LEN(text): Returns the number of characters in a text string.
    • Example: =LEN(A1) – Returns the number of characters in the text in cell A1.
    • Use Case: Validating data length, or calculating the number of characters in a field.
  • =CONCATENATE(text1, [text2], ...): Joins several text strings into one text string. You can also use the & operator.
    • Example: =CONCATENATE(A1, " ", B1) or =A1 & " " & B1 – Joins the text in cell A1, a space, and the text in cell B1.
    • Use Case: Creating full names from first and last names, or constructing addresses.
  • =TEXT(value, format_text): Formats a number as text using a specified format code.
    • Example: =TEXT(A1, "$0.00") – Formats the number in cell A1 as currency with two decimal places.
    • Use Case: Displaying numbers in a specific format, such as currency, dates, or percentages.
  • =FIND(find_text, within_text, [start_num]): Returns the starting position of one text string within another text string. Case-sensitive.
    • Example: =FIND("apple", A1) – Returns the starting position of the text “apple” within the text in cell A1.
    • Use Case: Locating specific words or characters within a text string.
  • =SEARCH(find_text, within_text, [start_num]): Returns the starting position of one text string within another text string. Not case-sensitive. Allows wildcard characters.
    • Example: =SEARCH("apple", A1) – Returns the starting position of the text “apple” (or “Apple”, “APPLE”, etc.) within the text in cell A1.
    • Use Case: Similar to FIND, but case-insensitive.
  • =REPLACE(old_text, start_num, num_chars, new_text): Replaces part of a text string with a different text string.
    • Example: =REPLACE(A1, 1, 5, "New") – Replaces the first 5 characters of the text in cell A1 with the text “New”.
    • Use Case: Correcting errors in data, or standardizing text formats.
  • =SUBSTITUTE(text, old_text, new_text, [instance_num]): Replaces all occurrences of a specified text string with another text string.
    • Example: =SUBSTITUTE(A1, "old", "new") – Replaces all occurrences of the text “old” with the text “new” in cell A1.
    • Use Case: Replacing specific words or characters in a text string. If you only want to replace the first occurrence, specify [instance_num] as 1.
  • =TRIM(text): Removes all spaces from a text string except for single spaces between words.
    • Example: =TRIM(A1) – Removes leading and trailing spaces from the text in cell A1, and reduces multiple spaces between words to single spaces.
    • Use Case: Cleaning up data with inconsistent spacing.
  • =LOWER(text): Converts all uppercase letters in a text string to lowercase.
    • Example: =LOWER(A1) – Converts all uppercase letters in the text in cell A1 to lowercase.
    • Use Case: Standardizing text for case-insensitive comparisons.
  • =UPPER(text): Converts all lowercase letters in a text string to uppercase.
    • Example: =UPPER(A1) – Converts all lowercase letters in the text in cell A1 to uppercase.
    • Use Case: Standardizing text for display purposes.
  • =PROPER(text): Capitalizes the first letter of each word in a text string.
    • Example: =PROPER(A1) – Capitalizes the first letter of each word in the text in cell A1.
    • Use Case: Formatting names and titles correctly.
  • =CHOOSE(index_num, value1, [value2], ...): Returns a value from a list of values based on an index number.
    • Example: =CHOOSE(2, "Red", "Blue", "Green") – Returns the 2nd value from the list, which is “Blue”.
    • Use Case: Assigning values based on a lookup number, or creating dynamic menus.

IV. Date & Time Formulas:

  • =TODAY(): Returns the current date. Volatile.
    • Example: =TODAY() – Returns today’s date.
    • Use Case: Displaying the current date, or calculating age.
  • =NOW(): Returns the current date and time. Volatile.
    • Example: =NOW() – Returns the current date and time.
    • Use Case: Timestamping events, or calculating elapsed time.
  • =DATE(year, month, day): Returns the serial number that represents a particular date.
    • Example: =DATE(2024, 1, 1) – Returns the date January 1, 2024.
    • Use Case: Creating dates from separate year, month, and day values.
  • =TIME(hour, minute, second): Returns the serial number that represents a particular time.
    • Example: =TIME(14, 30, 0) – Returns the time 2:30 PM.
    • Use Case: Creating times from separate hour, minute, and second values.
  • =YEAR(serial_number): Returns the year of a date.
    • Example: =YEAR(A1) – Returns the year of the date in cell A1.
    • Use Case: Extracting the year from a date.
  • =MONTH(serial_number): Returns the month of a date.
    • Example: =MONTH(A1) – Returns the month of the date in cell A1.
    • Use Case: Extracting the month from a date.
  • =DAY(serial_number): Returns the day of the month of a date.
    • Example: =DAY(A1) – Returns the day of the month of the date in cell A1.
    • Use Case: Extracting the day from a date.
  • =HOUR(serial_number): Returns the hour of a time value.
    • Example: =HOUR(A1) – Returns the hour of the time in cell A1.
    • Use Case: Extracting the hour from a time.
  • =MINUTE(serial_number): Returns the minute of a time value.
    • Example: =MINUTE(A1) – Returns the minute of the time in cell A1.
    • Use Case: Extracting the minute from a time.
  • =SECOND(serial_number): Returns the second of a time value.
    • Example: =SECOND(A1) – Returns the second of the time in cell A1.
    • Use Case: Extracting the second from a time.
  • =WEEKDAY(serial_number, [return_type]): Returns the day of the week as a number (1-7). [return_type] specifies which day the week starts on (e.g., 1 for Sunday, 2 for Monday).
    • Example: =WEEKDAY(A1, 2) – Returns the day of the week of the date in cell A1, where 1 is Monday and 7 is Sunday.
    • Use Case: Determining the day of the week for scheduling purposes.
  • =WEEKNUM(serial_number, [return_type]): Returns the week number of the year. [return_type] specifies which day the week starts on.
    • Example: =WEEKNUM(A1, 1) – Returns the week number of the year for the date in cell A1, where the week starts on Sunday.
    • Use Case: Tracking progress over weeks, or analyzing weekly trends.
  • =EDATE(start_date, months): Returns the date that is the specified number of months before or after a start date.
    • Example: =EDATE(A1, 3) – Returns the date that is 3 months after the date in cell A1.
    • Use Case: Calculating expiration dates, or scheduling recurring events.
  • =EOMONTH(start_date, months): Returns the last day of the month that is the specified number of months before or after a start date.
    • Example: =EOMONTH(A1, 0) – Returns the last day of the month for the date in cell A1.
    • Use Case: Calculating payment due dates, or determining the end of a fiscal period.
  • =DATEDIF(start_date, end_date, unit): Calculates the difference between two dates. unit specifies the unit of time to return (“Y” for years, “M” for months, “D” for days, “YM” for months ignoring years, “YD” for days ignoring years, “MD” for days ignoring months and years).
    • Example: =DATEDIF(A1, B1, "Y") – Returns the number of whole years between the dates in cells A1 and B1.
    • Use Case: Calculating age, tenure, or the duration of a project.

V. Lookup & Reference Formulas:

  • =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]): Looks for a value in the first column of a table array and returns a value in the same row from a specified column. [range_lookup] is TRUE for approximate match (table must be sorted ascending), FALSE for exact match.
    • Example: =VLOOKUP("Apple", A1:C10, 2, FALSE) – Looks for “Apple” in the first column of the table A1:C10 and returns the value from the 2nd column in the same row where “Apple” is found. Requires an exact match.
    • Use Case: Looking up prices based on product codes , or retrieving customer information based on ID.
  • =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]): Looks for a value in the first row of a table array and returns a value in the same column from a specified row. [range_lookup] is TRUE for approximate match (table must be sorted ascending), FALSE for exact match.
    • Example: =HLOOKUP("January", A1:L10, 2, FALSE) – Looks for “January” in the first row of the table A1:L10 and returns the value from the 2nd row in the same column where “January” is found. Requires an exact match.
    • Use Case: Looking up data in a horizontal table, such as finding sales figures for a specific month.
  • =INDEX(array, row_num, [column_num]): Returns a value or the reference to a value from within a table or range.
    • Example: =INDEX(A1:C10, 5, 2) – Returns the value in the 5th row and 2nd column of the range A1:C10.
    • Use Case: Retrieving data based on row and column numbers, often used with MATCH.
  • =MATCH(lookup_value, lookup_array, [match_type]): Returns the relative position of an item in an array that matches a specified value. [match_type] can be 1 (less than), 0 (exact match), or -1 (greater than).
    • Example: =MATCH("Apple", A1:A10, 0) – Returns the position of “Apple” in the range A1:A10. Requires an exact match.
    • Use Case: Finding the row number of a specific item in a list, often used with INDEX.
  • =OFFSET(reference, rows, cols, [height], [width]): Returns a reference to a range that is a specified number of rows and columns from a reference point.
    • Example: =OFFSET(A1, 2, 3, 1, 1) – Returns a reference to a cell that is 2 rows down and 3 columns to the right of cell A1 (which would be cell D3), and the range is 1 row by 1 column (a single cell).
    • Use Case: Creating dynamic ranges that adjust based on changing data.
  • =CHOOSE(index_num, value1, [value2], ...): Returns a value from a list of values based on an index number.
    • Example: =CHOOSE(2, "Red", "Blue", "Green") – Returns the 2nd value from the list, which is “Blue”.
    • Use Case: Selecting from a list of options based on a number. Note that this formula was also listed under “Text Formulas” because it can return text values.
  • =INDIRECT(ref_text, [a1]): Returns the reference specified by a text string.
    • Example: =INDIRECT("A1") – Returns the value in cell A1. =INDIRECT(B1) – If B1 contains the text “C5”, then this formula will return the value in cell C5.
    • Use Case: Creating dynamic references based on text values. Be careful using this function, as it can make your spreadsheet difficult to understand and debug.

VI. Array Formulas (Entered with Ctrl+Shift+Enter):

Note: Array formulas are powerful but can also slow down your spreadsheet if used excessively. They are entered by pressing Ctrl+Shift+Enter instead of just Enter.

  • =SUM(IF(A1:A10="Apple", B1:B10, 0)): Sums the values in B1:B10 only where the corresponding cell in A1:A10 is “Apple”.
    • Example: Entered with Ctrl+Shift+Enter. Calculates the total sales for “Apple” products from a list of products and sales figures.
    • Use Case: Performing calculations on a subset of data based on criteria.
  • =AVERAGE(IF(A1:A10>10, B1:B10)): Averages the values in B1:B10 only where the corresponding cell in A1:A10 is greater than 10.
    • Example: Entered with Ctrl+Shift+Enter. Calculates the average score of students who scored above 10 on a previous test.
    • Use Case: Calculating averages based on criteria.
  • =MAX(IF(A1:A10="Red", B1:B10)): Finds the maximum value in B1:B10 only where the corresponding cell in A1:A10 is “Red”.
    • Example: Entered with Ctrl+Shift+Enter. Finds the highest sales figure for “Red” products.
    • Use Case: Finding maximum or minimum values based on criteria.
  • =TRANSPOSE(array): Transposes a range of cells (switches rows and columns).
    • Example: =TRANSPOSE(A1:B5) – Transposes the range A1:B5. Select a 5×2 range of cells before entering this formula, then press Ctrl+Shift+Enter.
    • Use Case: Reorganizing data from horizontal to vertical or vice-versa.

VII. Statistical Formulas (Advanced):

  • =STDEV.S(number1,[number2],...): Calculates the sample standard deviation. This assumes your data is a sample of a larger population.
    • Example: =STDEV.S(A1:A100) – Calculates the standard deviation of the numbers in cells A1 through A100.
    • Use Case: Measuring the dispersion or variability in a set of data points (e.g., test scores, product measurements).
  • =STDEV.P(number1,[number2],...): Calculates the population standard deviation. Use this if your data represents the entire population you are interested in.
    • Example: =STDEV.P(A1:A100)
    • Use Case: Similar to STDEV.S, but for the entire population. Important to choose the correct one!
  • =VAR.S(number1,[number2],...): Calculates the sample variance. Variance is the square of the standard deviation and provides another measure of data dispersion.
    • Example: =VAR.S(A1:A100)
    • Use Case: Determining the spread of data points around the mean.
  • =VAR.P(number1,[number2],...): Calculates the population variance.
    • Example: =VAR.P(A1:A100)
    • Use Case: Similar to VAR.S, but for the entire population.
  • =MEDIAN(number1,[number2],...): Returns the median of a set of numbers. The median is the middle value when the numbers are sorted.
    • Example: =MEDIAN(A1:A100)
    • Use Case: Finding the central tendency of a dataset, which is less sensitive to outliers than the mean.
  • =MODE.SNGL(number1,[number2],...): Returns the most frequently occurring number in a dataset. If there are multiple modes, it returns the first one.
    • Example: =MODE.SNGL(A1:A100)
    • Use Case: Identifying the most common value in a dataset (e.g., the most popular product, the most frequent customer age).
  • =MODE.MULT(number1,[number2],...): Returns a vertical array of the most frequently occurring numbers in a dataset. If there is only one mode, it returns that value. Requires array entry (Ctrl+Shift+Enter).
    • Example: =MODE.MULT(A1:A100) – Select a vertical range of cells before entering this formula.
    • Use Case: Identifying all modes in a dataset.
  • =PERCENTILE.INC(array,k): Returns the k-th percentile of values in a range. k is a value between 0 and 1, inclusive.
    • Example: =PERCENTILE.INC(A1:A100, 0.25) – Returns the 25th percentile (the value below which 25% of the data falls).
    • Use Case: Understanding the distribution of data, such as identifying the top 10% of performers or the bottom 25% of students.
  • =PERCENTILE.EXC(array,k): Returns the k-th percentile of values in a range, exclusive of the endpoints. k is a value between 0 and 1, exclusive.
    • Example: =PERCENTILE.EXC(A1:A100, 0.25)
    • Use Case: Similar to PERCENTILE.INC, but excludes the possibility of returning the minimum or maximum value.
  • =QUARTILE.INC(array,quart): Returns the quartile of a dataset. quart specifies which quartile to return: 0 (minimum value), 1 (1st quartile – 25th percentile), 2 (2nd quartile – median), 3 (3rd quartile – 75th percentile), or 4 (maximum value).
    • Example: =QUARTILE.INC(A1:A100, 1) – Returns the first quartile.
    • Use Case: Dividing data into four equal groups for analysis.
  • =QUARTILE.EXC(array,quart): Returns the quartile of a dataset, exclusive of the endpoints. quart values are the same as in QUARTILE.INC.
    • Example: =QUARTILE.EXC(A1:A100, 1)
    • Use Case: Similar to QUARTILE.INC, but excludes the minimum and maximum.
  • =RANK.EQ(number,ref,[order]): Returns the rank of a number in a list of numbers. If multiple values have the same rank, the top rank is returned. [order] specifies ascending (0 or omitted) or descending (non-zero) order.
    • Example: =RANK.EQ(A1, A1:A100, 0) – Returns the rank of the number in A1 within the range A1:A100, in descending order (highest number gets rank 1).
    • Use Case: Determining a value’s position within a sorted list.
  • =RANK.AVG(number,ref,[order]): Returns the rank of a number in a list of numbers. If multiple values have the same rank, the average rank is returned. [order] is the same as in RANK.EQ.
    • Example: =RANK.AVG(A1, A1:A100, 0)
    • Use Case: Similar to RANK.EQ, but handles ties by averaging the ranks.
  • =COUNTIF(range, criteria): Counts the number of cells within a range that meet a given condition. We already covered this, but it’s so useful it’s worth repeating in the context of statistical analysis.
    • Example: =COUNTIF(A1:A100, ">10") – Counts the number of cells in the range A1:A100 that contain values greater than 10.
    • Use Case: Counting occurrences of specific values or ranges.
  • =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...): Counts the number of cells within multiple ranges that meet multiple criteria.
    • Example: =COUNTIFS(A1:A100, "Apple", B1:B100, ">10") – Counts the number of rows where the value in A1:A100 is “Apple” AND the value in B1:B100 is greater than 10.
    • Use Case: Counting based on multiple conditions.
  • VIII. Financial Formulas (Basic):
  • =PMT(rate, nper, pv, [fv], [type]): Calculates the payment for a loan based on constant payments and a constant interest rate.
    • rate: Interest rate per period.
    • nper: Total number of payment periods.
    • pv: Present value (the loan amount).
    • [fv]: Future value (optional, defaults to 0).
    • [type]: When payments are due: 0 (end of period, default) or 1 (beginning of period).
    • Example: =PMT(0.05/12, 360, 200000) – Calculates the monthly payment for a $200,000 loan at 5% annual interest over 30 years (360 months).
    • Use Case: Determining loan payments.
  • =IPMT(rate, per, nper, pv, [fv], [type]): Calculates the interest payment for a specific period of a loan.
    • rate: Interest rate per period.
    • per: The period for which you want to find the interest.
    • nper: Total number of payment periods.
    • pv: Present value (the loan amount).
    • [fv]: Future value (optional, defaults to 0).
    • [type]: When payments are due: 0 (end of period, default) or 1 (beginning of period).
    • Example: =IPMT(0.05/12, 1, 360, 200000) – Calculates the interest payment for the first month of a $200,000 loan at 5% annual interest over 30 years.
    • Use Case: Analyzing loan amortization schedules.
  • =PPMT(rate, per, nper, pv, [fv], [type]): Calculates the principal payment for a specific period of a loan.
    • Arguments are the same as IPMT.
    • Example: =PPMT(0.05/12, 1, 360, 200000) – Calculates the principal payment for the first month of a $200,000 loan at 5% annual interest over 30 years.
    • Use Case: Analyzing loan amortization schedules.
  • =PV(rate, nper, pmt, [fv], [type]): Calculates the present value of an investment or loan.
    • rate: Interest rate per period.
    • nper: Total number of payment periods.
    • pmt: Payment made each period.
    • [fv]: Future value (optional, defaults to 0).
    • [type]: When payments are due: 0 (end of period, default) or 1 (beginning of period).
    • Example: =PV(0.05/12, 360, -1060.65) – Calculates the present value of a loan with monthly payments of $1060.65 at 5% annual interest over 30 years. Note the negative sign on the payment.
    • Use Case: Determining the present value of a future stream of payments.
  • =FV(rate, nper, pmt, [pv], [type]): Calculates the future value of an investment.
    • rate: Interest rate per period.
    • nper: Total number of payment periods.
    • pmt: Payment made each period.
    • [pv]: Present value (optional, defaults to 0).
    • [type]: When payments are due: 0 (end of period, default) or 1 (beginning of period).
    • Example: =FV(0.05/12, 360, -100, 0) – Calculates the future value of an investment with monthly contributions of $100 at 5% annual interest over 30 years.
    • Use Case: Projecting the future value of savings or investments.
  • =RATE(nper, pmt, pv, [fv], [type], [guess]): Calculates the interest rate per period of an annuity.
    • nper: Total number of payment periods.
    • pmt: Payment made each period.
    • pv: Present value.
    • [fv]: Future value (optional, defaults to 0).
    • [type]: When payments are due: 0 (end of period, default) or 1 (beginning of period).
    • [guess]: An initial guess for the rate (optional, defaults to 0.1).
    • Example: =RATE(360, -1060.65, 200000) – Calculates the monthly interest rate for a loan of $200,000 with monthly payments of $1060.65 over 30 years. Multiply the result by 12 to get the annual rate.
    • Use Case: Determining the interest rate of a loan or investment.
  • Important Considerations When Using Formulas (Continued):
  • Error Handling: Be aware of common error values and how to handle them:
    • #DIV/0!: Occurs when you try to divide by zero or an empty cell. Use IFERROR to handle this: =IFERROR(A1/B1, 0) (returns 0 if B1 is 0).
    • #NAME?: Occurs when Excel doesn’t recognize a name used in a formula (e.g., a misspelled function name or an undefined named range). Double-check your spelling and defined names.
    • #VALUE!: Occurs when the wrong type of argument is used in a formula (e.g., trying to add text to a number). Use ISTEXT, ISNUMBER, etc. to check data types.
    • #REF!: Occurs when a cell reference is invalid (e.g., you deleted a cell that a formula was referring to). Review your formulas and update the cell references.
    • #NUM!: Occurs when a formula has a problem with a number (e.g., trying to calculate the square root of a negative number). Check your input values.
    • #N/A: Indicates that a value is not available. This can be used intentionally to mark missing data. Use ISNA to check for this error.
    • #NULL!: Usually occurs when you specify an intersection of two areas that do not actually intersect.
  • IFERROR(value, value_if_error): A very useful function for gracefully handling errors. If value results in an error, the formula returns value_if_error.
  • Circular References: A circular reference occurs when a formula refers to itself, either directly or indirectly. This can cause Excel to recalculate endlessly or produce incorrect results. Excel usually warns you about circular references.
  • Formula Auditing Tools: Excel has built-in tools for auditing formulas, such as tracing precedents (cells that are used in the formula) and dependents (cells that use the formula). Find these under the “Formulas” tab on the ribbon.
  • IX. Date and Time Formulas (More Advanced):
  • =WORKDAY(start_date, days, [holidays]): Calculates a date that is a specified number of working days before or after a start date. It excludes weekends (Saturday and Sunday) and any dates identified as holidays.
    • start_date: The start date.
    • days: The number of working days to add or subtract (positive or negative).
    • [holidays]: An optional range of cells containing holiday dates.
    • Example: =WORKDAY(TODAY(), 5) – Calculates the date 5 working days from today. =WORKDAY(TODAY(), 5, H1:H10) – Calculates the date 5 working days from today, excluding any holidays listed in cells H1 through H10.
    • Use Case: Scheduling tasks, calculating delivery dates, etc.
  • =WORKDAY.INTL(start_date, days, [weekend], [holidays]): Similar to WORKDAY, but allows you to customize which days are considered weekends.
    • start_date: The start date.
    • days: The number of working days to add or subtract (positive or negative).
    • [weekend]: An optional code specifying the weekend days (e.g., “1” for Saturday/Sunday, “11” for Sunday only, “7” for Saturday only, or a string of 7 zeros and ones representing Monday to Sunday, where 1 represents a weekend day and 0 represents a workday, such as “0000011” for Friday and Saturday as weekend).
    • [holidays]: An optional range of cells containing holiday dates.
    • Example: =WORKDAY.INTL(TODAY(), 5, "11") – Calculates the date 5 working days from today, considering only Sunday as a weekend day.
    • Use Case: Scheduling in situations where weekends are not Saturday and Sunday.
  • =NETWORKDAYS(start_date, end_date, [holidays]): Returns the number of whole working days between two dates.
    • start_date: The start date.
    • end_date: The end date.
    • [holidays]: An optional range of cells containing holiday dates.
    • Example: =NETWORKDAYS("1/1/2025", "1/31/2025") – Calculates the number of working days between January 1, 2025, and January 31, 2025. =NETWORKDAYS("1/1/2025", "1/31/2025", H1:H10) – Excludes holidays listed in H1:H10.
    • Use Case: Calculating the duration of a project in working days.
  • =NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]): Similar to NETWORKDAYS, but allows you to customize which days are considered weekends.
    • start_date: The start date.
    • end_date: The end date.
    • [weekend]: An optional code specifying the weekend days (same codes as WORKDAY.INTL).
    • [holidays]: An optional range of cells containing holiday dates.
    • Example: =NETWORKDAYS.INTL("1/1/2025", "1/31/2025", "11") – Calculates the number of working days between January 1, 2025, and January 31, 2025, considering only Sunday as a weekend day.
    • Use Case: Calculating workdays when weekends are not standard.
  • =EDATE(start_date, months): Returns the date that is the specified number of months before or after a start date.
    • start_date: The start date.
    • months: The number of months to add or subtract (positive or negative).
    • Example: =EDATE("1/15/2025", 3) – Returns the date that is 3 months after January 15, 2025 (April 15, 2025). =EDATE("1/15/2025", -2) – Returns the date 2 months before January 15, 2025 (November 15, 2024).
    • Use Case: Calculating expiration dates, renewal dates, etc.
  • =EOMONTH(start_date, months): Returns the last day of the month that is the specified number of months before or after a start date.
    • start_date: The start date.
    • months: The number of months to add or subtract (positive or negative).
    • Example: =EOMONTH("1/15/2025", 0) – Returns the last day of January 2025 (January 31, 2025). =EOMONTH("1/15/2025", 1) – Returns the last day of February 2025 (February 28, 2025).
    • Use Case: Calculating month-end deadlines.
  • =DATEVALUE(date_text): Converts a date represented as text to a date serial number. Excel stores dates as serial numbers.
    • date_text: A date represented as text (e.g., “January 1, 2025”).
    • Example: =DATEVALUE("January 1, 2025") – Returns the serial number for January 1, 2025. You’ll need to format the cell as a date to see the date instead of the serial number.
    • Use Case: Converting text dates to a format that Excel can recognize for calculations.
  • =TIMEVALUE(time_text): Converts a time represented as text to a time serial number (a fraction of a day).
    • time_text: A time represented as text (e.g., “3:30 PM”).
    • Example: =TIMEVALUE("3:30 PM") – Returns the serial number for 3:30 PM. Format the cell as time to display correctly.
    • Use Case: Converting text times for calculations.

X. Logical Functions (More Advanced)

  • =AND(logical1, [logical2], ...): Returns TRUE if all arguments are TRUE; returns FALSE if any argument is FALSE.
    • Example: =AND(A1>0, B1<10) – Returns TRUE only if A1 is greater than 0 and B1 is less than 10.
    • Use Case: Combining multiple conditions.
  • =OR(logical1, [logical2], ...): Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.
    • Example: =OR(A1="Yes", B1="Yes") – Returns TRUE if A1 is “Yes” or B1 is “Yes” (or both).
    • Use Case: Checking if at least one of several conditions is met.
  • =NOT(logical): Reverses the value of its argument. Returns TRUE if the argument is FALSE, and FALSE if the argument is TRUE.
    • Example: =NOT(A1>10) – Returns TRUE if A1 is not greater than 10.
    • Use Case: Inverting a condition.
  • =IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...): Checks multiple conditions and returns a value corresponding to the first condition that evaluates to TRUE. Similar to a nested IF, but often easier to read.
    • Example: =IFS(A1>90, "A", A1>80, "B", A1>70, "C", TRUE, "D") – Assigns a grade based on the value in A1. The TRUE condition at the end acts as a default (like an ELSE in other programming languages).
    • Use Case: Creating complex decision-making logic. Note: This function is available in Excel 2016 and later.
  • =SWITCH(expression, value1, result1, [value2, result2], ..., [default]): Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, it returns a default value (if provided).
    • Example: =SWITCH(A1, 1, "Red", 2, "Blue", 3, "Green", "Unknown") – If A1 is 1, returns “Red”; if A1 is 2, returns “Blue”; if A1 is 3, returns “Green”; otherwise, returns “Unknown”.
    • Use Case: Choosing from a set of options based on a single value. Note: This function is available in Excel 2016 and later.
  • XI. Text Formulas (Advanced):
  • =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...): Combines the text from multiple ranges and/or strings, with a delimiter between each text item. Allows you to ignore empty cells.
    • delimiter: The character(s) to insert between the text items.
    • ignore_empty: TRUE to ignore empty cells, FALSE to include them.
    • text1, [text2], ...: The text strings or ranges to join.
    • Example: =TEXTJOIN(", ", TRUE, A1:A5) – Joins the text in cells A1 through A5, separated by commas and spaces, ignoring empty cells.
    • Use Case: Concatenating text from multiple cells in a more flexible way than CONCATENATE or the & operator. Note: This function is available in Excel 2016 and later.
  • =REPT(text, number_times): Repeats a text string a specified number of times.

Important Considerations When Using Formulas:

  • Cell References: Understand the difference between relative (A1), absolute ($A$1), and mixed ($A1, A$1) cell references. This is crucial when copying formulas.
  • Order of Operations: Excel follows the standard order of operations (PEMDAS/BODMAS). Use parentheses to control the order in which calculations are performed.
  • Error Handling: Be aware of common error values.
Tags
Share your love

Related Posts