X-Y Scatter Not Plotting Properly

[Agent Carmichael]

I have a basic spreadsheet that I'm trying to generate 2 X-Y scatter plots from. The first graph has four lines that all use a formula for what is basically an hour count, and everything looks fine. The second X-Y scatter plot, however, is supposed to use a specific column (R) for its x-axis values. It basically calculates a cumulative percentage based off of a hard-entered cell. The formula is:

=IF(O67>0,SUM($O$19:O67)/$U$2,"")

The problem is that when I go to the second graph, which is supposed to have its x-axis values taken from that formula, the x-axis labels are the same as the first graph. Moreover, the graphs look identical, so it's not actually plotting properly. The chart type is xy scatter, and when I go to "Select Data", the horizontal axis labels actually show up as they should, with the percentages from the formulated column. Has anybody experienced this? I'm out of solutions at this point...

Side note: I used that if/then statement because the spreadsheet was designed to have more cells than will likely be needed. Because I am not the one entering the data and the collection period is not pre-determined, I assumed that was the best way to set up the cells being used for the graphs.

 

[Jon Peltier]

First, note that the "" in the formula may look blank, but it's not going to chart properly. If the cell containing the formula is part of the plotted data, the "" is text, and so it is interpreted as 0, and if it's part of the X values, it messes them up, so Excel will revert to plotting the data as if the X values are 1, 2, 3, etc.

See if this works better:

=IF(O67>0,SUM($O$19:O67)/$U$2,NA())

This puts #N/A in the cell, which doesn't look all that attractive, but doesn't mess up the chart.