The Difference Between an Excel Function & Formula
Excel allows spreadsheet users to build functional spreadsheets based upon the user's skills. The user may want to write out a formula or the parts of an equation necessary to achieve the a specific calculation. A formula manipulates the data in the spreadsheet as directed by its parts and returns a value. A function is also a formula, but the pieces have been predefined. Instead of having to accurately type out the details of the formula, the function acts as a shortcut to carry them out for you. Commonly used functions have been set up within the confines of Excel. More intricate formulas will have to be written out, and may include the use of functions.
Other People Are Reading
-
Sum
-
For the sake of example, assume the values of 5, 6 and 7 are in cells C3, C4 and C5. To calculate a group of values in a column, you could write out the formula as =C3+C4+C5 which would return the value of 18. The function SUM does the same thing, and is entered in the spreadsheet as =SUM(C3:C5). This will return the same value.
Average
-
You can calculate an average for two or more values by using either a formula or a function. To do this with a formula, you will need to write two formulas, in two different cells. First, write the formula to add the values from the example above in cell C6. In cell C7, write the formula =C6/3. You will see a value of 6. With the AVERAGE function, you enter your information in one cell as =AVERAGE(C3:C5). This will return the same value of 6 in fewer steps and saves space on the spreadsheet.
-
If
-
Using the IF function allows you test for data values before proceeding. The IF statement allows you to use nested functions within the formula. An example might be to calculate sales bonus. IF the bonus is paid on sales valued at higher than $100.00, the IF function would test and then apply the bonus if the criteria were met. It would be written like this: =IF(C2>100,C2*1.25,C2). This IF function checks the value in the cell C2 to verify if it is larger than 100. If it is, the bonus of 25 percent is calculated. If it is not, only the original value from cell C2 is displayed.
Substitute
-
SUBSTITUTE is another way a formula can be used with a function. For this example, we will assume that Tom made $80 sales and Bob made $110. The data was entered into the spreadsheet as "Tom 80" and "Bob 110." If a total of sales were needed, these values could not be added because of the presence of the text. SUBSTITUTE allows the text to be changed for the purpose of performing arithmetic. The formula would be written as: =SUBSTITUTE(C2,"com ",)+SUBSTITUTE(C3,"com",) which would return a value of 190.
Combining If with Average
-
Multiple functions can be used within a single formula. For this example, we will take five test scores, given in percentages and average them. Then we will compare the average score to determine a letter grade. Our test scores are 99, 80, 70, 59 and 100, entered in column C beginning in cell C2. The formula will look like this: =IF(AVERAGE(C2:C6)>90,"A",(IF(AVERAGE(C2:C6)>80,"B",IF(AVERAGE(C2:C6)>70,"C",IF(AVERAGE(C2:C6)>60,"D","F"))))). The formula uses the AVERAGE function to determine the average grade in percent form. Next it uses the IF function to compare that average to the criteria for each letter grade and determines the correct grade. Our scores create an average of 81.6 or letter grade of B.
-
