Skip zeros or non numeric cells in a graph

Email lists > MS Excel Questions and Answers

Thread: Skip zeros or non numeric cells in a graph

Search this list only Search all lists
Skip zeros or non numeric cells in a graph
	2006-07-27 22:22:18
Hi all: I am wondering if any of you could help me find a way to set up a chart so that it only shows the data in the cells that are over one. Right now, because the cells are linked from a different spreadsheet, it's charting those cells on the charts as 'zeros.' I have formatted the data and used if formulas so no numbers show if the cell value from the other spreadsheet is blank. Sample data below: 2006-07 2005-06 April 500 450 May 450 600 June 400 300 July 50 August 240 September 100 October 90 November 80 December 70 January 60 February 50 March 40 Formulas: 2006-07 April =IF('Current Year volumes'!B2<>0,'Current Year volumes'!B2, ) May =IF('Current Year volumes'!B3<>0,'Current Year volumes'!B3, ) June =IF('Current Year volumes'!B4<>0,'Current Year volumes'!B4, ) July =IF('Current Year volumes'!B5<>0,'Current Year volumes'!B5, ) August =IF('Current Year volumes'!B6<>0,'Current Year volumes'!B6, ) September =IF('Current Year volumes'!B7<>0,'Current Year volumes'!B7, ) October =IF('Current Year volumes'!B8<>0,'Current Year volumes'!B8, ) November =IF('Current Year volumes'!B9<>0,'Current Year volumes'!B9, ) December =IF('Current Year volumes'!B10<>0,'Current Year volumes'!B10, ) January =IF('Current Year volumes'!B11<>0,'Current Year volumes'!B11, ) February =IF('Current Year volumes'!B12<>0,'Current Year volumes'!B12, ) March =IF('Current Year volumes'!B13<>0,'Current Year volumes'!B13, ) Chart values show as (because the cells have a formula in them): 2006-07 2005-06 April 500 450 May 450 600 June 400 300 July 0 50 August 0 240 September 0 100 October 0 90 November 0 80 December 0 70 January 0 60 February 0 50 March 0 40 If you have any questions, please let me know. If you can offer any help it would be appreciated.. Thanks!!! ------------------------------------------------------------ -------------- The EXCEL-G list is hosted on a Windows NT(TM) machine running L-Soft international's LISTSERV(R) software. For subscription/signoff info and archives, see htt p://peach.ease.lsoft.com/archives/excel-g.html . COPYRIGHT INFO: http://peach.ease.lsoft.com/scripts/wa .exe?SHOWTPL=COPYRIGHT&L=EXCEL-G

Skip zeros or non numeric cells in a graph
	2006-07-27 22:34:40
You can set the chart options to not plot zeroes or use the NA() function. =IF('Current Year volumes'!B2<>0,'Current Year volumes'!B2,NA()) It depends on how you would like to display the missing items. Damon Longworth 2006 West Coast Excel / Access User Conference October 25-27th, 2006 Marina del Rey Hotel Marina del Rey, California USA Announcement soon! -----Original Message----- From: MS Excel General Q & A List [mailto:EXCEL-GPEACH.EASE.LSOFT.COM] On Behalf Of Teri W Sent: Thursday, July 27, 2006 5:22 PM To: EXCEL-GPEACH.EASE.LSOFT.COM Subject: Skip zeros or non numeric cells in a graph Hi all: I am wondering if any of you could help me find a way to set up a chart so that it only shows the data in the cells that are over one. Right now, because the cells are linked from a different spreadsheet, it's charting those cells on the charts as 'zeros.' I have formatted the data and used if formulas so no numbers show if the cell value from the other spreadsheet is blank. Sample data below: 2006-07 2005-06 April 500 450 May 450 600 June 400 300 July 50 August 240 September 100 October 90 November 80 December 70 January 60 February 50 March 40 Formulas: 2006-07 April =IF('Current Year volumes'!B2<>0,'Current Year volumes'!B2, ) May =IF('Current Year volumes'!B3<>0,'Current Year volumes'!B3, ) June =IF('Current Year volumes'!B4<>0,'Current Year volumes'!B4, ) July =IF('Current Year volumes'!B5<>0,'Current Year volumes'!B5, ) August =IF('Current Year volumes'!B6<>0,'Current Year volumes'!B6, ) September =IF('Current Year volumes'!B7<>0,'Current Year volumes'!B7, ) October =IF('Current Year volumes'!B8<>0,'Current Year volumes'!B8, ) November =IF('Current Year volumes'!B9<>0,'Current Year volumes'!B9, ) December =IF('Current Year volumes'!B10<>0,'Current Year volumes'!B10, ) January =IF('Current Year volumes'!B11<>0,'Current Year volumes'!B11, ) February =IF('Current Year volumes'!B12<>0,'Current Year volumes'!B12, ) March =IF('Current Year volumes'!B13<>0,'Current Year volumes'!B13, ) Chart values show as (because the cells have a formula in them): 2006-07 2005-06 April 500 450 May 450 600 June 400 300 July 0 50 August 0 240 September 0 100 October 0 90 November 0 80 December 0 70 January 0 60 February 0 50 March 0 40 If you have any questions, please let me know. If you can offer any help it would be appreciated.. Thanks!!! ------------------------------------------------------------ -------------- The EXCEL-G list is hosted on a Windows NT(TM) machine running L-Soft international's LISTSERV(R) software. For subscription/signoff info and archives, see htt p://peach.ease.lsoft.com/archives/excel-g.html . COPYRIGHT INFO: http://peach.ease.lsoft.com/scripts/wa .exe?SHOWTPL=COPYRIGHT&L=EXCEL-G ------------------------------------------------------------ -------------- The EXCEL-G list is hosted on a Windows NT(TM) machine running L-Soft international's LISTSERV(R) software. For subscription/signoff info and archives, see htt p://peach.ease.lsoft.com/archives/excel-g.html . COPYRIGHT INFO: http://peach.ease.lsoft.com/scripts/wa .exe?SHOWTPL=COPYRIGHT&L=EXCEL-G

