Calculating the average, depending on the number of filled-in cells:

At the request of Hilde, Gert, Luc and Stef, I will explain how to calculate the average, depending on the number of filled cells.

In order to calculate the average number of non-empty cells, we have two options.

I will start with the most “understandable” one.
We calculate the sum of the “range” and divide this by the number of filled-in cells. This should give us the average. Right?

So we use two functions in the formula.
They are the SUM function and the COUNT function.
The SUM function calculates the sum of the entire range.
The COUNT function counts the number of cells that contain numbers, in the range.
That’s important: We need only “the number of cells that contain numbers”. We don’t need those with alphabets.

This is only for informational purposes.
If you want to calculate the number of non-empty cells, use the COUNTA function.
This counts all cells that are not empty, which includes those with alphabets.
But, those cells are not applicable here.
We only need to have the number of cells, which contain numbers.

So, we type the formula = SUM (B1: B12) / COUNT (B1: B12).
The meaning of this is: the sum of the range B1 to B12 is divided by the number of numbers in the range B1 to B12.
Has everyone followed this?

Of course, this is easy.

The more experienced Excel user, however, uses an array formula.
In this example, it is not really necessary, but in the future when you want to use complicated formulas, you will still need to learn how this works.
In Tip 19, Valentin had already given an example of a complicated array formula so, I assume, this is not a problem for you anymore.

In order to calculate the average of the cells containing numbers, by means of a array formula, we use the following formula:
= AVERAGE (IF (B1: B12 <> 0, B1: B12, “”))

I explain it here:
IF (B1: B12 <> 0 )
In every cell in the range B2: I2, Excel checks whether the value is non-zero.
If this is true, then that value is taken into consideration.

B1: B12; “”
If the value is 0 then Excel replaces it with “”.

The result of the IF function yields the following matrix:
1245, 1135, 975, 1485, 1395, 1265, “”, “”, 1365, 1025, 1450; 1950
Thus:
January, February, March, April, May, June, July, August, September, October, November, December
or:-
1245, 1135, 975, 1485, 1395, 1265, nothing, nothing, 1365, 1025, 1450; 1950

Considering the array formula, as a whole:
1245 +1135 +975 +1485 +1395 +1265 +1365 +1025 +1450 +1950 divided by the number of numbers.
In this case it is 10.
This is because July and August have no value.

Very important!
An array formula is always closed by pressing Ctrl + Shift + Enter on your keyboard.
This places this formula in braces, automatically{}

If you do not do it this way and, simply press the Enter key, you will get the following error message:

So, when you see this, you will know what you mistake you have made.
You do not have to start again, though.
Just place the cursor back at the end of the formula in the formula bar and press Ctrl + Shift + Enter.

FYI: Swotster.com thinks of its students, so you also please remember swotster.com

Copyright © 2013 – Swotster Ltd.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats