MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Help - Make "Pre-loaded" Formulas on Spreadsheet Invisible on Line Chart

Posted by Tom L. on February 14, 2002 8:31 AM

I use Excel 97. On the spreadsheet, I use columns A thru AM and rows 1 through 110. In the first five columns I enter "the date, Open, High, Low, and Close" data, and across the remaining columns have various math formulas that all start with: IF(E2="","", (or whatever the cell row - could be anything up to E110) and then enter the various formulas. I preload each column's formula down to the 110th row, and the spreadsheet stays "clean" (empty to the eye)until I enter that day's "Open, High, Low, Close" data. In other words, I drag all my formulas down from F2 to AM 110, and as I said, the spreadsheet stays "clean" until I enter the next day's data, and at that point, as I enter the data in the first five columns for that row, all the calculations across that row are just automatically accomplished - it is easy for me to administer on a daily basis.

However --- even though I may only have entered data down through row 30 on the speadsheet, for instance, the line chart picks up the fact that there are "invisible" formulas all the way down to row 110 and from the 30th "tick" on the chart, places a line at the "zero" line on "tick 31" and runs it across the "x" axis to the 110th tick. The chart is set up on the "y" axis as "zero" to "100" by tens, and the "x" axis simply goes out those 110 ticks (for the 110 days of data).

I need something that will make those preloaded, unused formulas on the spreadsheet "invisible" to the chart until a calculation is done on the spreadsheet for that particular row, and I would prefer to also keep that unused portion of the spreadsheet "clean" to the eye until I am using each row.

I have five or six lines on each chart (using data from five or six columns of the spreadsheet, obviously), and as the lines drop to "zero" after that 30th day, the chart is very confusing, so I don't want that to happen.


Posted by Stuart S on February 15, 2002 11:21 AM


Check the following in the MS Knowledge Base.
Q93681 for XL97 and Q214103 for XL2000.
IF(K5=0,NA(),J5/(8*K5)) the use fo NA() will make the cell appear to be text which will not be plotted in the chart.

Posted by Tom Lotzer on February 15, 2002 9:48 PM

Thanks - Stuart

Stuart - thank you - I appreciate the explanation - I'll try it.

Posted by Tom Lotzer on February 15, 2002 10:17 PM

Stuart - Help

My formula had been: IF(E2="","",SUM(A2:D2))
This formula would keep the cell blank on the spreadsheet unless there is data in E2 and a calculation to be made - I was able to keep a "clean" spreadsheet. Using the NA()instead of the second set of "" places a #N/A in the cell on the spreadsheet.

Isn't there a way to keep cell E2 on the spreadsheet clean until the formula actually does a calculation, while at the same time not having the chart "see" just the formula - only see the effect of a calculation from the speadsheet when one is made?