Sometimes Macrocrap Excel really cheeses me off! My current ongoing frustration (Excel "feature" #5389) is the inability to have an automatic gap in a line chart based on a formula that defines a missing or invalid data value. Anyone know a way to have Excel NOT draw a data point if it is invalid or something? Everything that I have tried (e.g.,#DIV/0!, #VALUE, #NUM, FALSE) cause the stupid chart routines to draw a line from the previous valid value down to 0 and then back up to the next valid value. The problem is that the "missing data" is not blank so Preferences:Chart:Empty cells plotted as = "Not plotted (leave gaps)" does not apply. Anyone got any bright ideas for getting the chart drawing routines to "lift the pen" when they hit data with a special invalid/erroneous value? Thanx for any ideas, Traden4Alpha
You probably already know this Alpha, but www.MrExcel.com is a free site loaded with archived answers to questions and an active forum for questions. There are MrExcel reps there answering stuff. It is an excellent site imo.
inandlong, Well, I did NOT know about MrExcel. Thank you, thank you, thank you! I will post my question in those hallowed halls. Cheers, Traden4Alpha
Have you tried an if/then statement and include the error #'s to check against? Then if it is in error, use the previous cells value as next plot (or something). Syntax ERROR.TYPE(error_val) Error_val is the error value whose identifying number you want to find. Although error_val can be the actual error value, it will usually be a reference to a cell containing a formula that you want to test. If error_val is ERROR.TYPE returns #NULL! 1 #DIV/0! 2 #VALUE! 3 #REF! 4 #NAME? 5 #NUM! 6 #N/A 7 Anything else #N/A Example The following formula checks cell E50 to see whether it contains a #N/A error value. If it does, the text "Value is not available" is displayed. Otherwise, the value in E50 is returned. IF(ERROR.TYPE(E50)=7, "Value is not available", E50)
Minime, Good suggestion for filling in the missing data on a chart, but I actually WANT the gaps. I'd like to create my own variants of OHLC and candlestick charts and DON'T want a line between the opening price and the prior closing price. I would also use chart gaps to plot Open P&L of trades and want the chart line to end in mid-air when the trade ends vs. having the Open P&L drop to zero when it hits an "undefined" between-trades period. There must be a way to do this -- I remember programming an old Techtronix plotter using Z80 assembler in CPM and "Pen Up" and "Pen Down" were pretty fundamental commands. You would think that Excel would have technical innovations from the 70s built into it. (Of course Excel's 256 column limit is evidence to the contrary ) Thanx for the idea, it will be useful for other situations. Trade well, Traden4Alpha
Or easier to remember than Minime's perfectly good solution... Use the ISNUMBER function. IF (ISNUMBER(E50),E50,"") Use the double quotes right next to each other to have a completely blank cell that won't be plotted.
Yes, that should solve your problem. The "" creates a null cell that will give you a blank plot if set correctly in the options.
Alas, I tried this and it does not work. SIGH! The construct IF(ISNUMBER(E50),E50,"") does not actually create a blank cell, it creates a text cell with no text in it. If we put the formula IF(ISNUMBER(E50),E50,"") into cell F50 and then the formula TYPE(F50) in G50, the result is 2. This indicates that the result of the IF(ISNUMBER(E50),E50,"") is a text value (and the chart plots a line down to 0 and back up). If we delete the contents of cell F50, then TYPE(F50) returns the value 1 (and the chart plots a gap if the preferences are set correctly). Have you actually used this trick? Maybe my problem is my old copy of Excel 97. Thanx for all the ongoing efforts, Traden4Alpha