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

"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

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

"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

"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

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