Preventing Chart values from Being Plotted

mperez · May 22nd, 2002, 07:16 AM

Hi Mr. Excel:

I was wondering if you could help me out with some chart problems.

Here’s what I am trying to do. I have created a list of values that pulls from various sources using VLOOKUPS. One list of values refers to all of last year and the other set of values refer to year to date data for the current year. There are fewer values in the current year column because we are only in May. For dates in the future, the VLOOKUPS are binging in zeros. Here’s an example:

Current Year Prior Year
0% 100%
0% 90%
0% 80%
73% 75%
64% 70%
60% 65%
57% 55%
53% 50%

I am creating a chart using this data, but I would prefer the zero values NOT be plotted on the chart because they refer to dates in the future. These zero values are resulting in my current year chart line plummeting when it reaches the zero values.

I would like for the current year line to end at the last value that is greater than zero. I tried going to the chart options and selecting the “do not plot” option for empty cells, but it didn’t work. I think the reason is because there are formulas in these cells.

Is there any way to fix this problem without manually deleting the formulas in these cells?

sann0638 · May 22nd, 2002, 07:23 AM

Try using an IF statement to turn the 0's into "", eg IF(A1=0,"",A1)

Ian Mac · May 22nd, 2002, 07:25 AM

What formulas are you using?

Excel will not plot #N/A errors, so if your formulas are taking these out, put them back. You can conditionally make them invisible if you need to.

mperez · May 22nd, 2002, 07:36 AM

I tried using the IF(A1=0,"",A1)formula and I also have an if-iserror statement to prevent error values from coming in to the data set, but I'm still having the same problem....

sann0638 · May 22nd, 2002, 07:40 AM

=if(A1=0,NA(),A1) seems to work, although you do get left with a load of #N/A so it depends if this is a problem or not.

Ian Mac · May 22nd, 2002, 07:50 AM

Quote:

On 2002-05-22 06:36, mperez wrote:
I tried using the IF(A1=0,"",A1)formula and I also have an if-iserror statement to prevent error values from coming in to the data set, but I'm still having the same problem....

Excel will not plot #N/A errors, so if your formulas are taking these out, put them back. You can conditionally make them invisible if you need to.

To make the errors Non-visible

Highlight the area you want
goto Format>Conditional Formating

change the box to Formula Is and type =ISNA(C11)

C11 being the top left cell of the area you highlighted.

Format as desired (usually to white font)

May 22nd, 2002, 07:23 AM	#2
sann0638 Board Regular Join Date: Mar 2002 Posts: 175	Try using an IF statement to turn the 0's into "", eg IF(A1=0,"",A1) __________________ Regards, Mike.

May 22nd, 2002, 07:25 AM	#3
Ian Mac MrExcel MVP Join Date: Feb 2002 Location: Newcastle, UK Posts: 1,174	What formulas are you using? Excel will not plot #N/A errors, so if your formulas are taking these out, put them back. You can conditionally make them invisible if you need to. __________________ "Have a good time......all the time" Ian Mac

May 22nd, 2002, 07:40 AM	#5
sann0638 Board Regular Join Date: Mar 2002 Posts: 175	=if(A1=0,NA(),A1) seems to work, although you do get left with a load of #N/A so it depends if this is a problem or not. __________________ Regards, Mike.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 22nd, 2002, 07:16 AM	#1
mperez New Member Join Date: May 2002 Posts: 2	Hi Mr. Excel: I was wondering if you could help me out with some chart problems. Here’s what I am trying to do. I have created a list of values that pulls from various sources using VLOOKUPS. One list of values refers to all of last year and the other set of values refer to year to date data for the current year. There are fewer values in the current year column because we are only in May. For dates in the future, the VLOOKUPS are binging in zeros. Here’s an example: Current Year Prior Year 0% 100% 0% 90% 0% 80% 73% 75% 64% 70% 60% 65% 57% 55% 53% 50% I am creating a chart using this data, but I would prefer the zero values NOT be plotted on the chart because they refer to dates in the future. These zero values are resulting in my current year chart line plummeting when it reaches the zero values. I would like for the current year line to end at the last value that is greater than zero. I tried going to the chart options and selecting the “do not plot” option for empty cells, but it didn’t work. I think the reason is because there are formulas in these cells. Is there any way to fix this problem without manually deleting the formulas in these cells?

May 22nd, 2002, 07:36 AM	#4
mperez New Member Join Date: May 2002 Posts: 2	I tried using the IF(A1=0,"",A1)formula and I also have an if-iserror statement to prevent error values from coming in to the data set, but I'm still having the same problem....