Free Microsoft Excel 2013 Quick Reference

Make Chart ignore zero values

I'm attempting to create a template that allows me to compare fuel efficiency of two boats at specific miles per hour. The data available for each boat is the yellow highlighted areas and sorted by value of RPM. So I need a table that combines and sorts the data into MPH values then a chart that plots MPH vs GPH as per the example "Desired Result." I don't understand how to get a chart to ignore zero values. Thanks for any help.


Post your answer or comment

comments powered by Disqus
I have a pivot chart set up that reflects data across an annual period with each plot point being a month of the year - jan - december.

The problem i have is that the line chart that i have in place has an inclining slope showing the increase in the data as the year progresses. But as i only have data up until April, the line goes back down to zero for may. How can i get it to ignore the zero value that is in my data series?? I cant just delete the formula in place as then other parts of my spreadsheet wont work. Is there any way i can just have the line reflect the data that has values to it??

I can do it on a set of data where the value just reflects zero, but i think that because i have a formula in place for the data that at the moment returns a zero value that it automatically picks it up??

please somebody help!

I have created a pie chart using dyanmic ranges which changes when a user selects certain locations/years etc. The problem I'm having now is that all zero values are displayed on the chart (both on the legend and on the data labels on the pie) - is it possible to have the chart ignore these values?

I've tried having them as NA() but that doesn't work, I also can't just delete certain labels as my chart is based on dynamic ranges so I want it to update automatically.

Thanks,

Alice

Is it possible to have Excel chart a zero value cell as a blank or null cell? Similar to having Excel treat a blank cell as zero.

How do I determine the lowest non-zero value in a range?

This works if the lowest value is NOT zero:

dblMin = xlapp.Min(xlapp.Workbooks(strXlsFile).
Worksheets(sn(i)).Range(xlapp.Workbooks(strXlsFile ).
Worksheets(sn(i)).Cells(2, 4), xlapp.Workbooks(strXlsFile).
Worksheets(sn(i)).Cells(lr, lc)))
[lr = last row, lc = last column]

Is there some way to ignore zero values?

I would like to have my pivot chart supress zero value.

Since my chart link to pivot table, the chart always shows zero value.
Is it possible to have automatical supress zero value?
Thanks millions in advance,

How do I determine the lowest non-zero value in a range?

This works if the lowest value is NOT zero:

dblMin = xlapp.Min(xlapp.Workbooks(strXlsFile).
Worksheets(sn(i)).Range(xlapp.Workbooks(strXlsFile).
Worksheets(sn(i)).Cells(2, 4), xlapp.Workbooks(strXlsFile).
Worksheets(sn(i)).Cells(lr, lc)))
[lr = last row, lc = last column]

Is there some way to ignore zero values?

What is the correct formula to retrieve the Minimum value in a row of data ignoring zero values (or specified value for that matter)?

Thanks in advance.
Rob

I have a very large data sheet and I need to find the max and min of various cells in a row. These cells are not contiguous, and I need to ignore zero values. If i were to simply need a min of these cells the formula would be as follows:

=MIN(D4,G4,M4,S4,Y4,AE4,AK4,AQ4,AW4,BC4,BI4)

Because this formula needs to go down the entire 800 rows of this report I can't do it by hand.

Excel 2010.

Thanks!

How to make chart include only values from every 5th column till the end?
Manually in "values" field it looks like:

=(dati!$D$3;dati!$I$3;dati!$N$3;dati!$S$3;dati!$X$3;dati!$AC$3;dati!$AH$3;dati!$AM$3;dati!$AR$3;dati !$AW$3;dati!$BB$3;dati!$BG$3;dati!$BL$3;dati!$BQ$3;dati!$BV$3;dati!$CA$3;dati!$CF$3;dati!$CK$3)

But it takes a lot of time to put manually every 5th cell in "values" field.

I have chart with 2 Y axes, 1st presents column chart, 2nd - line chart.

Is there any other solution?

Thx in advance.

Hello

