When building a chart Dates are been included that are not in thedate range

[Sh0t2bts]

Hi All,

I have this data that I am trying to put into a scatter chart, there
are 4 colums A-D starting at row 1 to 367 I have the date entered in
column A from 1st Jan 2008 to 31st Dec 2009 entered as = Date
(2008,01,01) in cell A2 and A3 onwards is =A2+3, A3+1 etc etc, the
other 3 columns is random data from those dates.

When I select all the data range and press F11 to create a chart it
looks fine but when I right click and select scatter chart with smooth
line I get an X axis starting November 2007, which isn't in the
range.

Any Ideas on what i'm doing wrong?

Kind Regards
Mark

A B C D
Date Flow Presure Loss
01-Jan-08 211.4 514.6 0.0
02-Jan-08 231.6 462.2 8.6
03-Jan-08 231.6 473.8 0.0
04-Jan-08 251.7 501.2 0.0
05-Jan-08 259.0 463.6 0.0
06-Jan-08 261.5 430.4 0.0
07-Jan-08 261.3 460.1 0.0
08-Jan-08 260.4 443.7 0.0
09-Jan-08 256.9 446.0 0.0
10-Jan-08 262.0 431.5 0.0
11-Jan-08 259.0 437.6 0.0
12-Jan-08 252.0 439.4 0.0
13-Jan-08 248.1 441.9 0.0
14-Jan-08 217.8 403.3 0.0
15-Jan-08 211.1 442.2 0.0
16-Jan-08 226.3 382.5 0.0
17-Jan-08 242.6 384.4 0.0
18-Jan-08 238.1 375.4 0.0
19-Jan-08 228.7 403.3 0.0
20-Jan-08 29.4 0.0 0.0
21-Jan-08 1.9 0.0 0.0
22-Jan-08 2.0 0.0 0.0
23-Jan-08 2.4 0.0 0.0
24-Jan-08 2.4 0.0 0.0
25-Jan-08 2.7 0.0 0.0
26-Jan-08 2.7 0.0 0.0
27-Jan-08 2.8 0.0 0.0
28-Jan-08 2.9 0.0 0.0
29-Jan-08 2.9 0.0 0.0
30-Jan-08 3.1 0.0 0.0
31-Jan-08 3.2 0.0 0.0
01-Feb-08 4.6 0.0 0.0
02-Feb-08 31.5 0.0 0.0
03-Feb-08 200.1 0.0 0.0
04-Feb-08 191.9 0.0 0.0
05-Feb-08 196.2 497.9 0.0
06-Feb-08 175.2 522.5 0.0
07-Feb-08 180.4 531.2 0.0
08-Feb-08 18.5 64.9 0.0
09-Feb-08 183.0 763.5 0.0
10-Feb-08 190.5 746.8 0.0
11-Feb-08 195.5 677.7 0.0
12-Feb-08 196.7 627.4 0.0
13-Feb-08 188.1 614.3 9.5
14-Feb-08 194.5 566.6 8.1
15-Feb-08 236.7 451.1 0.0
16-Feb-08 235.0 397.7 0.0
17-Feb-08 233.2 504.3 0.0
18-Feb-08 220.0 566.4 0.0
19-Feb-08 219.1 565.7 0.0
20-Feb-08 216.4 571.0 0.0
21-Feb-08 227.6 497.2 0.0
22-Feb-08 235.8 435.6 0.0
23-Feb-08 240.5 489.4 0.0
24-Feb-08 224.5 569.0 0.0
25-Feb-08 226.6 543.7 0.0
26-Feb-08 230.4 588.2 0.0
27-Feb-08 237.7 563.3 0.0
28-Feb-08 235.2 553.6 0.0
29-Feb-08 205.2 537.9 0.0
01-Mar-08 222.0 522.7 0.0
02-Mar-08 211.4 482.0 0.0
03-Mar-08 248.8 478.7 11.1
04-Mar-08 211.9 457.4 0.0
05-Mar-08 204.1 465.9 13.9
06-Mar-08 93.2 238.1 0.0
07-Mar-08 1.0 7.8 0.0
08-Mar-08 0.0 6.2 0.0
09-Mar-08 0.0 8.7 0.0
10-Mar-08 0.0 6.6 0.0
11-Mar-08 0.0 7.0 0.0
12-Mar-08 0.0 7.3 0.0
13-Mar-08 0.0 7.0 0.0
14-Mar-08 0.0 20.3 0.0
15-Mar-08 0.0 41.6 0.0
16-Mar-08 0.0 36.9 0.0
17-Mar-08 0.0 45.4 0.0
18-Mar-08 94.5 37.0 0.0
19-Mar-08 207.0 677.4 0.0
20-Mar-08 181.4 379.6 0.0
21-Mar-08 210.0 363.1 0.0
22-Mar-08 204.6 346.1 0.0
23-Mar-08 206.4 399.1 0.0
24-Mar-08 203.7 426.4 0.0
25-Mar-08 205.3 417.4 0.0
26-Mar-08 233.6 429.3 7.4
27-Mar-08 239.3 419.7 0.0
28-Mar-08 257.0 458.7 0.0
29-Mar-08 264.3 463.1 0.0
30-Mar-08 266.0 468.1 0.0
31-Mar-08 261.0 461.9 11.3
01-Apr-08 233.8 420.0 12.9
02-Apr-08 233.5 441.7 13.2
03-Apr-08 267.9 506.5 10.9
04-Apr-08 265.3 448.8 0.0
05-Apr-08 264.9 466.2 0.0
06-Apr-08 267.5 498.1 26.2
07-Apr-08 238.8 465.9 0.0
08-Apr-08 233.9 430.0 0.0
09-Apr-08 238.5 454.4 39.3
10-Apr-08 238.8 558.3 0.0
11-Apr-08 252.1 563.8 0.0
12-Apr-08 263.0 540.1 0.0
13-Apr-08 252.3 474.8 13.8
14-Apr-08 259.8 476.0 23.2
15-Apr-08 260.7 560.2 0.0
16-Apr-08 257.0 514.3 0.0
17-Apr-08 258.5 449.6 12.9
18-Apr-08 261.6 415.3 0.0
19-Apr-08 260.3 423.1 0.0
20-Apr-08 262.5 426.7 0.0
21-Apr-08 259.1 457.2 9.3
22-Apr-08 269.4 444.3 11.0
23-Apr-08 254.7 421.2 10.5
24-Apr-08 250.7 356.0 11.6
25-Apr-08 258.8 397.6 0.0
26-Apr-08 222.2 372.9 0.0
27-Apr-08 225.3 373.2 10.3
28-Apr-08 229.5 408.6 10.9
29-Apr-08 239.8 402.7 10.6
30-Apr-08 5.6 0.0 0.0
01-May-08 5.6 11.9 0.0
02-May-08 257.5 335.4 0.0
03-May-08 254.6 337.8 0.0
04-May-08 261.9 375.1 0.0
05-May-08 259.0 355.8 9.0
06-May-08 255.2 348.4 10.1
07-May-08 253.2 304.0 0.0
08-May-08 245.2 331.3 12.1
09-May-08 216.1 351.2 0.0
10-May-08 188.6 457.4 0.0
11-May-08 203.3 439.5 8.6
12-May-08 187.8 533.6 0.0
13-May-08 183.6 587.1 0.0
14-May-08 156.4 100.6 9.1
15-May-08 261.9 291.9 0.0
16-May-08 262.2 261.7 0.0
17-May-08 255.8 279.3 0.0
18-May-08 228.7 244.1 0.0
19-May-08 163.0 184.3 0.0
20-May-08 232.2 237.8 8.0
21-May-08 261.0 305.5 0.0
22-May-08 246.6 298.6 0.0
23-May-08 189.9 228.8 0.0
24-May-08 253.1 327.7 0.0
25-May-08 265.4 351.7 0.0
26-May-08 248.2 338.2 0.0
27-May-08 239.4 341.7 0.0
28-May-08 253.7 344.6 0.0
29-May-08 248.0 325.2 0.0
30-May-08 251.3 340.5 0.0
31-May-08 246.7 340.2 0.0
01-Jun-08 253.4 355.8 11.6
02-Jun-08 248.9 362.1 0.0
03-Jun-08 247.4 352.6 0.0
04-Jun-08 251.6 394.8 10.6
05-Jun-08 250.2 425.7 0.0
06-Jun-08 245.4 398.6 12.5
07-Jun-08 251.8 370.3 0.0
08-Jun-08 239.0 355.2 9.8
09-Jun-08 244.2 342.9 0.0
10-Jun-08 210.5 342.7 14.1
11-Jun-08 238.9 358.8 12.8
12-Jun-08 208.5 427.1 11.4
13-Jun-08 256.0 355.2 10.4
14-Jun-08 249.3 403.0 0.0
15-Jun-08 249.8 402.2 11.3
16-Jun-08 256.7 380.9 11.1
17-Jun-08 247.0 375.5 13.3
18-Jun-08 256.1 384.5 13.1
19-Jun-08 254.7 423.3 0.0
20-Jun-08 252.0 427.3 17.8
21-Jun-08 234.8 388.9 0.0
22-Jun-08 238.2 380.1 10.3
23-Jun-08 256.9 417.9 12.2
24-Jun-08 239.2 380.7 13.2
25-Jun-08 243.9 390.2 0.0
26-Jun-08 245.9 392.8 10.5
27-Jun-08 254.1 383.3 0.0
28-Jun-08 256.7 373.5 11.3
29-Jun-08 234.7 343.4 11.6
30-Jun-08 234.9 361.0 11.5
01-Jul-08 220.7 355.0 0.0
02-Jul-08 254.5 391.3 0.0
03-Jul-08 263.9 401.1 0.0
04-Jul-08 255.5 391.9 0.0
05-Jul-08 257.4 409.6 0.0
06-Jul-08 244.7 390.1 0.0
07-Jul-08 202.7 396.4 0.0
08-Jul-08 253.7 404.4 0.0
09-Jul-08 255.5 398.7 0.0
10-Jul-08 259.4 395.6 0.0
11-Jul-08 235.1 386.1 0.0
12-Jul-08 249.3 376.9 0.0
13-Jul-08 260.1 388.0 11.3
14-Jul-08 253.3 382.7 0.0
15-Jul-08 249.6 390.7 0.0
16-Jul-08 248.4 412.0 8.8
17-Jul-08 248.3 414.1 11.0
18-Jul-08 212.8 331.7 15.3
19-Jul-08 164.8 383.3 11.5
20-Jul-08 222.8 466.8 6.7
21-Jul-08 215.1 447.0 0.0
22-Jul-08 216.7 497.1 10.1
23-Jul-08 233.2 522.9 0.0
24-Jul-08 253.0 553.5 7.2
25-Jul-08 237.7 527.7 4.5
26-Jul-08 238.6 514.2 0.0
27-Jul-08 241.0 633.7 9.2
28-Jul-08 241.2 581.5 0.0
29-Jul-08 261.9 556.1 6.5
30-Jul-08 260.3 492.6 12.0
31-Jul-08 208.9 367.9 13.2
01-Aug-08 204.7 366.9 7.1
02-Aug-08 184.4 373.6 6.3
03-Aug-08 232.1 419.5 5.5
04-Aug-08 238.0 390.5 5.2
05-Aug-08 211.0 452.3 6.5
06-Aug-08 225.1 471.4 3.5
07-Aug-08 223.7 489.0 5.6
08-Aug-08 228.5 388.1 10.1
09-Aug-08 227.4 490.2 0.0
10-Aug-08 234.2 474.0 0.0
11-Aug-08 235.4 457.0 0.0
12-Aug-08 260.1 490.0 6.4
13-Aug-08 239.7 513.1 0.0
14-Aug-08 259.1 476.3 0.0
15-Aug-08 263.7 488.7 6.7
16-Aug-08 213.4 421.0 5.0
17-Aug-08 240.5 421.4 2.5
18-Aug-08 236.2 394.6 7.3
19-Aug-08 249.7 449.2 5.9
20-Aug-08 253.1 497.1 8.5
21-Aug-08 259.1 517.6 10.0
22-Aug-08 257.6 586.1 9.1
23-Aug-08 259.6 567.9 0.0
24-Aug-08 247.9 522.0 10.2
25-Aug-08 206.4 451.1 0.0
26-Aug-08 250.9 538.5 10.9
27-Aug-08 251.6 516.7 0.0
28-Aug-08 238.2 521.1 8.7
29-Aug-08 222.9 516.3 0.0
30-Aug-08 221.0 545.7 0.0
31-Aug-08 235.1 610.8 4.5
01-Sep-08 233.7 526.9 8.0
02-Sep-08 252.5 595.1 5.9
03-Sep-08 250.0 493.9 0.0
04-Sep-08 249.3 481.4 0.0
05-Sep-08 245.7 453.1 0.0
06-Sep-08 202.4 429.4 8.2
07-Sep-08 190.0 440.1 10.0
08-Sep-08 194.1 446.3 3.7
09-Sep-08 194.4 318.9 8.0
10-Sep-08 212.6 399.0 5.7
11-Sep-08 210.8 363.9 7.2
12-Sep-08 181.8 323.2 0.0
13-Sep-08 155.3 140.1 0.0
14-Sep-08 155.3 162.4 0.0
15-Sep-08 169.1 160.1 0.0
16-Sep-08 2.6 55.4 0.0
17-Sep-08 3.0 12.2 0.0
18-Sep-08 2.3 12.0 0.0
19-Sep-08 2.4 12.0 0.0
20-Sep-08 2.4 5.5 0.0
21-Sep-08 2.5 5.6 0.0
22-Sep-08 2.7 5.5 0.0
23-Sep-08 2.7 5.8 0.0
24-Sep-08 3.8 5.5 0.0
25-Sep-08 3.5 6.8 0.0
26-Sep-08 3.6 5.6 0.0
27-Sep-08 2.6 5.6 0.0
28-Sep-08 3.4 7.6 0.0
29-Sep-08 2.7 5.8 0.0
30-Sep-08 3.5 5.5 0.0
01-Oct-08 4.0 5.5 0.0
02-Oct-08 5.8 6.8 0.0
03-Oct-08 5.1 5.5 0.0
04-Oct-08 5.2 6.2 0.0
05-Oct-08 4.9 5.5 0.0
06-Oct-08 3.3 10.2 0.0
07-Oct-08 3.5 5.6 0.0
08-Oct-08 3.4 5.6 0.0
09-Oct-08 3.6 5.6 0.0
10-Oct-08 4.5 5.6 0.0
11-Oct-08 64.0 57.5 0.0
12-Oct-08 168.3 85.4 0.0
13-Oct-08 168.4 85.6 0.0
14-Oct-08 175.2 181.0 0.0
15-Oct-08 191.0 229.2 0.0
16-Oct-08 3.4 11.9 0.0
17-Oct-08 3.6 12.0 0.0
18-Oct-08 2.6 12.1 0.0
19-Oct-08 2.6 12.1 0.0
20-Oct-08 2.8 12.1 0.0
21-Oct-08 6.9 11.9 0.0
22-Oct-08 235.2 341.8 21.0
23-Oct-08 201.6 433.2 22.6
24-Oct-08 251.3 401.1 10.6
25-Oct-08 260.8 369.2 0.0
26-Oct-08 228.1 341.9 20.4
27-Oct-08 210.0 377.8 23.0
28-Oct-08 209.5 380.3 24.2
29-Oct-08 206.6 332.2 26.0
30-Oct-08 216.5 325.7 0.0
31-Oct-08 179.7 313.6 29.0
01-Nov-08 197.1 341.2 23.3
02-Nov-08 201.4 312.0 19.5
03-Nov-08 182.6 272.5 27.7
04-Nov-08 180.2 298.5 21.1
05-Nov-08 189.5 326.3 22.9
06-Nov-08 184.1 320.7 26.7
07-Nov-08 194.9 351.8 25.4
08-Nov-08 194.0 347.0 29.6
09-Nov-08 190.5 400.9 26.0
10-Nov-08 194.7 359.0 21.7
11-Nov-08 200.4 389.1 21.2
12-Nov-08 198.3 373.3 19.7
13-Nov-08 178.1 346.1 27.6
14-Nov-08 115.6 287.3 20.1
15-Nov-08 185.4 333.3 19.1
16-Nov-08 219.2 372.5 16.5
17-Nov-08 233.7 367.6 0.0
18-Nov-08 174.7 339.5 0.0
19-Nov-08 49.0 63.0 0.0
20-Nov-08 190.7 446.5 0.0
21-Nov-08 188.4 586.1 20.6
22-Nov-08 187.0 475.4 15.9
23-Nov-08 196.2 432.7 14.0
24-Nov-08 224.8 435.3 15.8
25-Nov-08 211.3 461.2 11.9
26-Nov-08 236.3 572.4 7.2
27-Nov-08 254.0 645.9 0.0
28-Nov-08 248.6 581.8 0.0
29-Nov-08 247.3 469.1 0.0
30-Nov-08 235.5 508.6 0.0
01-Dec-08 234.9 473.7 7.8
02-Dec-08 229.6 499.6 0.0
03-Dec-08 196.7 333.9 0.0
04-Dec-08 212.2 464.5 0.0
05-Dec-08 189.4 410.0 15.1
06-Dec-08 202.8 371.0 0.0
07-Dec-08 219.1 396.9 0.0
08-Dec-08 217.6 400.2 16.0
09-Dec-08 218.7 374.0 8.2
10-Dec-08 182.2 344.8 16.2
11-Dec-08 207.4 427.3 0.0
12-Dec-08 182.8 411.2 0.0
13-Dec-08 233.7 417.0 9.9
14-Dec-08 233.5 455.4 3.0
15-Dec-08 206.1 493.5 12.3
16-Dec-08 205.6 464.4 14.2
17-Dec-08 203.0 429.4 18.7
18-Dec-08 202.6 443.3 6.6
19-Dec-08 203.8 416.5 0.0
20-Dec-08 203.0 430.5 9.9
21-Dec-08 208.0 396.9 16.8
22-Dec-08 223.1 405.3 14.1
23-Dec-08 211.1 356.7 12.9
24-Dec-08 236.2 436.0 10.8
25-Dec-08 232.2 485.3 0.0
26-Dec-08 239.4 516.5 0.0
27-Dec-08 248.0 517.6 18.7
28-Dec-08 250.2 497.8 12.7
29-Dec-08 241.2 465.2 11.9
30-Dec-08 249.5 492.3 13.7
31-Dec-08 246.7 492.1 4.0

 

[David Biddulph]

It'll be because it's trying to put tick marks on the chart at round
numbers. Its first round number is39400, which is 14 Nov 07.

I wouldn't normally recommend changing from XY to line chart, but you might
find this helps. Change to line, then Chart Options/ Axes/ select
timescale, then Format Axis/ Scale and choose an appropriate tick mark
interval. But with line chart you won't be able to choose smoothed (but I
wouldn't normally choose smoothed anyway).

 

[p45cal]

You're probably doing nothing wrong - excel is trying top be helpful.


After you've changed to x-y scatter, right-click on one of the dates on
the x-axis and choose Format Axis... then:

In xl2000-2003 choose the Scale tab and enter 39448 in the (X) axis
Minimum field, and type 39813 in the Maximum field below that. The ticks
in the checkboxes should/will disappear. Click OK.

In Excel 2007, make sure 'Axis Options' is selected in the left pane,
click the two top 'Fixed' radio buttons and enter the two numbers used
above, and click Close.