Skip zeros or non numeric cells in a graph
	2006-07-31 15:43:09
No need for Conditional Formatting, Custom Formatting, etc. Just go to Tools, Options, View, Window Options and uncheck "Zero Values". If you do not want to use that method, use a Custom Format of 0;0;;. ( This just doesn't even display Zero's. Your last resort is Conditional Formatting of the type "Formula Is". Select your range ( B2:B13 ) and go to Format, Conditional Formatting, Formula Is and enter =B2=0, Set your Font to White and hit enter. As for the chart series, Define a named range as "myFirstSeries" with a formula of: =IF(Sheet1!B2:B13<>0,Sheet1!B2:B13,NA()) and Array Enter it. ( i.e. type the formula and hit Control+Shift+Enter ) Now right click your chart and select Source Data. In the window that pops up, enter =Book1!myFirstSeries as the as the values for your 2006-07 series. ( replace sheet and book names as required and repeat for your others series as required. ) thank you, Wyatt Lemmons, HSG (425) 830-5962 4x10 Wrk Sch ( Fri OFF ) & Telecommute: Wed -----Original Message----- From: Teri W [mailto:teriletterHOTMAIL.COM] Sent: Thursday, July 27, 2006 3:22 PM To: EXCEL-GPEACH.EASE.LSOFT.COM Subject: Skip zeros or non numeric cells in a graph Hi all: I am wondering if any of you could help me find a way to set up a chart so that it only shows the data in the cells that are over one. Right now, because the cells are linked from a different spreadsheet, it's charting those cells on the charts as 'zeros.' I have formatted the data and used if formulas so no numbers show if the cell value from the other spreadsheet is blank. Sample data below: 2006-07 2005-06 April 500 450 May 450 600 June 400 300 July 50 August 240 September 100 October 90 November 80 December 70 January 60 February 50 March 40 Formulas: 2006-07 April =IF('Current Year volumes'!B2<>0,'Current Year volumes'!B2, ) May =IF('Current Year volumes'!B3<>0,'Current Year volumes'!B3, ) June =IF('Current Year volumes'!B4<>0,'Current Year volumes'!B4, ) July =IF('Current Year volumes'!B5<>0,'Current Year volumes'!B5, ) August =IF('Current Year volumes'!B6<>0,'Current Year volumes'!B6, ) September =IF('Current Year volumes'!B7<>0,'Current Year volumes'!B7, ) October =IF('Current Year volumes'!B8<>0,'Current Year volumes'!B8, ) November =IF('Current Year volumes'!B9<>0,'Current Year volumes'!B9, ) December =IF('Current Year volumes'!B10<>0,'Current Year volumes'!B10, ) January =IF('Current Year volumes'!B11<>0,'Current Year volumes'!B11, ) February =IF('Current Year volumes'!B12<>0,'Current Year volumes'!B12, ) March =IF('Current Year volumes'!B13<>0,'Current Year volumes'!B13, ) Chart values show as (because the cells have a formula in them): 2006-07 2005-06 April 500 450 May 450 600 June 400 300 July 0 50 August 0 240 September 0 100 October 0 90 November 0 80 December 0 70 January 0 60 February 0 50 March 0 40 If you have any questions, please let me know. If you can offer any help it would be appreciated.. Thanks!!! ------------------------------------------------------------ ------------ -- The EXCEL-G list is hosted on a Windows NT(TM) machine running L-Soft international's LISTSERV(R) software. For subscription/signoff info and archives, see htt p://peach.ease.lsoft.com/archives/excel-g.html . COPYRIGHT INFO: http://peach.ease.lsoft.com/scripts/wa .exe?SHOWTPL=COPYRIGHT&L=EXCEL-G ------------------------------------------------------------ -------------- The EXCEL-G list is hosted on a Windows NT(TM) machine running L-Soft international's LISTSERV(R) software. For subscription/signoff info and archives, see htt p://peach.ease.lsoft.com/archives/excel-g.html . COPYRIGHT INFO: http://peach.ease.lsoft.com/scripts/wa .exe?SHOWTPL=COPYRIGHT&L=EXCEL-G

[1-3]

about | contact Other archives ( Real Estate discussion Medical topics )

Mailing lists

ARCHIVES