Excel Formula: Everything you need to know about formulas in Excel

A formula in Excel is a series of instructions that begins with an equal sign (=) and that perform calculations or other actions on the data in your worksheet. A formula always begins with an equal sign (=), which tells Excel that you want to perform a calculation. Before typing a formula, you should always first select the cell where you want to enter the formula. For example, a simple formula might be =5+3, which calculates the sum of these two numbers. But a formula often contains multiple parts, such as functions, operators, and cell references, allowing formulas to be much more complex. Excel uses a reference style called A1, which identifies columns with letters (such as column A and column B) and rows with numbers. An absolute reference in a formula remains constant no matter where the formula is copied.

A formula can contain several parts:

• Functions: Predefined calculations such as SUM(), AVERAGE(), PMT(), etc. Functions in Excel are predefined formulas that perform specific calculations and often require multiple arguments. Some functions may require a specified base (such as logarithmic calculations), generate a random number, or return a specified value for error handling. Database functions work with specified criteria. Functions such as INDEX allow you to specify a specific row.
  Cell references: References to specific cells, such as A1, B2.
• Operators: Symbols that indicate the type of calculation to be performed, such as +, -, *, / (these are also called arithmetic operators). Some operators operate on two values.
• Constants: Fixed values ​​such as numbers or text that are used directly in the formula. Constants are fixed values ​​that cannot be calculated and always remain the same in a formula. Constants can consist of specified numbers, decimals, or a decimal number. For numeric constants, you can sometimes set a specified number of decimal places.

In addition, functions often accept multiple arguments, including logical values ​​(such as TRUE or FALSE) and a logical value as input. Array formulas can also contain array constants containing decimal numbers. Text functions allow you to extract the rightmost characters or phonetic symbols from text.

An important concept when working with formulas in Excel is relative references and absolute references.

• Relative reference: A reference that changes when you copy the formula to another cell. For example, if a formula in cell C1 refers to A1 and you copy it to C2, the reference automatically changes to A2. This is because the reference is based on the relative position to the formula cell. Excel uses relative references by default, meaning the reference changes when the formula is copied to other cells.
• Absolute reference: A reference that always points to a fixed cell, regardless of where you copy the formula. This is indicated by dollar signs, for example, $A$1. If you copy a formula with an absolute reference, the reference remains the same.
• Mixed reference: A mixed reference has one column or row absolute, while the other is relative. This type of reference offers flexibility when copying formulas.

Using formulas in Excel is essential for performing calculations, analyzing data, and automating tasks. Excel contains hundreds of functions that allow you to perform all kinds of calculations. In total, there are approximately 475 functions available, ranging from simple to very advanced applications. Below are some key examples:

• SUM(): Adds the values ​​in the cells, for example, =SUM(A1:A10). The SUM function adds all the numbers in a specified range. To calculate a conditional sum, you can use the SUMIFS function, which adds only the values ​​that meet certain criteria.
• AVERAGE(): Calculates the average of a set of numbers. You can also calculate a conditional average using functions like AVERAGEIF. Additionally, you can combine or nest different average functions for more complex calculations.
• IF(): Performs a logical test and returns a value depending on the result.
• VLOOKUP(): Looks up a value in the leftmost column of a table and returns a corresponding value. The VLOOKUP function looks up a value in the first column of a table and returns a value from the same row in a specified column, making it easy to retrieve corresponding values ​​from another column.
• PMT(): Calculates periodic payments, for example, for a discounted security whose yield you want to determine. You can also use the nominal annual interest rate.
• CUMULATIVE LOGARITHMIC NORMAL DISTRIBUTION: Advanced statistical function for distributions. In addition, Excel also supports the cumulative normal distribution and the standard cumulative normal distribution for probability calculations. For statistical analysis, there are functions for the binomial distribution, which allows you to calculate the probability that a value is less than a certain threshold (binomial distribution smaller), and the exponential distribution. The MODE function allows you to determine the most frequently occurring value in a data set.
• NOW() and TODAY(): Return the current date and time, and the date alone, respectively. The NOW function returns the current date and time based on the computer’s system clock. The TODAY function returns the current date without including the time.
• X.LOOKUP: A more modern and flexible search function than VLOOKUP.

For regression analysis, you can determine the linear regression line, which best describes the straight line between two variables. In financial functions, straight-line depreciation is a commonly used method for depreciating assets. Rounding functions in Excel allow you to round numbers to a desired multiple, the nearest significant multiple, or the nearest integer. Keep in mind that a number becomes negative if it falls below zero. When working with percentages, values ​​are often expressed as percentages.

For complex numbers, you can use a complex conjugate number, the real coefficient, the imaginary coefficient, and imaginary coefficients. For bonds or loans, you can calculate the weighted average life. Financial schedules do not necessarily have to be periodic. The precedence of operators in Excel is shown in the following table.

