Chart Scale

[Klo]

I have a scatter chart that consists of 256 data points. Excel labels the X axis as 0 thru 300. Actually each data point should be spaced 1.758 units apart to give a range of 0 thru 450. How can I change this?
Thanks

 

[SkipVought]

Klo,

Please post a sample of your source data.

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[Klo]

Here it is.

000,000,000,000,000,000,000,000,000,000,000,000,000,000,111,094,077,065,055,048,044,044,044,047,049,051,054,057,059,060,060,0

60,061,063,061,060,058,056,053,050,047,043,041,038,035,033,030,028,026,025,024,022,021,020,019,018,018,016,016,016,015,014,01

3,012,011,010,009,008,008,007,006,006,005,005,005,004,004,004,004,003,003,003,003,003,003,003,003,003,003,003,003,003,003,003

,003,003,003,003,003,004,004,004,004,004,005,005,005,005,006,005,006,006,006,006,006,006,006,007,007,008,008,008,008,009,008,

008,009,008,008,008,009,009,009,009,009,009,009,010,009,010,011,011,011,011,012,012,012,011,011,011,012,012,012,012,012,012,0

13,013,012,012,012,013,013,013,012,013,013,014,013,014,014,014,014,014,014,014,015,015,014,014,014,015,015,015,015,015,015,01

5,015,014,013,013,012,011,011,012,012,012,013,013,013,014,014,013,013,013,013,013,012,012,012,012,012,012,012,012,012,012,012

,011,011,011,010,010,010,009,009,008,008,008,008,008,008,008,008,007,007,007,007,006,006,006,006,006,006,005,005,005,005,005,

004,000,000,000,000,000

 

[SkipVought]

Where are you x-values (0,1.758,3.516,5.274,7.032,8.79,10.548,12.306,14.064,15.822,17.58,19.338,21.096,22.854,24.612,26.37,28.128,29.886,31.644,...)? I can fully understand why Excel ASSUMES a CATEGORY axis of one per value.

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[Klo]

Hi Skip,
OK, here's the whole story. I developed a LIS (Laboratory Information System) for two of the three lab machines at a doctor's office using access 2000. The third machine in addition to the normal data also has three graphs. The data I posted was the WBC (white blood count) data as it comes from the machine. I would like to create the graph in access but it seems the I am limited to 6 data points so I guess I need to create the graph in excel and embed it in an access report. (I could VERY easily be wrong in this.)From your post I am also guessing that I will have to tell excel what the x-values are? (these will never change for the WBC graph) How do I do this?

 

[SkipVought]

I assume that you're doing this in VBA.

With ActiveChart.SeriesCollection(1)
  .XValues = XValuesArray
  .Values = YValuesArray
End With

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[Klo]

No. Since I don't know a lot about excel or Charts (but I'm learning!) I just imported the data into excel and used the chart wizard to make the chart. I guess I'm not going to get off that easy. I also havent needed to use arrays in vba yet. Am I right in the 6 data point limit in access? What would you suggest?

 

[SkipVought]

How are you assigning the values to the y axis now?

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[Klo]

I just imported the data into an excel spreadsheet. This created 256 columns (or a row with 256 entries). The chart wizard chose this data as the y-axis values.

 

[SkipVought]

Well in row 2...
[tt]
A2: 0
B2: =A2+1.758
[/tt]
and copy B2 across.

Plot these two as x-y scatter

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[Klo]

OK. That will work with the WBC patient data I imported this time but what about when I import the next set of WBC patient data? Will I loose the x-axis data? Can I put the x-axis data in another spreadsheet and link the two so that the x-axis data always stays the same?

 

[SkipVought]

Tell me about the PROCESS of importing your data into your system (Access or whatever). You ought to be able to handle this data ONE TIME and then use it an iny number of ways, including plotting on a chart.

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[Klo]

Hummm. That might work. When I imported the data to excel (using a .txt file) excel seems to figure that all of the data is for the y-axis. How does excel tell the difference between the x and y data during import?

 

[SkipVought]

You can have the x-axis and y-axis data on separate sheets.

If you activate the chart, right click and select Source Data-Series Tab, this is where the x & y ranges are defined. Just clear the contents of either and with your cursor IN the TEXTBOX, select the data range on whatever sheet, and the correct reference will be recorded.

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[Klo]

Thanks Skip. That works great. Now I need to do the same using VBA.

 

[SkipVought]

Have you turned on your macro recorder?

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[Klo]

Just did that. Excel seems to work a little different from access but close enough that I think I can figure it out. The hardest part of this project next to formatting the data ( luckily I hace WinBatch) is automating everything and making it as idiot proof as I can.

 

[Klo]

Well I guess I spoke too soon. I can create the chart manually OK and even record a macro, but when i try to run the macro I get error 13, Type Mismatch on ActiveChart.SetSourceData Source:=Sheets("WBC Data").Rows("1:1"), PlotBy:= _
xlRows.
Any ideas?

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 08/30/2004
'

'
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Sheets("WBC Data").Rows("1:1"), PlotBy:= _
xlRows
ActiveChart.SeriesCollection(1).XValues = "='WBC X-Axis'!R1"
ActiveChart.SeriesCollection(1).Name = "=""WBC Histogram"""
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="WBC Histogram"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "WBC Histogram"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Femtoliters"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Lysate-Modified WBC"
End With
ActiveChart.HasLegend = False
End Sub