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"
<[email protected]> schrieb
im Newsbeitrag
news:[email protected]...

> 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.Value) = 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"
<[email protected]> schrieb
im Newsbeitrag
news:[email protected]...
>
> 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" <[email protected]> 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"
<[email protected]> schrieb
im Newsbeitrag
news:[email protected]...
>
> 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

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"
<[email protected]> schrieb
im Newsbeitrag
news:[email protected]...
>>
> Jean!
>>>
> I finally got it, now it works beautifully:


Hi Pete,

glad to hear that.
It was a pleasure for me.

best regards

Jean