I'm trying to make a chart where, among other regular values, there's supposed to be a column or two with some IF-formulas, which returns some values or otherwise nothing.
The "nothing" bit has given me some problems, as the zero returned reads like a very low value indeed, with the chart line dropping accordingly. I've considered making it return the same value as the former cell, but that's not very pretty either. Does anyone know how to make it return something that the chart also perceives as absolutely nothing?
Appreciate any help
BCB

I am having difficulty getting an area chart, plotted on the same chart as a stacked column chart, to display as I would like (see image).

ScreenShot030.jpg

I want the area charts (blue and yellow) to ignore the zero values at point 22 and start (in mid-air!) at point 23.
I have seen posts from Jon Peltier, Andy Pope and Tushar Mehta relating to this but have been unable to apply their knowledge (such as including a secondary axis formatted as dates, with the data to be plotted rescaled to this new axis) to my situation. I think I need an extreme degree of hand-holding here ie very detailed instructions!

Hi, everyone,

I am trying to build a PIE chart from the hereunder values:
1, 3, 0, 0, 7, 0, 11

I need a way to force the chrat to ignore all the Zeros.

In a normal BAR or LINE chart I would have used the =NA() or #N/A instead teh ZEROs but in a PIE chart it seems to have no influence on the ZERO Lables.

Bottom line:
I am looking for an automatic way to eliminate displaying only the ZERO Values (Lables) and to present a PIE chart made of 3 pieces and 3 Value Lables.

Thanks, Michael

Hi

the attached sample has a chart which monitors values sourced from named ranges. This in turn is populated from links from a front end sheet.(Sheet1)

My problem is that as there are 12 refence points included in the chart output, all not yet full, the chart reads the next reference as a zero and dives off the chart.

I would like it to ignore the zero refence so the chart line stays in the scale at the last reference value.

I tried removing the "0" values in Sheet2(the chart source) by using IF="" which cleared Sheet 2 of "0"s but the the chart would then only recognise the last row in the source named range.

If you view the chart you will see how they dive off and any assistance in correcting this would be greatly appreciated.

This project content is again the result of assistance given in this forum for which we here are grateful for.

I have several charts that I look at every day. They read data from a daily cumulative file of operations data. The charts read the daily inout file and create the chart without me having to do anything because the chart looks for data in cells for every day of the month. This works fine on the last day of the month but before the last day the charts graph a value of zero for days that have not yet happened. Is there a way to tell a Line Chart to ignore zero values? I don't want to have to change the chart every day to look at one more row of data and I don't want to delete the formulas in my input files - they are complicated formulas that show a blank when there is no data yet.

I am trying to make a dynamic chart so that future zero values (months with no data yet) do not show up on the chart.

With my spreadsheet, I have the values for the chart being referenced from other cells. Having several months that have zero values (which cannot be deleted), how can I have a dynamic chart to not display the zero values?

The dynamic charts work (from my understanding) if the zero values are actually BLANK values.

Using =NA# causes the same issue and has the extra months plotted which I don't want. Is there a way to make a cell that is equal to a zero value in another cell be blank/empty?

I've been working on this for hours and cannot find a solution.

I followed the steps from this thread: http://www.excelforum.com/excel-charting/470385-charting-a-full-year-without-zeros.html

Hi.

Can anyone help me with a formula that returns average for one column of data, but ignores "0" value. I have formulas in those cells, and I don't want to delete them, but I only need the average of valid data, not the zeros.

I've tried some database functions but cannot seem to figure it out. Please help.

Thanks bunches. :o)
john

The following is the context for the problem component. As excel won't ignore zero values when it comes to graphing I run a pass through the range of values to clear the contents of any cell with a zero value(see below).

Code:
' For cells on Summary page with a value of zero clear contents of cell
    If SummaryData.Value = 0 Then SummaryData.ClearContents
In fact I got the syntax for doing it from this forum and up til now it has worked flawlessly in other applications.

Code:
Sub NewWeekData()

' NewWeekData Macro

