A mind of its own?

J

JohnH

I'm trying to express this data as a simple scatter graph with points. It is for analysing car sales so I can bid at an auction. Usually when I try this it makes a scattering of data Price (Y), Km (X).. This time it groups it around a series of points which is meaningless to me.

There are 30 entries ranging from x 17,000 to 250,000 and (y) 4770 to 7170.

The Series entries are Name A,
=A!$B$3:$B$33
=A!$C$3:$C$33
This is the data:
92000 5470
55655 6470
88060 5020
17000 7170
82000 5920
94000 5570
96000 5600
64000 5570
91292 5720
114000 5020
89401 4770
90900 5470
114000 5270
250000 6470
97000 6520
83135 6420
57000 6570
31040 6420
114000 6120
79060 6670
92000 5470
90200 6620
84062 6620
118000 5470
97000 6520
92751 5170
113000 5570
114000 6020
66406 7120
85000 6320
78177 5870


What's going on here??????
Thanks John
 
J

Jon Peltier

John -

Do any of the cells contain non-numeric values, that is, they look like
numbers but are formatted as text?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



I'm trying to express this data as a simple scatter graph with points. It is
for analysing car sales so I can bid at an auction. Usually when I try this
it makes a scattering of data Price (Y), Km (X).. This time it groups it
around a series of points which is meaningless to me.

There are 30 entries ranging from x 17,000 to 250,000 and (y) 4770 to 7170.

The Series entries are Name A,
=A!$B$3:$B$33
=A!$C$3:$C$33
This is the data:
92000 5470
55655 6470
88060 5020
17000 7170
82000 5920
94000 5570
96000 5600
64000 5570
91292 5720
114000 5020
89401 4770
90900 5470
114000 5270
250000 6470
97000 6520
83135 6420
57000 6570
31040 6420
114000 6120
79060 6670
92000 5470
90200 6620
84062 6620
118000 5470
97000 6520
92751 5170
113000 5570
114000 6020
66406 7120
85000 6320
78177 5870


What's going on here??????
Thanks John
 
D

Del Cotter

I'm trying to express this data as a simple scatter graph with points.
It is for analysing car sales so I can bid at an auction. Usually when
I try this it makes a scattering of data Price (Y), Km (X).
Click to expand...

What interesting features do you usually find in this scatter?
This time it groups it around a series of points which is meaningless
to me.
Click to expand...

I'm not sure I understand what you mean by this sentence.

Have you tried excluding the point which is 250,000 km but only $6,470?
It seems to be an outlier, and maybe having to display that point on the
graph makes all the others look like they're clustered too closely for
you to read.

Try setting the upper limit of the x scale to 120,000, and see if that
pattern looks more familiar to you.
 
J

JohnH

I fixed it by copying the entries individually to each cell. Why would that be????

