+ Reply to Thread
Results 1 to 5 of 5

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

Hybrid View

  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

    =if(iserror(average(a1,a2,a3)),"",average(a1,a2,a3)
    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

    =IF(ISERROR(AVERAGE(A1,A2,A3)),"",AVERAGE(A1,A2,A3))
    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