Dim wb As Workbook
Dim wsSummary As Worksheet
Dim wsBackbone As Worksheet
Dim SummaryFormulas As Range
Dim SummaryDestination As Range
Dim BackboneOldData As Range
Dim BackboneDestination As Range
Dim SummaryData As Range

Set wb = ThisWorkbook
Set wsSummary = wb.Sheets("52-WEEK Summary")
Set wsBackbone = wb.Sheets("Backbone Items")

Set SummaryFormulas = wsSummary.Range("D100:BC180")
Set SummaryDestination = wsSummary.Range("D1")
Set BackboneOldData = wsBackbone.Range("C2:BA5")
Set BackboneDestination = wsBackbone.Range("B2")
Set SummaryData = wsSummary.Range("D2:BC81")

' Turn off screenupdating:
    Application.ScreenUpdating = False
    
' Turn off calculations
    Application.Calculation = xlManual

' Copy and paste formulas on 52-week summary page
    SummaryFormulas.Copy
    SummaryDestination.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        
' Cut and paste data 1 week to left on Backbone tab
    BackboneOldData.Copy
    BackboneDestination.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
' Remove data from last column of Backbone to make room for new week's data
    Range("BA2:BA5").ClearContents
    
' Set data on Summary page from formula to raw number
    SummaryData.Value = SummaryData.Value
    
' For cells on Summary page with a value of zero clear contents of cell
    If SummaryData.Value = 0 Then SummaryData.ClearContents
    
' Turn calculations back to automatic upon completion
    Application.Calculation = xlAutomatic
    
' Turn on screenupdating:
    Application.ScreenUpdating = True
    
End Sub


I have a very simple series, on which a line chart is based. The series
contains the following formula:

=IF(I6<>"",H26+I6,"")

Therefore the cells in the range are blank if the source cell (row I) is
empty.

However, all cells with the above formula are being plotted as a zero
value in the line chart?

Can anyone advise how to prevent this happening while retaining the
formula? I want NO value to be plotted instead of a zero.

Many thanks,

Jay

Please can someone tell me how to stop my charts plotting zero values
at the end in cases where the chart runs for a longer period than my
data?

For example, my chart total date range is say Jan 05 to Dec 05, and I
have several lines to plot, the longest one extending for the whole 12
month period, but others for shorter periods from say Jan to Jun 05,
and Feb to Nov 05 etc. At the moment the way the source data is set
up, is that for the line item that ends in June, there are zero values
in the column for Jul to Dec 05 and my chart is plotting these zero
values, however I want my line to just end in June 05 otherwise my
chart looks odd.

The easy answer might be to delete the zero values from my source data,
but that will take a lot of fiddling around if I need to do that every
time I want to update my charts.

I am sure I remember turning off zero values once before, but I cant
remember how!

Thanks in advance

Laura

Hi,
How to average a set of values but ignore zero (0) and error values (#N/A, #value!)? Been searching for a way to do this. Lots of forums on how to ignore zero value OR ignore error values but not ignore both. Appreciate the assistance.

Regards,
klchoy

I am trying to create a simple line chart. One of the series is all zero values. When I add it to the chart it doesn't show unless I have markers turned on. I don't want to have markers. I just want a line to show up and run across the horizontal axis. Is there someway to turn off this new default setting??

-thanks
-eric

Hi all,

I am banging my head off the desk trying to figure out how to get the following formula to ignore zeros when processing the SUM part. This is to take 4 values and average the smallest two in the range to return a result, but zeros do not count.

=IF(AND(H3=0;I3=0;J3=0);G3;SUM(SMALL(G3:J3;1);SMALL(G3:J3;2))/2)

Any ideas?

Many thanks

Macca

I'm trying create an area chart and I do not want to chart zero values. I've tried using na(), zero value, and null in the data table but nothing seems to work (example attached). Is there any way to not chart this field? Thanks

I've a pie chart which is populated from a table which can have some zero value series - I don't want to remove these from the legend, but I do want to remove the 0% data labels.

Is there anyway this can be done other than individually removing 0% labels each time?

Thanks in advance


No luck finding an answer? You could always try Google.