Excel 2007 Tutorial – How to use common Excel formulas?

One of the main strengths of Microsoft Excel is “Number Crunching”. It could be something as simple as creating a home budget or as complex as Statistical analysis at work. Excel can do it all as it has a host of built-in mathematical functions. Excel formulas allow you to perform calculations on data that is entered in an Excel spreadsheet. These formulas in Excel can range from logical, statistical, mathematical and financial in nature. Today we are going to look at five commonly used formulas in Microsoft Excel 2007. Here we go:

 

Excel SUM formula:

With any type of numeric calculations, a common end result is to add up all the numbers or find the total sum of numbers in a list. This is where you can use the SUM function in Excel 2007. The SUM formula is so commonly used that there’s even an Auto Sum icon right on the Home Tab ribbon under the Editing group. For our example we are going to enter the following values in Excel:

Cell B3 = 150
Cell B4 = 40

In Cell B6 of Excel, enter the following formula

=SUM(B3:B4)

If you did this right, you should get 190. You can download the Excel 2007 Workbook with all these examples of common formulas (different worksheets) from this location.

http://www.learningcomputer.com/training-blog/excel/excel-formulas-blog.xlsx

We have included a screen shot of this right below

Excel-sum-formula

 

If you would like more information on Microsoft Excel, please visit our Excel 2010 tutorial page.

 

Excel AVERAGE formula:

Another commonly used numeric calculation in Excel is trying to find the average or mean for a range of numbers. This in essence will add up all the numbers and then divide by the total count of numbers. For example, let’s say we have the following numbers:

20, 35, 11, 15, 6

Their Average should be (20 + 35 + 11 + 15 + 6)/5 = 17.4

Go ahead and enter the above numbers in column A from A3 through A7. Next enter the following formula in cell A9. You should see the same Average result as above

=AVERAGE(A3:A7)

 

Difference between two dates with DATEDIF function in Excel 2007:

If you have a MS Excel 2007 workbook with Customer order information, it may be necessary to calculate the difference between two dates. For example, we may have a column named OrderDate and another one named ShippedDate. What if we wanted to know the difference between the two dates?

We can do that by using DATEDIF function in Excel. We are going to enter the following information.

Cell B8 (OrderDate) = 8/15/2011
Cell C8 (ShippedDate) = 9/1/2011

Next you can enter the Excel formula DATEDIF in Cell Date

=DATEDIF(B8,C8,”d”)

The above formula should give you an answer of 17 days. Here is a screen capture of what it looks like on our computer monitor.

 

Excel-datedif-formula

 

Once again you can download the Excel Workbook with all these examples from this location.

http://www.learningcomputer.com/training-blog/excel/excel-formulas-blog.xlsx

For more information on the DATEDIF function, please visit the following site

http://www.meadinkent.co.uk/xl_birthday.htm


Excel Formulas for Minimum and Maximum values:

What if you are trying to find minimum and maximum values among a range using Microsoft Excel? No Problem! We can certainly do that with Excel 2007. Let’s go ahead and use the numbers that we already used with AVERAGE formula earlier. In the same Excel workbook, insert a new worksheet. If you do not know how to do that, you can simply do Shift + F11 to insert a new worksheet. Enter the same numbers again as before: 20, 35, 11, 15, and 6 in cells A2 through A6. Next enter the respective formulas in following:

Cell C3 = MIN(A2:A6) should give you 6
Cell D3 =MAX(A2:A6) should give you 35

 

COUNT function in MS Excel 2007:

The last common Excel formula we’re going to look at is the COUNT function. Let’s say you have a customer list and you are trying to count the number of customers that you have. This is an easy task if you only have a handful of customers but what if you had thousands of customers. You can use the Excel formula COUNT which counts the total numeric values to come up with this customer count. Here is an example of how to achieve this objective.

Go to the COUNT tab in the following Excel workbook.

http://www.learningcomputer.com/training-blog/excel/excel-formulas-blog.xlsx

Scroll all the way down to Cell A105 and enter the following Excel formula.

=COUNT(A1:A101)

This will give you a count of 100 customers

 

LearningComputer.com is an elearning company with training on products like Microsoft Windows, Microsoft Office, Mozilla Firefox, Internet Explorer, Visual Basic, Java, SQL Server, Internet Marketing, SEO and many more topics on IT training and computer learning.

 

 

One Response

  1. Buffie Says:

    Ho ho, who wlouda thunk it, right?