Charting Problem
Posted by Thomas Lotzer on February 13, 2002 9:51 AM
I'm currently using Excel 97 - but same old problem as with older versions.
I preload my formulas on the spreadsheet and drag them down 110 rows and go across columns G to AM. On a daily basis, I add new data in columns A to E and the formulas put the calculated amounts across G through AM on that specific row, but the unused rows below that appear blank (the preload formulas are there however).
I use 9 different line charts to view various data from the spreadsheet(the charts having a "y" axis of "zero to 100 by 10's", and the "x" axis goes out the 110 rows).
Now the problem. The charts identify those "invisible" preloaded formulas as a "zero" and places a line across the "unused" portion of the 110 rows of the chart at the "zero" level - I don't want anything to appear on the unused portion of the 110 rows as it is most confusing.
Does anyone have a solution to keep the unused "preloaded" formulas from being seen by the chart until the formulas turn into visible calculated amounts on the spreadsheet?
Posted by Mark W. on February 13, 2002 10:08 AM
What value does your "invisible" preloaded formula produce?...
...by chance, is if IF(condition,formula,""). If
so replace "" with #N/A.
Posted by Clifford Phillips on February 13, 2002 5:19 PM
IF you don't need to see All 110 rolls but only the active rolls then adjust your charts each day. I understand that if your data for your chart is in adjoining columns there is a easy way to do this in excel-97. Click once on the chart to select it, around the data that is included in the chart, you will see a blue outline, in the bottom right hand corner of the blue outline there is a little handle left click on it, and drag it up or down till you have the data you want to see in the chart. If this wont work there is a page here on Mrexcel.com that will tell you the other way to adjust the chart size.
Posted by Thomas Lotzer on February 13, 2002 6:45 PM
Posted by Thomas Lotzer on February 13, 2002 6:52 PM
Showing me all 110 rows on the "x" axis gives me a perspective I need from a size of the five or six lines generated from the calculated solutions presented in the columns.
I am looking to just enter stock market "open, high, low, close" values on a daily basis, and having the "preloaded" formulas automatically calculate the answers across the row, and the results in the individual columns then add one more tick of information on that specific line on the chart - I'm looking for something automatic since I chart 22 different stocks daily and the process gets tedious doing this day in and day out.
I appreciate your help - by the way, I'm not a great technician - another reason for me wanting to keep this process automatic and simple.
Posted by Thomas Lotzer on February 13, 2002 7:02 PM
Re: What value does your "invisible" preloaded formula produce?...
I track 22 different commodities on a daily basis with "open, high, low, close" data and start all the formula lines with (IF(E6="","",(.....) and then use the math formula. The cell E6 would be a closing price - if I haven't entered data for that date yet, nothing shows visually on the cells from say, G6 to AM6. When I enter the daily data on row 6, then my formulas would automatically calculate across that row, and one more "tick" is added to the chart (other than that "zero" tick). Since I rely on data from five or six columns on each chart, the chart gets rather confusing if the data is generally up around, say the midway portion of the chart (the chart going from zero to 100 by ten's), and then dropping straight down to the "zero" line and saying there right across to the 110th tick.
By the way, I appreciate the input you are giving me. I'm not that gifted technically, so want to try to keep this as simple and automatic as possible once I have set up my template for the 22 commodities I track on a daily basis.
Posted by Thomas Lotzer on February 13, 2002 7:02 PM
Re: What value does your "invisible" preloaded formula produce?...
I track 22 different commodities on a daily basis with "open, high, low, close" data and start all the formula lines with (IF(E6="","",(.....) and then use the math formula. The cell E6 would be a closing price - if I haven't entered data for that date yet, nothing shows visually on the cells from say, G6 to AM6. When I enter the daily data on row 6, then my formulas would automatically calculate across that row, and one more "tick" is added to the chart (other than that "zero" tick). Since I rely on data from five or six columns on each chart, the chart gets rather confusing if the data is generally up around, say the midway portion of the chart (the chart going from zero to 100 by ten's), and then dropping straight down to the "zero" line and saying there right across to the 110th tick.
By the way, I appreciate the input you are giving me. I'm not that gifted technically, so want to try to keep this as simple and automatic as possible once I have set up my template for the 22 commodities I track on a daily basis.
