"Excel 2010 Functions: The Advantages of Using Functions in your Formulas"

Introduction to Excel 2010 Functions

A function is a built-in tool that you use in a formula. In other words, it's a predefined formula that performs a specific task.

Worksheet functions allow you to perform calculations or operations that would otherwise be impossible. A typical function (such as SUM) takes one or more arguments and then returns a result. The SUM function, for example, accepts a range argument and then returns the sum of the values in that range (see demonstrations below).

Because of this, you need to understand the advantages of using functions, and you need to know the basic structure of every function. This is what we will covers in this tutorial.

Why you need to master the Excel 2010 Functions?

You'll find functions useful because they:
1) Make simple but cumbersome formulas easier to use.
2) Perform otherwise impossible calculations
3) Speed up some editing tasks
4) Provide decision-making capability

Let see it with real-life examples.

1.) Make simple but cumbersome formulas easier to use

For example, you might need to calculate the total of the values in 10 cells (B1:B10). Without the help of any functions, you would need to construct a formula like this:

=B1+B2+B3+B4+B5+B6+B7+B8+B9+B10

No doubt that you can get the end result correct. But how about if you need to calculate the total values in cells B1 to B50? Even worse, you would need to edit this formula if you inserted a new row in the B1:B10 range and needed the new value to be included in the total.

However, you can replace the above formula with a much simpler one that uses the SUM function:

=SUM(B1:B10)

2.) Perform otherwise impossible calculations

For example, you need to determine the smallest value in a range. A formula can't tell you the answer without using a function. This formula uses the MIN function to return the smallest value in the range A1:C50:

=MIN(A1:C50)

3.) Speed up some editing tasks

Assume that you have a worksheet that contains 500 names in cells A1:A500 and that all the names appear in all-uppercase letters. You has been asked to convert all the names to the lowercase!! For example, JOHN SMITH must change to John Smith.

You can use the function such as the following, which uses the PROPER function to convert the text in cell A1 to proper case:

=PROPER(A1)

• Type this formula in cell C1 and then copy it down to the next 499 rows using drap and drop method.
• Select ranges C1:C500 and press Ctrl + C to copy the range to the Clipboard.
• Click on cell A1 and from the Home tab, in the Clipboard group, click the Paste (down arrow) button and click Paste Values to convert the formulas to values.
• Delete column C.

Bingo! You just eliminated several hours of tedious work in less than a minute.

4.) Provide decision-making capability

Suppose that you need to calculates sales commissions. If a salesperson sells at least $10,000 of product, the commission rate reaches 10 percent; otherwise, the commission rate remains at 5.0 percent.

Here you can uses the IF function to check the value in cell C1 and make the appropriate commission calculation:

=IF(C1<10000,C1*5%,C1*10%)

The formula is making a decision: If the value in cell C1 is less than 10,000, then return the value in cell C1 multiplied by 5 percent. Otherwise, return the value in cell C1 multiplied by 10 percent.

To conclude, a thorough knowledge of Excel 2010 functions is essential for anyone who wants to master the art of formulas.

Related Topics:

How to Create Formulas and Discover the 5 Types of Formulas in Excel 2010?

Knowing the different types of Function Argument in Microsoft Excel

Excel Absolute Cell Reference (Example Demonstration - Part 1)

Excel Absolute Cell Reference (Example Demonstration - Part 2)

Excel Relative Cell Reference with Real-live Example Demonstration

Can't find what you're looking for? Try Google Search!

Custom Search

Back to Top

You're viewing the Microsoft Excel 2010 functions info page, click here to go back to the Home Page

Add To Your Social Bookmarks: Blink Del.icio.us Digg
Furl Google Simpy Spurl Technorati Y! MyWeb