Operators determine which operation Excel performs in a formula. The most important arithmetic operators are:

• (add)
• (subtract)
• (multiply)
• / (divide)
• ^ (raise to the power)

The priority with which these operators are executed is shown in the following table. It shows the order in which Excel evaluates the various operators within formulas.

In addition, there are also comparison operators such as =, >, <, >=, <=, and < >, which are often used in logical tests.

You can combine formulas by nesting functions. For example, you can nest average functions, such as AVERAGE and SUM, within other functions. This means you can first calculate a sum and then use that as an argument in an average. Functions in Excel can be nested, where one function is used as an argument in another function. This allows you to create very complex formulas that perform multiple steps simultaneously. Often, a complex formula contains multiple parts, such as functions, operators, and references. When using nested functions, the nested function must return the correct value type for the argument of the main function. A formula can contain up to seven levels of nested functions. If a nested function does not return the correct value type, it can result in a #VALUE! error in Excel.

• Begin each formula with an equal sign (=).
• Use the Formula Auto function to find and enter functions more quickly.
• Be aware of the difference between relative and absolute cell references to avoid errors.
• Use parentheses to determine the order of calculations. You can change the order of evaluation in Excel by using parentheses in formulas.
• Regularly check formulas for errors, especially when working with complex calculations.
• When entering a function in Excel, use the Insert Function dialog box to enter the necessary arguments.

• Begin elke formule met een gelijkteken (=).
• Gebruik de formule automatisch-functie om sneller functies te vinden en in te voeren.
• Wees bewust van het verschil tussen relatieve celverwijzing en absolute verwijzing om fouten te voorkomen.
• Gebruik haakjes om de volgorde van berekeningen te bepalen. U kunt de evaluatievolgorde in Excel wijzigen door het gebruik van haakjes in formules.
• Controleer formules regelmatig op fouten, vooral bij het werken met complexe berekeningen.
• Bij het invoeren van een functie in Excel, gebruikt u het dialoogvenster ‘Functie invoegen’ om de benodigde argumenten in te vullen.

Using formulas is essential in Excel for a wide range of applications, from simple calculations to advanced data analysis.

• Financial calculations such as calculating the effective annual interest rate, net present value, or internal rate of return.
• Data analysis with functions for averages, sums, and statistical distributions. For example, you can generate a random number with functions like RAND or RANDBETWEEN.
• For numerical applications, Excel allows you to work with decimal numbers and a decimal number, for example, when converting numbers between different number systems.
• Text manipulation with functions like LEFT(), RIGHT(), and MID() to extract a specified number of characters from text. You can also use these to extract the rightmost characters from text or retrieve phonetic characters for linguistic applications.
• With error handling, it’s important to be aware that a formula sometimes produces an error; functions like IFERROR() can help you catch and manage these errors.
• For simple calculations, you can also use simple formulas, such as addition or subtraction.
• Automate reporting and create dynamic dashboards.
• Excel offers the UPPERCASE function to capitalize the first letter of each word in a string.

When working with formulas in Excel, you may regularly encounter errors or unexpected results. This is often the result of incorrect input, incorrect cell references, or the use of inappropriate arguments in a formula. Below is an overview of the most common formula errors in Excel and tips for resolving them:

• #VALUE!: This error occurs when a formula encounters an inappropriate value, for example, if you are trying to calculate with text instead of numeric values. Check that all cells included in the formula contain the correct data.
• #DIV/0!: This means you are trying to divide by zero. Check that the divisor in your formula is not a zero or an empty cell.
• #NAME?: This error usually occurs when Excel doesn’t recognize a formula or function, for example, due to a typo in the function name or missing quotation marks around text. Check the spelling of your functions and arguments.
• #REF!: This error indicates that a cell reference has become invalid, for example, because a cell to which the formula refers has been deleted. Check that all cell references are still correct.
• #NUMBER!: This occurs when a formula returns an invalid numeric value, for example, when calculating the positive square root of a negative number.
• #N/A: This error means that a value is not available, for example if a search function cannot find a match.

To prevent errors in Excel formulas, it’s wise to regularly check your formulas and use the built-in error checking. Pay close attention to the use of relative and absolute references so your formulas continue to work correctly when copied to other cells. Also, make sure you only work with non-empty cells and that all necessary arguments are entered in your formulas. By working carefully and checking regularly, you can easily resolve most formula problems in Excel.

Formulas in Excel are the key to efficiently processing and analyzing data. By understanding how to create formulas, which functions are available, and how to use references correctly, you can save a lot of time and prevent errors. Excel formulas allow users to automate repetitive calculations. Whether you want to perform simple calculations or advanced analyses, Excel formulas are a powerful tool at your fingertips.

Want to learn more about Excel formulas? Start practicing, use the built-in functions, and discover the versatility of Microsoft Excel!