Is it possible to plot discontiguous cells?

[Peter Bernadyne]

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

 

[Jean Ruch]

"Peter Bernadyne"
im Newsbeitrag

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

 

[Peter Bernadyne]

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

 

[Jean Ruch]

"Peter Bernadyne"
im Newsbeitrag

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]

= 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

 

[Peter Bernadyne]

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

 

[Jean Ruch]

"Peter Bernadyne"
im Newsbeitrag

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

 

[Peter Bernadyne]

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

 

[Jean Ruch]

"Peter Bernadyne"
im Newsbeitrag

I finally got it, now it works beautifully:

Hi Pete,

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

best regards

Jean