Share via

Excel chart not plotting xy values correctly

Anonymous
2022-05-13T12:08:19+00:00

Hello,

I have a Workbook in Excel 2016 on Office 365 (I think this is correct, but there is no Help > About to tell me).

I have several xy charts set up to use static referenced cells such as:

=Series('Site'!$C$189,'Site'!$C$190:$C$269,'Site'!$D$190:$D$269,1)

I have a worksheet with lots of data structured with columns for each site and rows that are dates. Another worksheet is used to plot the data for one site at a time. By changing a single cell on the Site worksheet the data is updated to the data for that site. This process works and has been checked.

The site worksheet has several xy charts with auto scaled axes. I have provided examples below. To help explain the problem I have added a callout point label to the first point in the data series on two separate charts. This is set to show the x and y values for the point.

The four plots below show the same two charts for 2 different sites.

The top row of charts shows that the xy label is linked to a point that is plotted in the right place according to the axis labels. For the second site this is not the case. The point is plotted in a completely wrong location. More confusing is that the bottom right plot appears to use the wrong salinity but the correct temperature.

In all cases the xy values in the label are the same values as in the data on the worksheet range that is being plotted. If I hover over a point then the tool tip information is correct for the first site, but for the other site it is incorrect (see screen shot below other charts). The Tool Tip appears to name the point with the correct name, but the data is not the data from the worksheet.

I first spotted this because the bottom right plot should have salinity values of 0.2 to 0.4 and I noticed the axis were going up to 60.

It seems Excel is plotting completely different data to that which it is being passed.

I have tried to:

* Repair the workbook: no repairs identified.

* Force recalculation: no difference

* Used VBA to force a refresh of the chart: no change

* Used VBA to DoEvents: no change

* Create a new chart linked to the same range: problem persists with the new chart

What is Excel doing and how do I resolve this?

Thank you.

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-05-13T12:58:50+00:00

    Re: "Series('Site'!$C$189,'Site'!$C$190:$C$269,'Site'!$D$190:$D$269,1)"

    Some General Chart Information...
    A chart series formula has four parts:
    Name of series - Category labels - Data for the series - Series number.
    The four parts are separated by commas.
    All sections (parts), of the formula, may not have data but the commas

    are still required.

    '---

    Nothing Left to Lose https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

    0 comments