Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Charting with zeros or DIV/0 values
Hi - this might have an easy solution, but I'm a bit stuck. I've created a spreadsheet for data entry by another party. This data will be entered on a monthly basis and I only want to update it occasionally. So, I have control charts set up with the basic formulae (percent, mean, upper and lower control limits) in there. I was wondering if there was any way to have all of this data in the range of the chart without having the chart bottom out (i.e. plotting zeros). For example, my columns _might_ look something like this: Date.........Total...Defects...%ofDefects...Mean.. ..UCL...LCL And this _might_ be one data point: Mar2005...230....23............10%.............10% .....0%....50% So, I would have the percent, mean and upper and lower control limits set to calculate automatically, however, when there's no denominator (Total) present, there is naturally an error. I've taken care of the error appearance with an IF function, but when I set up the graphs, there are zeroes for the subsequent months because they have no data. Essentially, I would like to know if I can have the calculations set up to be captured by the chart and NOT have zeroes show up on the chart. I was reading something about dynamic range, but couldn't figure it out. If you all believe that is the best option, I'm happy to go learn more about it. I hope this makes sense. Thanks so much, Marianne :) -- MarianneR ------------------------------------------------------------------------ MarianneR's Profile: http://www.excelforum.com/member.php...fo&userid=6253 View this thread: http://www.excelforum.com/showthread...hreadid=385748 |
#2
|
|||
|
|||
Your formula is like this:
IF(<something,<value,"") Change this to IF(<something,<value,NA()) This produces the #N/A error in the worksheet, which is ugly there, but which is ignored in a line or scatter chart. Debra Dalgleish shows how to hide the errors in the sheet with conditional formatting: http://contextures.com/xlCondFormat03.html#Errors - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ MarianneR wrote: Hi - this might have an easy solution, but I'm a bit stuck. I've created a spreadsheet for data entry by another party. This data will be entered on a monthly basis and I only want to update it occasionally. So, I have control charts set up with the basic formulae (percent, mean, upper and lower control limits) in there. I was wondering if there was any way to have all of this data in the range of the chart without having the chart bottom out (i.e. plotting zeros). For example, my columns _might_ look something like this: Date.........Total...Defects...%ofDefects...Mean.. ..UCL...LCL And this _might_ be one data point: Mar2005...230....23............10%.............10% .....0%....50% So, I would have the percent, mean and upper and lower control limits set to calculate automatically, however, when there's no denominator (Total) present, there is naturally an error. I've taken care of the error appearance with an IF function, but when I set up the graphs, there are zeroes for the subsequent months because they have no data. Essentially, I would like to know if I can have the calculations set up to be captured by the chart and NOT have zeroes show up on the chart. I was reading something about dynamic range, but couldn't figure it out. If you all believe that is the best option, I'm happy to go learn more about it. I hope this makes sense. Thanks so much, Marianne :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Match function with duplicate values in an array | Excel Worksheet Functions | |||
Charting cumulative values in histogram ? | Charts and Charting in Excel | |||
Second serie doesn't use X-as values | Charts and Charting in Excel | |||
Leading Zeros in Numeric Values | Excel Worksheet Functions | |||
Charting time? help please... | Charts and Charting in Excel |