The Microsoft Excel application allows you to enter data or a formula into each spreadsheet cell. When you enter a formula, you see the result displayed. Multiple formulas in one cell are not allowed, but built-in functions and nesting can be used to express a series of calculations and logical operations in a single formula. Using standard functions, you can build complex formulas that handle a variety of conditions.
Creating an Excel Formula
Video of the Day
An Excel formula is an equation that performs a calculation and comes up with a result. To differentiate between data and a formula in a cell, you begin a formula by typing an equal sign. Within the formula, you can include numbers and text strings; arithmetic operators for addition, subtraction, multiplication and division; and references to other cells. If you type "=5+2" into a cell and press the Enter key, the cell displays the result, which is the number 7. You can see the underlying formula in the Excel formula bar when you select the cell.
Video of the Day
Combine Two Formulas in Excel With IF
Excel functions are predefined formulas for frequently used calculations. The IF function performs a logical test between two values, returning one value if the comparison is true and another if it's false. The syntax of the IF function is IF(test, value if true, value if false). For example, the formula =IF(C1>B1,C1-B1,0) compares the value in cell C1 to the value in cell B1. If the value in C1 is greater than B1, the result displayed in the cell is the difference between the values in C1 and B1. If the value in B1 is greater than C1, the displayed result is 0.
The IF function becomes even more powerful when nesting is used. The term nesting refers to using a function as an argument to another function in a formula. For example, the SUM and AVERAGE functions are nested within the IF function in this formula: =IF(SUM(A2:A6)>100,SUM(A2:A6),AVERAGE(A2:A6)), which tests whether the sum of a range of cell values is greater than 100 and returns the sum of the values if true or the average of the values if false. Excel allows function nesting up to 64 levels deep, which accommodates both simple and complex calculations within a single formula.
Display Two Results as Text in Excel
If you don't mind treating the results of your calculation as text, you can use the ampersand operator to join certain types of formulas. The ampersand operator, like the CONCAT function, allows you to combine text in Excel. For example, the formula =SUM(2,2)&" "&SUM(5,5) returns the text string "4 10". The space character between the ampersands is added to the formula so that a space appears in the result. It's important to remember that this value is text and cannot be treated as a numerical value within any other formula.