+ Reply to Thread
Results 1 to 3 of 3

cell displaying '#DIV/0!' when no data enetered- how to make this clear

  1. 04-26-2015, 06:56 PM #1
    kevwilsondj
    kevwilsondj is offline
    Forum Contributor
    Join Date
    11-09-2014
    Location
    Lauder, Scotland
    MS-Off Ver
    Windows 8.1
    Posts
    160

    cell displaying '#DIV/0!' when no data enetered- how to make this clear

    Hi,

    I have a formula that averages out data within some cells but when no data is entered then it displays '#DIV/0!

    How can I make it so that this cell will remain clear when no data added? (please see sheet titled 'Big Writing'

    Thanks
    Attached Files Attached Files
    Last edited by kevwilsondj; 04-26-2015 at 07:21 PM.
    Reply With QuoteRegister To Reply
  2. 04-26-2015, 07:02 PM #2
    kevwilsondj
    kevwilsondj is offline
    Forum Contributor
    Join Date
    11-09-2014
    Location
    Lauder, Scotland
    MS-Off Ver
    Windows 8.1
    Posts
    160

    Re: cell displaying '#DIV/0!' when no data enetered- how to make this clear

    sorry- just found out that i need to add IFERROR prior to my formula.
    Reply With QuoteRegister To Reply
  3. 04-26-2015, 07:04 PM #3
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: cell displaying '#DIV/0!' when no data enetered- how to make this clear

    I thought Tony gave you a suggestion for this on your other thread? (unless Im getting my threads mixed up again lol)

    Anyway, try this...
    =IFERROR(INDEX(Sheet1!$A$2:$A$22,MATCH(ROUND(SUMPRODUCT(--($E4:$G4=Sheet1!$A$2:$A$22)*(Sheet1!$B$2:$B$22))/COUNTA($E4:$G4),0),Sheet1!$B$2:$B$22,0)),"")

    edit: ok great, thought I wasnt losing it
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] how to make cells clear if no data added
    By kevwilsondj in forum Excel General
    Replies: 11
    Last Post: 04-25-2015, 11:23 AM
  2. VBA help..Need a code to input data & have it clear but save to another cell (make sense?)
    By Jasmine Dee in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-03-2014, 05:33 PM
  3. a macro to clear a range of data based on cell result - i.e. if A1 = A2, clear C1: C10
    By Capolan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-21-2013, 04:05 AM
  4. Using ActiveX CheckBox Control to make a cell change colour and clear values
    By drmit in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-20-2012, 04:18 AM
  5. How to make buttons to select certain cells and then click on a button to clear data?
    By ohlalayeah in forum Excel General
    Replies: 3
    Last Post: 06-05-2012, 04:36 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1