Average Formula Excluding Empty Cells

**Alhazred** · 03-19-2008, 02:32 PM

Hello, I have the formula:

=AVERAGE('TEAM Summary'!D7,'TEAM Summary'!M7,'TEAM Summary'!D17,'TEAM Summary'!M17,'TEAM Summary'!D27)

Which does a great job of taking the Averages from several team work sheets & giving me the overall average. However recently I've been getting #DIV/0 Errors because some of the Team Cells IE D7, M7, D17 etc, contain no values.

Is there a way I can modify the formula to include only numerical values, thus eliminating the errors?

Thank you in advance.

**ChemistB** · 03-19-2008, 03:15 PM

You will get the #DIV/0 error if you have no numbers, not if some of your values are blank or text. This probably means that values you see as numbers are formatted as text. (See Example 2 in my attachment.) If this is not the case, an uploaded zipped example might help us.

To force Excel to see the numbers as text as numbers, use the VALUE function

=AVERAGE(VALUE('TEAM Summary'!D7),VALUE('TEAM Summary'!M7),VALUE('TEAM Summary'!D17),VALUE('TEAM Summary'!M17,'TEAM Summary'!D27))

ChemistB

**Alhazred** · 03-19-2008, 04:10 PM

Surething, Chemist I hope this helps.

I apologize for any references to 'Sandy' you may see, that's the name of the original workbook. (I took an example directly from the book, which is over 190 sheets long)

As I mentioned I'm looking to get the Overall averages in specific areas calculated from values in the Team Summary Sheet. For example in Cell D6 on the Floor Summary Sheet I need the Overall Average for JMS Long distance as calculated from averaging cells D7, M7, D17, M17, & D27 on the Team Summary sheet.

The problem I'm having are these values are populated slowly over the month, & from values entered in the different Team sheets. As such, more often then not, there is no value in most of these cells. Hopefully there is a way I can ignore any non numerical values & keep a running average as they are entered. As you may note, I'll use the same formula to complete each Monitoring Type cell, & the Overall Avg, etc., so this is a real stumbling block.

Thx again for your help previously. I used the suggestions you made earlier on this workbook & they really saved me alot of time. I really appreciate it.

Oh & I tried the VALUE idea, & no go, I still get the #DIV/0 Error

**ChemistB** · 03-19-2008, 04:36 PM

Ahhh, the problem is not in your formula but in the cells you're referring to. You're trying to take the average of #Div/0!. The average of an error is an error. We need to fix the Team Summary page. For all of those cells, you'll need to include an IF statement to catch errors (such as #DIV/0). Since we are looking for the value in each of those cells to be a number, I used

old formula =I7
new formula =IF(ISNUMBER(I7),I7,"")

I fixed all the formulas on the Team Summary Tab that are internal to the workbook. Since I don't have the linked workbook, I didn't mess with those formulas but hopefully from seeing what I did you can modify some of the other formulas (if they need modifying).

**Alhazred** · 03-21-2008, 08:20 AM

Again Chemist thank you, it worked like a charm. I changed the Team Sheets to eliminate the errors, & the Overall summary took care of itself. Really appreciate the assistance, & I hope you have a nice Easter.