PMT (Payment)

The PMT function calculates the payment for a loan based on constant payments and a constant interest rate. It takes into account the principal amount, interest rate, and number of periods to repay the loan. The formula is =PMT(rate,nper,pv). For example, =PMT(0.05/12, 5*12,-25000) calculates monthly payments for a $25,000 loan at an annual interest rate of 5% over 5 years.

FV (Future Value)

The FV function in Excel calculates the future value of an investment based on constant periodic payments and a fixed interest rate. The formula is =FV(rate, nper, pmt,[pv],[type]), where 'rate' is the interest rate per period, 'nper' is the number of periods, 'pmt' represents payment made each period; '[pv]' denotes present value or initial deposit amount; and '[type]' specifies whether payments are due at the beginning or end of each period.

PV (Present Value)

The PV function in Excel calculates the present value of an investment based on a constant interest rate. The formula is =PV(rate, nper, pmt, [fv], [type]). Rate represents the interest rate per period; Nper stands for the total number of payment periods; Pmt denotes the payment made each period and must remain constant throughout all periods. Fv is optional and refers to future value or cash balance after last payment; Type specifies whether payments are due at beginning or end of period.

NPV (Net Present Value)

The NPV function calculates the net present value of an investment based on a series of periodic cash flows and a discount rate. It is used to determine whether an investment will add value to the company or project. The formula for NPV in Excel is =NPV(rate, range_of_cash_flows). When using this function, ensure that the first cash flow occurs at time period 1.

RATE Function

The RATE function calculates the interest rate per period of an annuity. Syntax: =RATE(nper, pmt, pv, [fv], [type]). nper is the total number of payment periods; pmt represents the payment made each period; pv denotes present value or lump sum amount; fv stands for future value (optional); type specifies when payments are due (0=end of period, 1=start of period). Example: =RATE(5,-200,,1000) returns approximately -10%.

IRR (Internal Rate of Return)

The IRR function in Excel calculates the internal rate of return for a series of cash flows. It is useful for evaluating the profitability and potential returns on an investment. The syntax is =IRR(values, [guess]). Values represent the range containing cash flow values, while guess is an optional initial estimate for IRR.

MIRR (Mirrored Internal Rate of Return)

The MIRR function calculates the internal rate of return where positive and negative cash flows are financed at different rates. It takes into account both reinvestment and finance costs, providing a more accurate picture than traditional IRR calculations. The syntax is =MIRR(values, finance_rate, reinvest_rate). Values represent the range of cash flows; finance_rate denotes the cost to borrow money or interest paid on financing; reinvest_rate indicates the return from investing surplus funds.

AVERAGEIF Function

