+ Reply to Thread
Results 1 to 5 of 5

Avoiding a #DIV/0! result when using AVERAGE on empty cells

  1. #1
    Registered User
    Join Date
    09-27-2008
    Location
    Canada
    Posts
    9

    Avoiding a #DIV/0! result when using AVERAGE on empty cells

    Hello,

    I have a spreadsheet in which I am averaging the values in three cells. I get a valid result as long there is a value entered in at least one of the cells that I am referencing. However there will be times when there are no values entered in those three cells, and when that is the case, a "#DIV/0!" shows up in the cell that would normally display the average. How can I keep this message from showing up if all three cells are empty.

    For example, in cell B1 I have entered:
    =AVERAGE(A1,A2,A3)
    As long is there is a number in A1,A2, or A3 I get the proper result, but if A1,A2 and A3 are all empty, "#DIV/0!" shows up in cell B1.

    How can I avoid this?

    Thanks
    Randy

  2. #2
    Valued Forum Contributor Macdave_19's Avatar
    Join Date
    03-14-2007
    Location
    Birmingham, England
    MS-Off Ver
    12.0
    Posts
    808

    Talking try

    Please Login or Register  to view this content.
    Mr MaGoo
    Magoo.Inc MMVII

    If i've helped please add to my Rep by Clicking on the Blue Scales in the top right hand corner of the post

  3. #3
    Forum Contributor EdMac's Avatar
    Join Date
    01-23-2006
    Location
    Exeter, UK
    MS-Off Ver
    2003
    Posts
    1,264
    try this

    Please Login or Register  to view this content.
    this traps the error
    Ed
    _____________________________
    Always learning, but never enough!
    _____________________________

  4. #4
    Registered User
    Join Date
    09-27-2008
    Location
    Canada
    Posts
    9
    That solved it, Thanks!

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689
    Or =IF(COUNT(A1:A3), AVERAGE(A1:A3), "")
    Entia non sunt multiplicanda sine necessitate

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Moving average excluding blank cells
    By bog3494 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-28-2011, 07:18 PM
  2. "Translating" numbers into words
    By Portuga in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-14-2008, 11:44 AM
  3. Spell number
    By nowfal in forum Excel General
    Replies: 4
    Last Post: 08-20-2007, 04:21 PM
  4. searching merged cells
    By chris_mayer in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-30-2006, 11:14 AM
  5. searching for empty cells in a range
    By chris_mayer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-28-2006, 07:23 AM

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