|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
November 10th, 2009, 04:30 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
2003 Excel Chart with zero data
I have a chart that shows the fiscal year. In the months that currently do
not have data it is charting to a zero for the month. How can I get the chart not to show the next month's that have a zero for a value? The catch is that some months maybe zero and the months that do not have a value are currently empty. Below is the formula that I am currently using. =IF(SUM('Global KPI Monitoring chart'!F7:F8)=0,IF(AND('Global KPI Monitoring chart'!F7="", 'Global KPI Monitoring chart'!F8=""),"",0),SUM('Global KPI Monitoring chart'!F7:F8)) 
|
|
#2
November 10th, 2009, 04:40 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
2003 Excel Chart with zero data
Instead of an output of "", have the formula output the #N/A error (as charts
recognize this data as not applicable) I believe something like this: =IF(SUM('Global KPI Monitoring chart'!F7:F8)=0,IF(AND('Global KPI Monitoring chart'!F7="", 'Global KPI Monitoring chart'!F8=""),NA(),0),SUM('Global KPI Monitoring chart'!F7:F8)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "C Kreig" wrote: I have a chart that shows the fiscal year. In the months that currently do not have data it is charting to a zero for the month. How can I get the chart not to show the next month's that have a zero for a value? The catch is that some months maybe zero and the months that do not have a value are currently empty. Below is the formula that I am currently using. =IF(SUM('Global KPI Monitoring chart'!F7:F8)=0,IF(AND('Global KPI Monitoring chart'!F7="", 'Global KPI Monitoring chart'!F8=""),"",0),SUM('Global KPI Monitoring chart'!F7:F8))
|
|
#3
November 10th, 2009, 05:12 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
2003 Excel Chart with zero data
Luke-
If I do that, I get ?Name error "Luke M" wrote: Instead of an output of "", have the formula output the #N/A error (as charts recognize this data as not applicable) I believe something like this: =IF(SUM('Global KPI Monitoring chart'!F7:F8)=0,IF(AND('Global KPI Monitoring chart'!F7="", 'Global KPI Monitoring chart'!F8=""),NA(),0),SUM('Global KPI Monitoring chart'!F7:F8)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "C Kreig" wrote: I have a chart that shows the fiscal year. In the months that currently do not have data it is charting to a zero for the month. How can I get the chart not to show the next month's that have a zero for a value? The catch is that some months maybe zero and the months that do not have a value are currently empty. Below is the formula that I am currently using. =IF(SUM('Global KPI Monitoring chart'!F7:F8)=0,IF(AND('Global KPI Monitoring chart'!F7="", 'Global KPI Monitoring chart'!F8=""),"",0),SUM('Global KPI Monitoring chart'!F7:F8))
|
|
#4
November 10th, 2009, 05:21 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
2003 Excel Chart with zero data
Luke-
I got the formula to work, however it is still showing a value of zero on my chart. Any suggestions? "Luke M" wrote: Instead of an output of "", have the formula output the #N/A error (as charts recognize this data as not applicable) I believe something like this: =IF(SUM('Global KPI Monitoring chart'!F7:F8)=0,IF(AND('Global KPI Monitoring chart'!F7="", 'Global KPI Monitoring chart'!F8=""),NA(),0),SUM('Global KPI Monitoring chart'!F7:F8)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "C Kreig" wrote: I have a chart that shows the fiscal year. In the months that currently do not have data it is charting to a zero for the month. How can I get the chart not to show the next month's that have a zero for a value? The catch is that some months maybe zero and the months that do not have a value are currently empty. Below is the formula that I am currently using. =IF(SUM('Global KPI Monitoring chart'!F7:F8)=0,IF(AND('Global KPI Monitoring chart'!F7="", 'Global KPI Monitoring chart'!F8=""),"",0),SUM('Global KPI Monitoring chart'!F7:F8))
|
|
#5
November 10th, 2009, 07:03 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
2003 Excel Chart with zero data
Hmm. You might want to take a look at dynamic charting techniques to help
limit the amount of data going into your chart. http://peltiertech.com/Excel/Charts/Dynamics.html It appears you are trying not only keep the data from months that have no data from being plotted, but also keep those months from appearing on the axis, correct? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "C Kreig" wrote: Luke- I got the formula to work, however it is still showing a value of zero on my chart. Any suggestions? "Luke M" wrote: Instead of an output of "", have the formula output the #N/A error (as charts recognize this data as not applicable) I believe something like this: =IF(SUM('Global KPI Monitoring chart'!F7:F8)=0,IF(AND('Global KPI Monitoring chart'!F7="", 'Global KPI Monitoring chart'!F8=""),NA(),0),SUM('Global KPI Monitoring chart'!F7:F8)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "C Kreig" wrote: I have a chart that shows the fiscal year. In the months that currently do not have data it is charting to a zero for the month. How can I get the chart not to show the next month's that have a zero for a value? The catch is that some months maybe zero and the months that do not have a value are currently empty. Below is the formula that I am currently using. =IF(SUM('Global KPI Monitoring chart'!F7:F8)=0,IF(AND('Global KPI Monitoring chart'!F7="", 'Global KPI Monitoring chart'!F8=""),"",0),SUM('Global KPI Monitoring chart'!F7:F8))
|
|
#6
November 11th, 2009, 03:23 AM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
2003 Excel Chart with zero data
Luke-
I am doing a chart for fy and from Nov - Aug we do not have data. Instead of the chart leaving the point blank where the data does not exist it considers it a zero value and is charting it as a zero. "Luke M" wrote: Hmm. You might want to take a look at dynamic charting techniques to help limit the amount of data going into your chart. http://peltiertech.com/Excel/Charts/Dynamics.html It appears you are trying not only keep the data from months that have no data from being plotted, but also keep those months from appearing on the axis, correct? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "C Kreig" wrote: Luke- I got the formula to work, however it is still showing a value of zero on my chart. Any suggestions? "Luke M" wrote: Instead of an output of "", have the formula output the #N/A error (as charts recognize this data as not applicable) I believe something like this: =IF(SUM('Global KPI Monitoring chart'!F7:F8)=0,IF(AND('Global KPI Monitoring chart'!F7="", 'Global KPI Monitoring chart'!F8=""),NA(),0),SUM('Global KPI Monitoring chart'!F7:F8)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "C Kreig" wrote: I have a chart that shows the fiscal year. In the months that currently do not have data it is charting to a zero for the month. How can I get the chart not to show the next month's that have a zero for a value? The catch is that some months maybe zero and the months that do not have a value are currently empty. Below is the formula that I am currently using. =IF(SUM('Global KPI Monitoring chart'!F7:F8)=0,IF(AND('Global KPI Monitoring chart'!F7="", 'Global KPI Monitoring chart'!F8=""),"",0),SUM('Global KPI Monitoring chart'!F7:F8))
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
