+ Reply to Thread
Results 1 to 6 of 6

Average Formula Excluding Empty Cells

  1. #1
    Registered User
    Join Date
    02-06-2008
    Posts
    13

    Average Formula Excluding Empty Cells

    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.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578
    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
    Please Login or Register  to view this content.
    ChemistB

  3. #3
    Registered User
    Join Date
    02-06-2008
    Posts
    13
    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
    Attached Files Attached Files
    Last edited by Alhazred; 03-19-2008 at 04:15 PM.

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578
    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
    Please Login or Register  to view this content.
    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).
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-06-2008
    Posts
    13

    Thumbs up

    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.

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578
    Thanks Alhazred,

    Glad it helped.

    ChemistB

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1