Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Is it possible to plot discontiguous cells?
Hi, I posted this yesterday in Miscellaneous, but thought it would be a good idea to ask here, too. I have a column which returns data from other columns based on a set of formulas. The result in each cell can be either a number (to plot in a line chart) or a blank and/or "-" sign like so: 5 - - 3 - - - 8 I would like to be able to chart only the numbers which result in this column. The dashes and numbers will not always position in the same manner as the calculations are altered by user input (therefore I cannot merely return the next number a fixed rows down into another column to form a contiguous range). Also, I have tried using array formulas to form a contiguous range in a neighboring column but the resulting memory overload makes my workbook run very very slowly, so I'd like to avoid this as it's not feasible to work with the workbook afterwards. Does anyone know of a way I could chart only the numbers from such a range? I've read that charts don't graph hidden rows, is there a way to hide a row using a formula's result (say if the cell is going to be "-", then hide it)? Any advice would be welcomed. -Peter -- Peter Bernadyne ------------------------------------------------------------------------ Peter Bernadyne's Profile: http://www.excelforum.com/member.php...fo&userid=7017 View this thread: http://www.excelforum.com/showthread...hreadid=540669 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Is it possible to plot discontiguous cells?
"Peter Bernadyne" <Peter.Bernadyne.27lhyy_1147270502.147@excelforu m-nospam.com schrieb im Newsbeitrag news:Peter.Bernadyne.27lhyy_1147270502.147@excelfo rum-nospam.com... Hi, I posted this yesterday in Miscellaneous, but thought it would be a good idea to ask here, too. I have a column which returns data from other columns based on a set of formulas. The result in each cell can be either a number (to plot in a line chart) or a blank and/or "-" sign like so: 5 - - 3 - - - 8 Any advice would be welcomed. -Peter Hi Peter, Could a short macro making a contiguous series of your figures in an (empty) Column of your choice (Column B in the example, assuming the original values are in Column A down from A1) help you ? could look as follows Sub help() Range("A1").Select Do Until IsEmpty(ActiveCell) If Application.WorksheetFunction.IsText(ActiveCell.Va lue) = False _ Then _ Range("B65536").End(xlUp).Offset(1, 0).Value = ActiveCell.Value ActiveCell.Offset(1, 0).Select Loop End Sub regards Jean |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Is it possible to plot discontiguous cells?
Hi Jean, Thanks for your reply. Actually, I am making use of such a macro at the moment, but I'm trying to avoid embedding any more code in the workbook and was hoping to make it lighter using formulas or somehow tricking the charting utility into skipping the blank fields. If you have any other ideas, I'd welcome them. Thanks again, -Pete -- Peter Bernadyne ------------------------------------------------------------------------ Peter Bernadyne's Profile: http://www.excelforum.com/member.php...fo&userid=7017 View this thread: http://www.excelforum.com/showthread...hreadid=540669 |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Is it possible to plot discontiguous cells?
"Peter Bernadyne" <Peter.Bernadyne.27lsdy_1147284002.2921@excelfor um-nospam.com schrieb im Newsbeitrag news:Peter.Bernadyne.27lsdy_1147284002.2921@excelf orum-nospam.com... Hi Jean, Thanks for your reply. Actually, I am making use of such a macro at the moment, but I'm trying to avoid embedding any more code in the workbook and was hoping to make it lighter using formulas or somehow tricking the charting utility into skipping the blank fields. If you have any other ideas, I'd welcome them. Thanks again, -Pete Hi Pete, although I don't believe that such a short macro (which in the bargain could be in your personl.xls) should have a significant influence of the "slowness" of your workbook - on the contrary of too much formulas ? - could you consider in a free column such a formula : = IF(A1 = "-", "=NA()", A1) Copy down. Doesn't look very nice (optically) but your chart should be OK ? regards Jean |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Is it possible to plot discontiguous cells?
"Jean Ruch" schrieb im Newsbeitrag = IF(A1 = "-", "=NA()", A1) Hi Pete, Sorry: I guess it rather should look like = IF(A1 = "-", #NA, A1) In German Excel : =WENN (A1="-";#NV;A1) regards Jean |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Is it possible to plot discontiguous cells?
Hi Jean, Thanks again for responding and I apologize for my delayed response. When I try this, unfortunately the charting utility in Excel reads the null spaces in between as zero, so when I try to create a line chart, I have a very irregular pattern as it is plotting something like 5-0-0-3-0-0-0-8 instead of skipping the null values and just plotting 5-3-8, and so on. By the way, my formula is: =IF(A1="-","",A1) [maybe not correct?] =WENN(A1="-","",A1), glaube ich This is exactly the sort of solution I'm looking for. If I could get this formula to work properly, I believe I will have my solution. -Pete -- Peter Bernadyne ------------------------------------------------------------------------ Peter Bernadyne's Profile: http://www.excelforum.com/member.php...fo&userid=7017 View this thread: http://www.excelforum.com/showthread...hreadid=540669 |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
Is it possible to plot discontiguous cells?
"Peter Bernadyne" <Peter.Bernadyne.27nceo_1147356611.6245@excelfor um-nospam.com schrieb im Newsbeitrag news:Peter.Bernadyne.27nceo_1147356611.6245@excelf orum-nospam.com... Hi Jean, Thanks again for responding and I apologize for my delayed response. When I try this, unfortunately the charting utility in Excel reads the null spaces in between as zero, so when I try to create a line chart, I have a very irregular pattern as it is plotting something like 5-0-0-3-0-0-0-8 instead of skipping the null values and just plotting 5-3-8, and so on. By the way, my formula is: =IF(A1="-","",A1) [maybe not correct?] =WENN(A1="-","",A1), glaube ich This is exactly the sort of solution I'm looking for. If I could get this formula to work properly, I believe I will have my solution. Hallo Pete, may be my syntax corrected in the second posting as = IF(A1 = "-", #NA, A1) is not fully correct for the English version ( that I supposed equivalent to #NV in the German version I am using at the time). In the cell must stand #NA without any quotes, whereas in the formula bar, you read = NA() (The whole through analogy with my version) you can achieve a correct issue when you write in a cell = NA() This particular value (standing for #Not Available) has for effect, that cells with this content are fully ignored for charting as the name of the whole indicates already... When You tell me that this is not the case for you , I'm ready to conclude, as long as the sign seems to be present in the cell, that it is inside quotes ( my error in the intermediate Posting, I beg your pardon! ). Alternatively you could try simply to REPLACE the minus sign by #NA through tools from the Menue of through the proper worksheet function. In my german version it works fine by using for this purpose the worksheetfunction "WECHSELN" what should be something like change / exchange in English ? You easily can verify, that a graph will not visualize the cells with the correct content as having a zero value, as it is the case when their content is interpreted as a text. Coming back to the slowness of your workbook. I suppose you have a lot of calculations happening through Code. Did you optimize that latter ( for example beginning with the general organization of the whole, avoiding as soon as possible Select. + Selection...or select copy / Select / Paste. and making use of Screenupdating = False, etc... ?) May be you can make it faster.... I hope by all means that you will succeed with your charts, regards Jean |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
Is it possible to plot discontiguous cells?
Jean! You are absolutely correct!! I finally got it, now it works beautifully: =IF(A1="-",NA(),A1) Thank you so very much for your kind help. Also, thank you for your macro tips. In fact, I am making use of those suggestions, too (too much data and code!!) But this suggestion is exactly what I was looking for. I hope I can return the favor one day. Best Regards, -Pete -- Peter Bernadyne ------------------------------------------------------------------------ Peter Bernadyne's Profile: http://www.excelforum.com/member.php...fo&userid=7017 View this thread: http://www.excelforum.com/showthread...hreadid=540669 |
#9
Posted to microsoft.public.excel.charting
|
|||
|
|||
Is it possible to plot discontiguous cells?
"Peter Bernadyne" <Peter.Bernadyne.27nt2m_1147378206.1604@excelfor um-nospam.com schrieb im Newsbeitrag news:Peter.Bernadyne.27nt2m_1147378206.1604@excelf orum-nospam.com... Jean! I finally got it, now it works beautifully: Hi Pete, glad to hear that. It was a pleasure for me. best regards Jean |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
How-to not plot cells with zero value on graphs | Excel Discussion (Misc queries) | |||
how do i protect cells in a shared worksheet | Excel Discussion (Misc queries) | |||
Display first, second, etc Nonblank Cells in a Range | Excel Worksheet Functions | |||
Plot Wizard -- "Not enough system resources to display completely. | Charts and Charting in Excel |