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.

- Pivot chart - Ignore zero values - help needed
- Ignoring zero values on a pie chart
- Chart a zero value cell as blank/null?
- Application.Min - how to ignore zero value?
- Pivot chart suppress zero value
- Application.Min - how to ignore zero value?
- MIN value ignoring zero values
- Ignoring zero values in min and max functions - non contiguous cells
- How to make chart include only values from every 5th column?
- How to make chart ignore "zero"/FALSE values
- Ignore zero value at beginning of area chart ie begin plot further along axis
- Ignore ZERO values in PIE Chart
- Chart to ignore zero values - can it be done
- How to do chart without showing zero values
- Dynamic Chart with Zero values
- AVERAGE fx ignoring zero value (not blanks)
- Problem with code designed to remove zero values from a range
- Formula'd cells charting as zero values
- Turn off Zero values at the end of the chart
- How to average a set of values but ignore zero and error values (#N/A)?
- 2010 Line Chart Show Zero Value Line
- Unable to ignore zero values in SUM formula
- Don't chart na/zero value in area chart
- Pie Chart - Remove Zero Value Labels

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'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

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?

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,

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?

Thanks in advance.

Rob

=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!

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.

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

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!

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

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.

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

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

Code:

' For cells on Summary page with a value of zero clear contents of cell If SummaryData.Value = 0 Then SummaryData.ClearContentsIn 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

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

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

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

-thanks

-eric

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

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

Thanks in advance