Hello, I have a chart based on a table of information which includes some formulae returning #DIV/0. The chart also contains some results of zero but when I chart the data, the #DIV/0 results shows as zero in the chart which makes them look the same as the actual zero results.
I need the #DIV/0 results to be excluded from the chart. Is this possible?
Many Thanks
Hi milliemoo,
Is that table is pivot table?
Regards,
DILIPandey
<click on below 'star' if this helps>
DILIPandey, Excel rMVP
+919810929744 (India), +971528225509 (Dubai), [email protected]
No, it's just a table of formulae...
Ok..
You can have blank ("") in place of #DIV/0.. if you are fine with:-
=IF(ISERROR(<your formula>),"",<your formula>)
Try using above formula in your data which will convert all #DIV/0 to "" (blank)
In case of issues, post the sample file. thanks.
Regards,
DILIPandey
<click on below 'star' if this helps>
In my opinion You should get rid of errors especially div/0
Regards
tom1977
If You are satisfied with my solution click the small star icon on the left to say thanks.
So, my original formula is like this:
=AVERAGE(IF(('PIR Data'!Q:Q<>"")*('PIR Data'!T:T<>"")*('PIR Data'!T:T>=--"2012-01-02")*('PIR Data'!T:T<=--"2012-01-27")*('PIR Data'!F:F="Yes"),'PIR Data'!T:T-'PIR Data'!Q:Q))
Should it now be like this:
=If(ISERROR(AVERAGE(IF(('PIR Data'!Q:Q<>"")*('PIR Data'!T:T<>"")*('PIR Data'!T:T>=--"2012-01-02")*('PIR Data'!T:T<=--"2012-01-27")*('PIR Data'!F:F="Yes"),'PIR Data'!T:T-'PIR Data'!Q:Q))),"",AVERAGE(IF(('PIR Data'!Q:Q<>"")*('PIR Data'!T:T<>"")*('PIR Data'!T:T>=--"2012-01-02")*('PIR Data'!T:T<=--"2012-01-27")*('PIR Data'!F:F="Yes"),'PIR Data'!T:T-'PIR Data'!Q:Q)))
yes..
what is the outcome when you applied this...are you still seeing #DIV/0?
Regards,
DILIPandey
<click on below 'star' if this helps>
It works! Thank you very much.
Final final question.......how would I add in to this an extra condition that it should only calculate cells where the value in column C DOES NOT say "JPN"?
Thank you...
You are welcome milliemoo...
For adding extra condition.. do you want:-
1) to add a condition to consider only the cells in your average calculation where the column C does not say "JPN" or
2) to display the result only in the rows where Column C is not "JPN"
?
Try adding following in your existing formula, along with ('PIR Data'!Q:Q<>"")
('PIR Data'!C:c<>"JPN")
In case of any issues, post the sample file. Thanks.
Regards,
DILIPandey
<click on below 'star' if this helps>
Since you are using Excel 2007 you should be using the simpler IFERROR() approach instead of IF(ISERROR()).
Hello, I have the same case a chart based on a table of information which includes some formulae returning #DIV/0. The chart also contains some results of zero but when I chart the data, the #DIV/0 results shows as zero in the chart which makes them look the same as the actual zero results.
I need the #DIV/0 results to be excluded from the chart.
It's not a pivot table and the it's from another table, the data cell formula =Sheet8!B4, I've tried this formula but still appearing in the chart =IFERROR(Sheet8!A4,"")
Thanks in advance
Administrative Note:
Welcome to the forum.
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks