Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Have a formula yield a true blank that disconnects graph line
I use a formula to calculate values or use set criteria to yield blanks.
Then I graph the data and want the line graph to end where a blank occurs rather than connect the line to zero where the blank occurs. Actually all blanks are plotted as zeros, but I could enter -1 instead of blank and make the scale minimum zero to eliminate this problem if the line did not connect from the last data point to the -1 or zero. This problem is not cured by copypaste specialvalues even though the blanks then no longer contain formula but look indistinguishable from real blanks. But they do not behave as real blanks, for example, when hitting enddown arrow. |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Have a formula yield a true blank that disconnects graph line
Hi,
If a cell contains something then the chart will plot it. Depending upon the content what is plotted will vary. Zero and text are plotted as zero. NA() is not plotted - but if the option 'Not Plotted (leave gaps)' or 'interpolated' is used then the line will connect the valid points either side. If 'Plot as zero' is used that is what is plotted, zero. Have a look at this page for a possible work around. http://www.andypope.info/charts/brokenlines.htm Cheers Andy Mr. Owl wrote: I use a formula to calculate values or use set criteria to yield blanks. Then I graph the data and want the line graph to end where a blank occurs rather than connect the line to zero where the blank occurs. Actually all blanks are plotted as zeros, but I could enter -1 instead of blank and make the scale minimum zero to eliminate this problem if the line did not connect from the last data point to the -1 or zero. This problem is not cured by copypaste specialvalues even though the blanks then no longer contain formula but look indistinguishable from real blanks. But they do not behave as real blanks, for example, when hitting enddown arrow. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Have a formula yield a true blank that disconnects graph line
Thanks Andy, I will try this. I also came across a macro code:
Sub removeblanks() Dim act As Worksheet Set act = ActiveSheet Sheets.Add After:=Sheets(act.Index) ActiveSheet.Name = "formulas" Row = 2 For Each cell In act.Range("a1").SpecialCells(xlCellTypeFormulas, 23) If cell.Value < "" Then cell.Copy Sheets("formulas").Range("a" & Row).PasteSpecial xlPasteValues Row = Row + 1 End If Next End Sub that I tried and it worked once and thereafter I can't get it to work again. I get runtime error '1004' ; no cells were found. I don't know whom to credit for this macro code, because I'm not sure where I found it. Owl "Andy Pope" wrote: Hi, If a cell contains something then the chart will plot it. Depending upon the content what is plotted will vary. Zero and text are plotted as zero. NA() is not plotted - but if the option 'Not Plotted (leave gaps)' or 'interpolated' is used then the line will connect the valid points either side. If 'Plot as zero' is used that is what is plotted, zero. Have a look at this page for a possible work around. http://www.andypope.info/charts/brokenlines.htm Cheers Andy Mr. Owl wrote: I use a formula to calculate values or use set criteria to yield blanks. Then I graph the data and want the line graph to end where a blank occurs rather than connect the line to zero where the blank occurs. Actually all blanks are plotted as zeros, but I could enter -1 instead of blank and make the scale minimum zero to eliminate this problem if the line did not connect from the last data point to the -1 or zero. This problem is not cured by copypaste specialvalues even though the blanks then no longer contain formula but look indistinguishable from real blanks. But they do not behave as real blanks, for example, when hitting enddown arrow. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use a formula to create a new line of text in a cell? | Excel Worksheet Functions | |||
CAN YOU SET GRAPH TO READ, FORMULA IN A CELL AS A BLANK CELL | Excel Discussion (Misc queries) | |||
Lookup Data in two seperate Spreadsheets | Excel Worksheet Functions | |||
Stop renaming or moving sheet tabs | Excel Discussion (Misc queries) | |||
Bar graph and line graph overlayed | Charts and Charting in Excel |