Another question: How do I Arrange a column in desecending order (lowest to highest value while retaining the corresponding value in the next column?
Thanks
John
 
J

JohnH

That must have been the problem, but I tried to reformat them as numbers
without decimal points
Thanks a lot
 
D

David Biddulph

It looks as if you originally had numbers in some cells and text in others. Spaces and other non-printing characters can cause values to be treated as text when you intended them to be numbers. If you've got default alignment a tell-tale sign is that text is usually aligned to the left and numbers normally to the right, and that is what the example you posted looks like. =ISTEXT() and =ISNUMBER() can help you to tell, and another symptom is that if you try to change the number format (such as changing the number of decimal points displayed), then it won't affect text cells.

On the sorting question, select the range you want to sort, choosing the rows & columns you're interested in, then Data/ Sort/ Sort by whichever column. You'll have to choose between ascending and descending, but I think you'll find that descending goes from highest to lowest, not lowest to highest as you've described it.
--
David Biddulph
Rowing web pages at
http://www.biddulph.org.uk/
I fixed it by copying the entries individually to each cell. Why would that be????

Another question: How do I Arrange a column in desecending order (lowest to highest value while retaining the corresponding value in the next column?
Thanks
John
 
J

JohnH

Thanks for that, I'll keep a record of it. The car we were going to bid for was withdrawn, as somebody bought it before the auction.
John

"David Biddulph" <groups [at] biddulph.org.uk> wrote in message It looks as if you originally had numbers in some cells and text in others. Spaces and other non-printing characters can cause values to be treated as text when you intended them to be numbers. If you've got default alignment a tell-tale sign is that text is usually aligned to the left and numbers normally to the right, and that is what the example you posted looks like. =ISTEXT() and =ISNUMBER() can help you to tell, and another symptom is that if you try to change the number format (such as changing the number of decimal points displayed), then it won't affect text cells.

On the sorting question, select the range you want to sort, choosing the rows & columns you're interested in, then Data/ Sort/ Sort by whichever column. You'll have to choose between ascending and descending, but I think you'll find that descending goes from highest to lowest, not lowest to highest as you've described it.
--
David Biddulph
Rowing web pages at
http://www.biddulph.org.uk/
I fixed it by copying the entries individually to each cell. Why would that be????

Another question: How do I Arrange a column in desecending order (lowest to highest value while retaining the corresponding value in the next column?
Thanks
John
 
J

JohnH

Is their no way to change text to numbers other than re-enter?

" =ISTEXT() and =ISNUMBER() can help you to tell," > I don't understand this.
Thanks
John
"David Biddulph" <groups [at] biddulph.org.uk> wrote in message It looks as if you originally had numbers in some cells and text in others. Spaces and other non-printing characters can cause values to be treated as text when you intended them to be numbers. If you've got default alignment a tell-tale sign is that text is usually aligned to the left and numbers normally to the right, and that is what the example you posted looks like. =ISTEXT() and =ISNUMBER() can help you to tell, and another symptom is that if you try to change the number format (such as changing the number of decimal points displayed), then it won't affect text cells.

On the sorting question, select the range you want to sort, choosing the rows & columns you're interested in, then Data/ Sort/ Sort by whichever column. You'll have to choose between ascending and descending, but I think you'll find that descending goes from highest to lowest, not lowest to highest as you've described it.
--
David Biddulph
Rowing web pages at
http://www.biddulph.org.uk/
I fixed it by copying the entries individually to each cell. Why would that be????

Another question: How do I Arrange a column in desecending order (lowest to highest value while retaining the corresponding value in the next column?
Thanks
John
 
D

David Biddulph

If you have a cell A2, and you don't know whether the cell is text or number, the functions =ISTEXT(A2) and =ISNUMBER(A2) will return TRUE or FALSE as appropriate.

If you've got text and you want to convert to numbers without retyping, there are a number of techniques which sometimes work. Data/ Text to columns might work, as might adding zero with Edit/ Paste special/ Add (or multiplying by 1 in equivalent manner). You may be able to get rid of some of the spurious spaces or non--printing characters with TRIM() and CLEAN() functions.
--
David Biddulph

Is their no way to change text to numbers other than re-enter?

" =ISTEXT() and =ISNUMBER() can help you to tell," > I don't understand this.
Thanks
John
"David Biddulph" <groups [at] biddulph.org.uk> wrote in message It looks as if you originally had numbers in some cells and text in others. Spaces and other non-printing characters can cause values to be treated as text when you intended them to be numbers. If you've got default alignment a tell-tale sign is that text is usually aligned to the left and numbers normally to the right, and that is what the example you posted looks like. =ISTEXT() and =ISNUMBER() can help you to tell, and another symptom is that if you try to change the number format (such as changing the number of decimal points displayed), then it won't affect text cells.

On the sorting question, select the range you want to sort, choosing the rows & columns you're interested in, then Data/ Sort/ Sort by whichever column. You'll have to choose between ascending and descending, but I think you'll find that descending goes from highest to lowest, not lowest to highest as you've described it.
--
David Biddulph
Rowing web pages at
http://www.biddulph.org.uk/
I fixed it by copying the entries individually to each cell. Why would that be????

Another question: How do I Arrange a column in desecending order (lowest to highest value while retaining the corresponding value in the next column?
Thanks
John
 
D

Del Cotter

Is their no way to change text to numbers other than re-enter?
Click to expand...

You can export the entire column to text and re-import using "Text to
Columns". The numbers you published here on Wednesday imported as all
numbers with no anomalies.
 
Top