The AVERAGEIF function calculates the average of a range based on specified criteria. It takes three arguments: range, criteria, and average_range (optional). Example: =AVERAGEIF(A1:A10, \">50\"). This formula will calculate the average of values in cells A1 to A10 that are greater than 50.

COUNTIFS Function

The COUNTIFS function in Excel counts the number of cells within a range that meet multiple criteria. It allows for up to 127 range/criteria pairs. Syntax: =COUNTIFS(range1, criteria1, [range2], [criteria2]...). Example: =COUNTIFS(A1:A10,\">=50\",B1:B10,\"<100\") will count the cells where column A is greater than or equal to 50 and column B is less than 100.

MAXIFS Function

The MAXIFS function returns the maximum value among cells specified by a given set of conditions or criteria. It allows users to find the highest value in a range that meets multiple criteria, such as sales above a certain threshold and within a specific region. The syntax is =MAXIFS(max_range, criteria_range1, criterion1, [criteria_range2], [criterion2], ...). For example: =MAXIFS(B:B,A:A,\"North\",C:C,\">10000\") will return the maximum value in column B where column A equals 'North' and column C is greater than 10,000.

SUMIF Function

The SUMIF function adds the cells specified by a given condition or criterion. It takes three arguments: range, criteria, and sum_range (optional). Example: =SUMIF(A2:A10, \">100\", B2:B10) will add the values in cells B2 to B10 where their corresponding value in A2 to A10 is greater than 100.

AVERAGEIFS Function

The AVERAGEIFS function calculates the average for values in one column based on multiple criteria. It allows users to specify conditions using logical operators and compare with corresponding columns. The syntax is: =AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2], [criteria2]...). For example, =AVERAGEIFS(B:B,A:A,\"apples\",C:C,\"red\") will calculate the average of column B where column A contains 'apples' and column C contains 'red'.

MINIFS Function

The MINIFS function returns the minimum value among cells specified by a given set of conditions or criteria. It takes multiple range and criteria pairs to determine which values to consider for finding the minimum. For example, =MINIFS(B2:B10, A2:A10, \"Red\", C2:C10, \">=100\") will return the smallest number in B column where corresponding cells in A column are 'Red' and cells in C column are greater than or equal to 100.

PERCENTILE.INC

The PERCENTILE.INC function in Excel returns the k-th percentile of values in a range, where k is a value from 0 to 1. The syntax for this function is =PERCENTILE.INC(array,k), where 'array' represents the data set and 'k' denotes the desired percentile (expressed as a decimal). For example, using =PERCENTILE.INC(A1:A10,0.5) will return the median value of cells A1 through A10.

=QUARTILE.EXC(range,k)

The =QUARTILE.EXC function in Excel returns the quartile (kth percentile) for data points at any position within the dataset. The 'range' argument specifies the range of cells containing the dataset, and 'k' represents which quartile to return: 1 for first quartile, 2 for second quartile (median), and so on. This function is useful for analyzing datasets by identifying key percentiles or dividing data into quarters.

ROUND Function

The ROUND function is used to round a number to a specified number of digits. For example, =ROUND(15.678, 2) will return the value 15.68 by rounding off two decimal places. If the second argument is negative, it rounds at tens or hundreds place; for instance, =ROUND(12345,-3) returns 12000.

COS Function

The COS function returns the cosine of an angle in radians. It takes one argument, which is the angle in radians for which you want to calculate the cosine. For example, =COS(0) returns 1 because cos(0) equals 1.

SIN Function

The SIN function returns the sine of an angle in radians. It takes one argument, which is the angle given in radians. For example, =SIN(PI()/2) will return 1 because the sine of π/2 (90 degrees) is equal to 1.

SUM Function

The SUM function adds all numbers in a range or array together. It can be used with individual cell references, ranges (e.g., A1:A10), and arrays. Example: =SUM(A1:A5) will add the values in cells A1 to A5.

PRODUCT Function

The PRODUCT function multiplies all numbers given as arguments. For example, =PRODUCT(A1:A5) will multiply the values in cells A1 through A5. It ignores empty cells and text within the range.

TAN Function

The TAN function returns the tangent of an angle in radians. It takes one argument, which is the angle for which you want to calculate the tangent. For example, =TAN(0) will return 0 because tan(0) is 0.

SQRT Function

The SQRT function returns the square root of a number. It takes one argument, which is the number for which you want to calculate the square root. For example: =SQRT(16) will return 4, as it calculates the square root of 16.

POWER Function

The POWER function raises a given number to any power. It takes two arguments: the base number and the exponent. For example, =POWER(2,3) returns 8 (2 raised to the power of 3). This function is useful for calculating exponential growth or decay in financial modeling and scientific analysis.

CONCATENATE Function

The CONCATENATE function in Excel is used to combine two or more strings into one. It takes multiple arguments and joins them together. For example, =CONCATENATE(\"Hello\", \" \", \"World\") will result in the string 'Hello World'. An alternative way of achieving concatenation is by using the '&' operator: =A1&\"-\"&B1 would concatenate cell A1 with a hyphen and then B1.

LEFT Function

The LEFT function extracts a specified number of characters from the start of a text string. Syntax: =LEFT(text, num_chars). Example: =LEFT(\"Excel\", 3) returns \"Exc\" by extracting the first three characters from the word 'Excel'. The function is useful for manipulating and analyzing textual data in Excel spreadsheets.

RIGHT Function

The RIGHT function extracts a specified number of characters from the end of a text string. Its syntax is =RIGHT(text, num_chars), where 'text' is the input text and 'num_chars' specifies how many characters to extract. For example, =RIGHT('Excel',3) returns 'cel'. The RIGHT function can be useful for extracting suffixes or file extensions from data.

LEN Function

The LEN function returns the number of characters in a text string. It is useful for determining the length of cell contents, including spaces and punctuation marks. The syntax is =LEN(text). For example, =LEN(\"Hello\") will return 5 as there are five characters in the word 'Hello'. This function can be handy when working with data validation or manipulating text strings.

FIND/SEARCH Function

The FIND function locates one text within another and returns its position. Syntax: =FIND(find_text, within_text, [start_num]). Example: =FIND(\"e\", \"Excel\") will return 2 as the letter 'e' is at the second position in 'Excel'. The SEARCH function works similarly to FIND but isn't case-sensitive. It also allows wildcard characters.

MID Function

The MID function extracts characters from the middle of a text string. Its syntax is =MID(text, start_num, num_chars), where 'text' is the input text string, 'start_num' specifies the starting position for extraction, and 'num_chars' indicates how many characters to extract. For example: =MID('Excel Formulas', 7, 8) would return 'Formulas'.

& Operator

The & operator in Excel is used to concatenate multiple strings together, providing an alternative method to the CONCATENATE function. It can be utilized within formulas or directly in cells for combining text values. For example, =A1&\" \"&B1 would combine the contents of cell A1 and B1 with a space between them.

SUBSTITUTE/REPLACE

The SUBSTITUTE function replaces occurrences of a specified substring within a string with new text. Syntax: =SUBSTITUTE(text, old_text, new_text, instance_num) where 'text' is the original text or reference to cell containing it; 'old_text' is the specific substring to be replaced; 'new_text' is the replacement value; and optional parameter 'instance_num' specifies which occurrence of old_text should be replaced.

IF Function

The IF function is used to perform a logical test and return one value if the condition is true, or another value if it's false. Its syntax is =IF(logical_test, [value_if_true], [value_if_false]). For example: =IF(A1>10, 'Yes', 'No') will return 'Yes' if the value in cell A1 is greater than 10; otherwise, it will return 'No'. The IF function can be nested within other functions for more complex conditions.

NOT Function

The NOT function reverses the logical value of its argument. For example, it changes TRUE to FALSE and vice versa. The syntax is =NOT(logical). It can be used in combination with other functions like IF to perform complex logical operations.

AND Function

The AND function checks multiple conditions and returns TRUE only if all are true. It is commonly used in conjunction with other functions to create complex logical tests. The syntax for the AND function is =AND(logical1, [logical2], ...). For example, =AND(A1>10, B1<20) will return TRUE only if cell A1 contains a value greater than 10 and cell B1 contains a value less than 20.

Nested IF functions

Nested IF functions allow for more complex logic testing by using an IF function within another. This is useful when multiple conditions need to be evaluated in a specific order. The syntax involves placing one or more IF functions inside the logical_test argument of another, creating nested levels of conditional statements. However, excessive nesting can make formulas hard to read and maintain; consider using other Excel functions like SWITCH or IFS for improved readability.

IFERROR Function

The IFERROR function is used to handle errors in Excel formulas. It returns a custom result when the formula generates an error, and if there's no error, it returns the result of the formula. Syntax: =IFERROR(value, value_if_error). Example: =IFERROR(A1/B1, 'Error') will return 'Error' if A1 or B1 contains an error; otherwise, it will return the result of A1 divided by B2.

IFS Function

The IFS function tests multiple conditions and returns a value corresponding to the first true condition. It is an alternative to nested IF functions, making it easier to read and write complex logical tests in Excel formulas. The syntax of the IFS function is =IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2],...). For example: =IFS(A1>90,\"A\", A1>80,\"B\", A1>70,\"C\"). This formula checks if cell A1 contains a score greater than 90; if so, it returns \"A\" as the result.

OR Function

The OR function checks multiple conditions and returns TRUE if any of the conditions are true. It is commonly used in conjunction with IF functions to create more complex logical tests. The syntax for using the OR function is =OR(logical1, [logical2], ...). For example, =IF(OR(A1>10, B1<5), \"Pass\", \"Fail\") will return 'Pass' if either A1 is greater than 10 or B1 is less than 5.

SWITCH Function

The SWITCH function in Excel compares an expression against a list of values and returns the corresponding result for the first matching value. It is useful for handling multiple conditions without nesting IF functions. The syntax is =SWITCH(expression, value1, result1, [value2], [result2]...). For example: =SWITCH(A2," ,"Red”, 1,”Blue”, 2,”Green”,3)”} 
 
                                   " : " Red", "Color is red." : -4, "Ambiguous

AVERAGE Function

The AVERAGE function calculates the average of a range of cells. It is used to find the arithmetic mean for a set of numbers in Excel. The syntax is =AVERAGE(number1, [number2], ...). For example, =AVERAGE(A1:A10) will calculate the average value for cells A1 through A10.

COUNT

The COUNT function counts how many cells in a range contain numerical data. It does not count empty cells or cells containing non-numeric values like text or errors. Example: =COUNT(A1:A10) will count the number of numeric entries within the specified range A1 to A10.

MIN Function

The MIN function is used to find the smallest number in a set of values. It can be applied to both numerical and date/time data. The syntax for using the MIN function is =MIN(number1, [number2], ...). For example, =MIN(A1:A10) will return the smallest value from cells A1 through A10.

SUM Function

The SUM function adds up all the numbers in a specified range of cells. It is commonly used for calculating totals and subtotals.
Formula example: =SUM(A1:A10) will add up the values in cells A1 to A10.
The SUM function can also be used with multiple ranges, e.g., =SUM(A1:A5, C1:C5).
To exclude non-numeric values from the sum, use array formulas like {=SUM(IF(ISNUMBER(range),range))}.

MAX Function

The MAX function identifies the largest number in a set of values. It can be used to find the maximum value within a range or an array of numbers. The syntax is =MAX(number1, [number2], ...), where 'number1', 'number2', etc., are numeric values or cell references containing numbers. For example, =MAX(A1:A10) returns the highest value from cells A1 through A10.

ROUND function

The ROUND function in Excel is used to round off decimals to a specified place or digit. It takes two arguments: the number you want to round and the number of digits you want it rounded to. For example, =ROUND(3.14159,2) will return 3.14 as it rounds off the decimal value of pi up to two places.

IF Function

The IF function is used to perform logical tests and return one value if the test is true, and another value if the test is false. Its syntax: =IF(logical_test, [value_if_true], [value_if_false]). For example, =IF(A1>10,'Yes','No') will return 'Yes' if A1 contains a number greater than 10; otherwise it returns 'No'. Nested IF functions can be used for more complex logic. It's an essential tool for creating dynamic relationships in Excel spreadsheets.

ABS function

The ABS function returns the absolute value (positive) from any given number. It is useful for converting negative numbers to positive, or when only the magnitude of a number is needed in calculations. The syntax is =ABS(number). For example, =ABS(-10) will return 10.

TODAY()

The TODAY function returns the current date in the selected cell. It does not require any arguments and automatically updates to display the current date whenever the spreadsheet is recalculated or opened. For example, typing =TODAY() into a cell will display today's date.

YEAR(serial_number)

The YEAR function extracts the year from a given date serial number. For example, =YEAR(A2) will return the year from cell A2. It is useful for extracting specific components of dates to perform further calculations or analysis.

DATE Function

The DATE function in Excel creates a new date from the year, month, and day components. The syntax is =DATE(year,month,day). For example: =DATE(2022,12,31) returns December 31st of the year 2022. This function is useful for performing calculations involving dates or creating dynamic date relationships within spreadsheets.

EDATE Function

The EDATE function in Excel returns a date that is a specified number of months before or after the start date. The syntax for this function is =EDATE(start_date, months), where 'start_date' is the initial date and 'months' specifies how many months to move forward or backward. For example, =EDATE(A2, 3) will return a date three months ahead of the value in cell A2.

DAY(serial_number)

The DAY function extracts the day from a given serial number representing a date (1-31). For example, =DAY(A2) returns the day of the month for the date in cell A2. It can be used to extract and manipulate specific components of dates within Excel formulas.

HOUR Function

The HOUR function in Excel extracts the hour from a given serial number representing a time. The syntax is =HOUR(serial_number). For example, =HOUR(A2) returns the hour portion of the time value in cell A2. This function can be useful for analyzing and manipulating time data within spreadsheets.

MONTH(serial_number)

The MONTH function in Excel extracts the month from a given serial number representing a date, returning an integer value between 1 and 12. For example, =MONTH(A2) would return the month of the date in cell A2. This function is useful for analyzing time-based data or creating reports based on specific months within a dataset.

MINUTE Function

The MINUTE function extracts the minute from a given serial number representing time. It takes one argument, which is the serial_number (the time value). Example: =MINUTE(A2) will return the minutes portion of the time in cell A2. This function can be useful for extracting specific components of a timestamp or performing calculations based on minutes within a given timeframe.