Get graph and trendline to not display non-numerical values as zero

[SerenityNetworks]

I have a graph that draws information from two columns in a simple table. The information is drawn from cells that contains formulas, for example: =IF(COUNTA(F4:H4)>0,LN(E4),"-"). I've tried replacing the "-" with "", "x", and nothing, but the graph always picks up the non-numerical value and adjusts the trendline as if the values were zero. However, if I go to the table and manually delete the formulas from all the cells that do not contain numerical values then the graph's trendline displays correctly. Obviously, I want to leave the formulas in all the cells and still have the graph display as it would as if the non-numerical results were ignored. How can I either modify the formula or the graph to display without considering the non-numerical values in the source table?

Here is a link to a sample workbook that demonstrates the issue I'm having. The illustration below details the issue, if downloading the workbook sample is not an option.

Thank you in advance,
Andrew

 

[pgc01]

Hi Andrew

Since you are using formulas in the cells the cells will always have a value and in the case you have a string value you'll get the zero value in the chart, that you don't want.

You have several solution, you can use a vba solution, a worksheet solution or some mix of both.

For ex., If you want a worksheet solution you can define names to extract just the number values.
For ex., for the X axis, you define a XValues name that will only extract the values of the cells K6 and K14.
For the Y axis similar.
You'll then use those names in the Edit Series dialog.

I don't have time to test now, but you have difficulties post back and I'll post a solution tonight.

 

[SerenityNetworks]

Thank you very much. I didn't put it in the example workbook, but I'm currently using a VBA routine to delete the formulas from the non-numeric cells. I'm trying to avoid continuing with that solution, as (1) it's a kludge and (2) people that don't know me and want to use the workbook may be hesitant to enable macros. I tried your suggestion of defining names that extract the numerical values, but there is something I'm doing wrong. I would appreciate your help later in implementing that non-macro solution.

Thanks again,
Andrew

 

[pgc01]

Hi

I defined 2 named formulas, for the X and Y values.

Name: XValues
Scope: Performance Drop
=N(OFFSET('Performance Drop'!$K$4,SMALL(IF(ISNUMBER('Performance Drop'!$K$4:$K$22),ROW('Performance Drop'!$K$4:$K$22)-ROW('Performance Drop'!$K$4)),ROW(INDIRECT("1:"&COUNT('Performance Drop'!$K$4:$K$22)))),0))

Name: YValues
Scope: Performance Drop
=N(OFFSET('Performance Drop'!$K$4,SMALL(IF(ISNUMBER('Performance Drop'!$K$4:$K$22),ROW('Performance Drop'!$K$4:$K$22)-ROW('Performance Drop'!$K$4)),ROW(INDIRECT("1:"&COUNT('Performance Drop'!$K$4:$K$22)))),1))

and then in the Edit Series dialog I used for the data source of the series

Series X values:
='Performance Drop'!XValues

Series Y values:
='Performance Drop'!YValues

Please try.

 

[SerenityNetworks]

Wow! That is perfect. Thank you!

I understand the different functions you've combined, the value of adding Scope with the name, etc., but I don't yet understand how they all work together. I'm will need to study your solution a bit. Certainly, it's not something I could have devised. It is very much appreciated.

Thank you again,
Andrew

 

[Jon Peltier]

Simpler:

Change your IF statements from

=IF(something,something,"-")

to

=IF(something,something,NA())

This puts #N/A in the cell instead of "-", which is not plotted as zero, and which is ignored by the trendline.

If #N/A is too ugly for your worksheet, you can use conditional formatting to hide it. But you may learn to embrace them, as they provide a bit more insight than a blank cell or a dash.

 

[SerenityNetworks]

Thanks Jon. That's slick too. I never noticed throwing an error wasn't plotted, so I never thought of forcing one. Personally, I don't have a problem with #N/A, but others using the workbook likely would. If I use this method then I'll probably use conditional formatting to make them invisible.

Thanks again,
Andrew

 

[Jon Peltier]

I usually don't make them totally invisible, I just use a light gray font, so the actual numbers stand out.

 

[pgc01]

I'm glad it helped. ... and now you have other options.

 

[SerenityNetworks]

Actually, these two columns don't show any values useful to the user. I may place them elsewhere or just hide the columns, in which case #N/A isn't an issue anyway. Then again, the named ranges may be useful for other tasks I'm contemplating. At this point, I'm not sure which method I'll use. I do appreciate having options.