Problem Overlaying Scatter Charts


New Member
Nov 6, 2011
I'm trying to create pediatric preterm growth charts in excel such that the standard data resides in a smooth line scatter chart with the X axis equal to the corrected gestational age in weeks: 23, 24, ...,43 and the Y axis equals the weight in kg: 0.25...4.5. Plotted on this chart are 7 series of data representing the standard percentiles 3.....97. This part has worked fine.

The problem: need to add the child's growth data in overlay--age and weight.
Data is collected at serial visits
So need to plot: at 30 weeks baby weighed 3.26 kg (or whatever)
then baby gets weighed again a week later so at 31 weeks, wt= 3.50 kg

No matter what I do, I can't make the X axis match between the two charts.

Excel wants to make the baby's actual data such that the data point: 30,3.26 plots as "data point" 30 with the number 1 corresponding to that point on the X axis instead of 30.

I've tried it as a line chart with markers, I've tried it as a scatter chart with lines.

The worst part is that I've been able to do this easily for term babies with overlaying scatter charts.

Thanks, Kathy

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
I'm using Excel 2010.

I don't have any data in A1 on any worksheet.

I think it might have something to do with the formula in the "corrected age" cell/range. I have been able to create the chart in a test workbook that works perfectly. But, for the life of me, I can't make it work in the real workbook and I don't know why. I've double checked all the formulas. I've checked cell formats. Everything looks exactly the same. I've created the overlay patient data chart before putting the formulas in, after, etc. I put dummy data in the patient data chart prior to creating the overlay chart so it's not that I've been trying to create a chart without plotting points.

The data table for the percentile chart (smooth line scatter) looks like this:

It makes a percentile chart that looks fine with the corrected age in weeks on the X-axis and the patient weight in kg on the Y-Axis:


The patient data entry page looks like this:


The EGA(weeks) data is manually entered based on the baby's gestational age.

The formula in the Date column (which isn't used in the overlay chart) is:
The formula in the Chronological Age column is:
The formula in the Corrected Age Column is:
=IF([@[Wt(kg)]]<>"",$C$6+[@[Chronological Age(Wks)]],"")

To create the patient overlay chart, I entered data in the patient chronological age and wt columns. I opened the percentile chart and clicked it, then Chart Design>Select Data Set> Add>
Series Name: Patient Data
Series X Values: Chronological Age column
Series Y Values: Wt(kg) column

But no matter what I do, the chart creates a point called eg. "32" at coordinates (1,2.5) when I need it to create a point called "I don't care" at coordinates (32,2.5)

I could sure use some help re: what I'm doing wrong. Thanks, Kathy
Upvote 0
Sorry not much help. It's suspicious that it's actually the x=1 coordinate that's reversed. Did you check the link and my ps? It doesn't have to be A1. Beyond that I have no suggestions other than to rebuild the wb in the trial wb that does work. Dave
Upvote 0
Sorry, it's been a crazy day. Yes, I did check your link etc. and reviewed the steps I'd been taking.

Created the chart age following those steps carefully--Chart plotted points at coordinates (1,2.5), (2,2.75) etc

So, I deleted formulas from "Corrected Age" cell/range and started over, again, following the steps from the link. PERFECT chart. Worked, updated, just like anticipated. Made a few adjustments to the axes. Tested it again. All as expected.

Put the formula back in the cell range. Points disappeared and chart broken again

So, started over without formulas. Chart worked.
Added formula for Corrected Age: =EGA+Chronological Age as follows:
=$C$6+[@[Chronological Age(Wks)]]
where [@[Chronological Age(Wks)]]
and $C$4=Patient's Date of Birth
Chart worked perfectly

Added following to remove #VALUE in cells in which future data would reside:
=IF([@[Wt(kg)]]="","",$C$6+[@[Chronological Age(Wks)]])
#VALUE disappears as expected and chart breaks---back to plotting as (1,2.5),(2,2.75) instead of (24,2.5), (28,2.75).

So, why would IF function break the scatter plot?
Upvote 0
Problem Solved!!!

In the corrected age formula instead of "" I changed to this:
=IF([@[Wt(kg)]]<>"",$C$6+[@[Chronological Age(Wks)]],NA())

The chart works!

It's not pretty because now the table has N/A in all the empty fields. But it works.

Will try some conditional formatting to hide the N/A

Thanks for your help!!!! Kathy
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back