1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2007 not including cells valued 0 in charts

Discussion in 'Business Applications' started by vjmania142, Mar 20, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. vjmania142

    vjmania142 Thread Starter

    Joined:
    Mar 20, 2013
    Messages:
    13
    Hi Guys,

    First time post so please be nice :p

    I'm having an issue in Excel 2007 where I'm trying to create a chart with information including the formula:

    =IFERROR(SUM(IF($F$2:$F$66=$F72,K$2:K$66))/K162,0)

    (that was just an example, but all the data calculated uses the same formula)

    But I do not want to include any data valued 0 in the chart.

    I've tried adding IF(formula=0,#N/A,formula) into the formula as such:

    =IF((IFERROR(SUM(IF($F$2:$F$66=$F72,K$2:K$66))/K162,0))<>0,(IFERROR(SUM(IF($F$2:$F$66=$F72,K$2:K$66))/K162,0)),)

    But then this makes the value of all cells which does have a figure 0 also.

    Please can anyone help? It's driving me mad!!!

    Kind Regards,

    Vj
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,191
    First Name:
    Wayne
    can you post a dummy spreadsheet example

    you dont have a value for =0 -i assume #N/A


    which means - ?

    what issue are you seeing ?
     
  3. vjmania142

    vjmania142 Thread Starter

    Joined:
    Mar 20, 2013
    Messages:
    13
    2010-2011 2011-2012 2012-2013 2013-2014 Spelthorne 8,730 8,531 - - Wandsworth 28,797 29,080 30,592 - Hammersmith and Fulham 45,324 47,818 52,146 - Ealing 26,849 26,262 26,768 27,496 Leeds City 1,925 2,013 2,093 2,148 Liverpool City 45,945 44,898 45,800 - Hounslow 109,288 89,490 108,506 - Sandwell 7,038 7,361 7,650 - Hackney 8,901 9,310 9,783 12,717 Birmingham City Council - 27,063 28,625 - Manchester City 27,602 28,145 29,770 29,770 Tower Hamlets - 113,250 119,500 122,750 Westminster 38,480 46,550 58,199 109,984 Lambeth 21,164 23,873 25,191 -
     
  4. vjmania142

    vjmania142 Thread Starter

    Joined:
    Mar 20, 2013
    Messages:
    13
    Sorry, I just copied and pasted the table I have with the values,

    I'm guessing that's not what you meant?
     
  5. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,191
    First Name:
    Wayne
    no
    actually attach a spreadsheet
    and the full formula
     
  6. vjmania142

    vjmania142 Thread Starter

    Joined:
    Mar 20, 2013
    Messages:
    13
    I've just included the sheet that I'm working on, would take me too long to create a dummy (sorry!)

    Basically if you scroll down past the main table of data, you'll see an averages table which I'm trying to create a chart from!

    There are some rows hidden (used to calculate my average formulas)

    Have a look and let me know what you think!

    Thanks!

    <removed spreadsheet - most info public domain, but not dummy data etaf-moderator>
     
  7. vjmania142

    vjmania142 Thread Starter

    Joined:
    Mar 20, 2013
    Messages:
    13
    As I said, I need those values that equate to 0 in the small table (Averages) under the main table, to not be included in the line chart I make.
     
  8. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,191
    First Name:
    Wayne
    remember this is a public forum and any information will be available to search engines
     
  9. vjmania142

    vjmania142 Thread Starter

    Joined:
    Mar 20, 2013
    Messages:
    13
    I didn't include the rest of the spreadsheet which did include data more private. Other data in this form is easily obtainable from councils etc anyway
     
  10. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,191
    First Name:
    Wayne
    can you break the formula down - seems tobe missing factors and also brackets

    =IFERROR(SUM(

    IF($F$2:$F$66=$F72,K$2:K$66) - so no false statement

    )/K162,0)
     
  11. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,191
    First Name:
    Wayne
    rather than use the IFERROR to return a zero
    Why not change that to return a #N/A

    {=IFERROR(SUM(IF($F$2:$F$66=$F72,K$2:K$66))/K162,#N/A)}

    seems to work

    <removed spreadsheet - most info public domain, but not dummy data etaf-moderator>
     
  12. vjmania142

    vjmania142 Thread Starter

    Joined:
    Mar 20, 2013
    Messages:
    13
    Yeah that's excellent, seems to work. However when I try to ammend the formula in the original spreadsheet, why does it just return #N/A

    I really appreciate this help, I'm not massively advanced on excel (yet) still getting to grips with the more advanced bits :(
     
  13. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,191
    First Name:
    Wayne
    it must be seeing an error

    IFERROR - is the only time it will return the #N/A
    at a guess a div0 error

    if error works on these types of error The following error types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.
    and in your SUM you are then doing a /
    so is that zero ?

    also your 2nd IF does not have a value for false , as pointed out in the above formula - post #10

    breakdown the formula and post back what each bit is doing for true and false condition
     
  14. vjmania142

    vjmania142 Thread Starter

    Joined:
    Mar 20, 2013
    Messages:
    13
    Sorry, that was me just being a fool, I forgot to 'CTRL+SHIFT+ENTER' after updating the formula!

    Thanks so much for all your help, I know this was fairly straightforward!

    Shall I/Can I delete this thread now?
     
  15. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,191
    First Name:
    Wayne
    no - we marked solved ,but i can remove the spreadsheets if you want

    You can mark your own threads solved using the [​IMG] button at the top of the page of the thread in the upper left corner. :)
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1093688

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice