Chart range that skips non-numerical values

[labratto]

Hi all,
I am trying to make a chart that skips points that are not numbers. For the x-axis my values are coming from this named range formula (paintMSdatapointnumber) which shows the last specified number of values.

Please Login or Register  to view this content.

I thought with the count function it would skip the "" cells, but it appears that is not the case. The cells that appear blank have formulas in them that result in "" if there is no data entered into the parent cells.
The y values are the above names range with the column offset.

Please Login or Register  to view this content.

I attached the sheets.
Can I make this work, or should I start over with a different strategy?

[Glenn Kennedy]

Unfortunately, the way you have saved the file, some of the references are external to files which are not present. So our ability to do anything is limited.

If you are trying to get Excel to skip blanks.... make the formula return the #N/A error instead of a blank: =IF(X="",NA(),Y) so you get #N/A returned. If you don't want to see all those ugly errors, you can always use conditional formatting to give them a white font colour.

However, as it stands, I can't muck around with the sheet. I don't know where the data are/don't have access to the data.

[labratto]

Ah sorry about that. I think I fixed the references for the ranges, and deleted all the extras.

[labratto]

I don't mind making the it display #N?A, however with the my current formula for numbering if one cell displays #N/A, all subsequent cells below also display #N/A. Any idea for a workaround on that?

Please Login or Register  to view this content.

[Glenn Kennedy]

This appears to be the same file as the first time. Where are the #N/A errors? The charts are looking for a file on your S drive as the source...

You really need to ttell us where we are supposed to be looking. You have 00s of rows of data.

[labratto]

My apologies, did I get all the incorrect references out this time?

I did not save the version where I told it to give the G column an #N/A. But if you change the formula in the G column from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

then all the values under the first #N/A are returned as #N/A

The MS % Recovery Chart is taking x-axis labels from G669:G767 and Y values from K669:767. The formulas for the x and y ranges are named.

[JeteMc]

Try:

Please Login or Register  to view this content.

Let us know if you have any questions.