# Solved: Excel 2007 not including cells valued 0 in charts

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

Not open for further replies.

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

First time post so please be nice

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.

Kind Regards,

Vj

2. ### etafModerator

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 ?

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 -

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. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,191
First Name:
Wayne
no
and the full formula

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>

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. ### etafModerator

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

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. ### etafModerator

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. ### etafModerator

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>

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. ### etafModerator

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

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. ### etafModerator

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 button at the top of the page of the thread in the upper left corner.

As Seen On