Another extremely useful Excel function is the AVERAGE function. As I’m sure you can guess, this function will return the average of the values in the cells that you pass into it.

Here is the example I’ll use:

Using this example, here’s the usage for this function:

• AVERAGE(A1,A2) - This will return the average of the values in cells A1 and A2. The returned value would be 13.5.

• AVERAGE(A1,A2,5) - This will return the average of the values in cells A1 and A2, and the number “5″. The returned value would be approximately 10.7.

• AVERAGE(A1:A4) - This will return the average of the values in the range of cells between and including A1 and A4. The returned value would be 12. You’ll see that there is no value in cell A3. That’s ok, because the AVERAGE function will ignore this and just calculate the average based on the populated cells. This is also true any time that a cell in a given range would have a non-numeric value, such as text.

• AVERAGE(A1:A4,5) - This will return the average of the values in the range of cells between and including A1 and A4, and the number “5″. The returned value would be 10.25.

I find the AVERAGE function is nice to use when you don’t know exactly how many cells in a range you’ll want to average (as in the case of possible empty cells, etc.). It’s also convenient when you don’t want to type long formulas with a ton of cell references, and then manually figure out the denominator. I use it on a pretty regular basis, and it saves me a good amount of time and cuts down on the mistakes that manual calculations can cause.

Updates below:

• Guys, see today’s Tip of the Day for a helpful tip on how long to make your tie.
• The latest article on Microsoft Excel deals with an underused but very helpful function, COUNTA.

While this is a basic Excel function, I haven’t come across a lot of people who know about it. It’s a shame, because it’s very useful. Basically, the COUNTA function will return the number of non-blank cells in a list or array that you pass into it.

Let’s use the following sample data:

Using the above data, here’s the usage for this function:

• COUNTA(A1,A2) - This will return the count of the non-blank cells when looking at A1 and A2. In the above example, this would return two.

• COUNTA(A1:A3) - This will return the count of all the non-blank cells between and including A1 and A3. In the above example, this would return two.

• COUNTA(A1:A5,”Seven”) - This will return the count of all the non-blank cells between and including A1 and A5, and then will add one to this count for the value “Seven”. In the above example, this would return five. You can also use this syntax with specific cells instead of a range, as in the case of COUNTA(A1,A5,”Seven”).

• COUNTA(A1:A3,2) - This is very similar to the example usage above. It will return the count of all the non-blank cells between and including A1 and A3, and then will add one to this count for the value “2″. In the above example, this would return three. You can also use this syntax with specific cells instead of a range, as in the case of COUNTA(A1,A2,2).

I’ve found COUNTA to be a very useful function, especially when you have a lot of data where not all the cells are necessarily populated, and you only care about how many populated ones there are. For example, if you are calculating the average score for each question on a survey, but only want to divide by the number of surveys where this question was answered, then you would use this function to get the number to divide by.

This is the second article in what has become my “Basic Excel Functions” series, where I provide a quick how-to on how to use some simple but useful functions in Excel.

For this article, I’ll be taking a look at the MIN and MAX functions. They can be defined as the following:

• MIN - returns the minimum value in the range of cells you specify.
• MAX - returns the maximum value in the range of cells you specify.

Usage: MIN

Here are a couple of ways to use this function:

• MIN(cell1,cell2,…) - This will return the minimum value between cell1, cell2, and any other cell you specify.
• MIN(cell1:cell4) - This will return the minimum value of the cells between cell1 and cell4, including cell1 and cell4.
• MIN(cell1,cell2,0), MIN(cell1:cell4,0) - This works like the two examples above, however it also includes the number 0 as a value for comparison as well.

Usage: MAX

The usage for this function is pretty much the same as the MIN function, so I won’t go into details for each usage. Here’s a quick usage list for this function:

• MAX(cell1,cell2,..)
• MAX(cell1:cell4)
• MAX(cell1,cell2,15), MAX(cell1:cell4,15)

That’s it for these functions, it really is just that simple to use them. Here’s a link to the previous article in this series on using the SUM function.

Ah, the wonders of modern technology. Went to see the doctor, she prescribed me some fantastic medication, now I’m on my way to full recovery. In celebration, I have written two new articles, see below:

• Since I haven’t published a Humor article in a while, I thought that it would be a good idea. The result is a personally embarrassing story, see it here: Tales of the Metro: Watch the Feet!.
• See Basic Excel Functions - SUM, for a quick how-to on using one of the most commonly used Microsoft Excel functions, SUM.

This is the first article I will be writing on some basic Microsoft Excel functions that are commonly used. This article will highlight how to use the SUM function.

The SUM function is pretty basic: It will add up whichever numbers and/or range of numbers that you specify, and output that result to a cell where the formula is contained. This is probably the most commonly used formula in Excel, so it is a good idea to know how to use it.

There are a couple of ways that you can insert this function into a cell. I have listed them below:

1. Type the function directly into the cell.
2. Use the Insert Function shortcut, which opens up the Function Wizard.
   
3. Use the Function Wizard via Insert -> Function….
   

For this article, I will be explaining the first method, that of typing the formula directly into the cell. I think that this is easier than using the Function Wizard for the SUM function. You should play around with the wizard to see if that is the case for you.

To begin, when entering a formula into a cell, you need to preface the formula with the “=” sign. Otherwise, Excel will interpret the formula as text instead of a formula.

For the SUM formula, the basic structure looks like this:

=SUM(number1, number2,…)

In this case, you could enter actual numbers for “number1, number2″ or cell references. Here are some examples:

• =SUM(1,2) - This would add “1″ and “2″ and come up with a total of “3″. It should also be noted that if you put quotes around a number in the formula (like it was text), the number will still be interpreted as a number.
• =SUM(A1,A2) - This would add the value in cell A1 to the value in cell A2 to come up with the total.

Alternatively, you can sum a range of cells. For example:

• =SUM(A1:A3) - This would add the values in cells A1 through A3 to come up with the total.

Lastly, you can combine these two methods. This is shown below:

• =SUM(A1:A3,A2,1) - This would add the values of cells A1 through A3 to the value in cell A2, as well as add the number “1″ in order to get the total.

This is a simple overview of how to use the SUM function, mainly because it is a very easy function to use. I would say that I use this function in about 90% of the spreadsheets I create, so I think it’s well worth it to take the time to learn how to use implement it with ease.