Problem Overlaying Scatter Charts

[jkmoore]

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

 

[NdNoviceHlp]

Is this the thing about not having data in A1? See Jon's site here....
http://peltiertech.com/Excel/ChartsHowTo/VBAExcelXYCharts.html
HTH. Dave
ps. this could be relevant..."simply clear the top left cell of the data range"

 

[jkmoore]

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:
=IF([@[Wt(kg)]]<>"",IF($F4="",TODAY(),$F4),"")
The formula in the Chronological Age column is:
=IF([@[Wt(kg)]]<>"",INT((TODAY()-$C$4)/7),"")
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

 

[jkmoore]

And, now I can't even get the links to work. Sigh.

http://imageshack.us/photo/my-images/819/ptfdata.png/
http://imageshack.us/photo/my-images/830/ptdata.png/
http://imageshack.us/photo/my-images/689/graphij.png/

 

[NdNoviceHlp]

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

 

[jkmoore]

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)]]
=IF([@[Wt(kg)]]<>"",INT((TODAY()-$C$4)/7),"")
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?

 

[jkmoore]

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

 

[NdNoviceHlp]

Thanks for posting your outcome. I'm glad that your effort and perseverance has paid off. This link has some relevant stuff....
http://www.mrexcel.com/archive/Chart/16393.html
Dave