Get rid of zeros in formula (Excel xp)

Topic

New Reply

I need to make several charts that i want to link to the source like in the attached file.

I want to put a formula in the source range so the zeros don’t become part of the bar graph. How do i add to my formulas?

Thank you for the help.

Reply | Quote

Replies

You could use autofilter to do this!
Insert a new row 1, above the data.
In K1 add a heading, say Item, and in L1 a heading, say Number.
Select K1:L13, and choose Data – Filter – Autofilter.
Click the dropdown arrow in L1 and choose custom.
Set up the filter to be greater that 0
Apply the filter.
Lastly, select the chart, choose Tools – Options.
Click the chart tab and check the Plot visible cells only.

This will only plot data greater than 0

Hope this helps!

Reply | Quote

I need to use formulas as this is a small part of a bigger project and i need it to “automatically” take out the zeros. Is there a set of formulas i can use to get rid of them? thanks

Reply | Quote

Have you thought about using an advance data filter (data>Filter>advance filter) and set the criteria for values 0?

John

Reply | Quote

Have you thought about using an advance data filter (data>Filter>advance filter) and set the criteria for values 0?

John

Reply | Quote

If you don’t want to use the autofilter route, an automatic way could be done using dynamic range names and OFFSET.

See if this is what you are after. It defines a range for the X anf y based on the count of the cells in Col L that are >0. The chart plots these dynamic ranges.

Steve

Reply | Quote

Yes, this is it! Thank you very much

Reply | Quote

Is there a way to do this when a graph has 4 series instead of just one?

Reply | Quote

Define (using OFFSET) 5 range names instead of only 2: the Xrange and the 4 YRanges

Then use the named ranges as the chart source data.

Steve

Reply | Quote

ok, got ya

Reply | Quote

I can’t figure out what i’m doing wrong in the attached. I’m trying to make a dynamic range in case some of the rows are zero. Can u please let me know what i’m doing wrong. Thank you.

Reply | Quote

Your “counti” is yielding 0 since none of the values are >0 (most are text, the others = 0).

Try:
=COUNTIF(Bar2!$R$3:$R$15,”0″)

Steve

Reply | Quote

Your “counti” is yielding 0 since none of the values are >0 (most are text, the others = 0).

Try:
=COUNTIF(Bar2!$R$3:$R$15,”0″)

Steve

Reply | Quote

I can’t figure out what i’m doing wrong in the attached. I’m trying to make a dynamic range in case some of the rows are zero. Can u please let me know what i’m doing wrong. Thank you.

Reply | Quote

ok, got ya

Reply | Quote

Define (using OFFSET) 5 range names instead of only 2: the Xrange and the 4 YRanges

Then use the named ranges as the chart source data.

Steve

Reply | Quote

Is there a way to do this when a graph has 4 series instead of just one?

Reply | Quote

Yes, this is it! Thank you very much

Reply | Quote

If you don’t want to use the autofilter route, an automatic way could be done using dynamic range names and OFFSET.

See if this is what you are after. It defines a range for the X anf y based on the count of the cells in Col L that are >0. The chart plots these dynamic ranges.

Steve

Reply | Quote

I need to use formulas as this is a small part of a bigger project and i need it to “automatically” take out the zeros. Is there a set of formulas i can use to get rid of them? thanks

Reply | Quote

You could use autofilter to do this!
Insert a new row 1, above the data.
In K1 add a heading, say Item, and in L1 a heading, say Number.
Select K1:L13, and choose Data – Filter – Autofilter.
Click the dropdown arrow in L1 and choose custom.
Set up the filter to be greater that 0
Apply the filter.
Lastly, select the chart, choose Tools – Options.
Click the chart tab and check the Plot visible cells only.

This will only plot data greater than 0

Hope this helps!

Reply | Quote

Here’s my alternative way with some formula revised and highlighted

Hope that helps

Bosco

Reply | Quote

Here’s my alternative way with some formula revised and highlighted

Hope that helps

Bosco

Reply | Quote