Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
A mind of its own?
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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
A mind of its own?
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 _______ "JohnH" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
A mind of its own?
On Thu, 14 Jun 2007, in microsoft.public.excel.charting,
JohnH said: 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). 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. 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. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Had to enter data individually
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 |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
A mind of its own?
That must have been the problem, but I tried to reformat them as numbers
without decimal points Thanks a lot "Jon Peltier" wrote in message ... 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 _______ "JohnH" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
A mind of its own?
I try to get a point of comparison for any particular car I might buy. Most seem to follow a curve with a steep drop from new and then tail off. I haven't used it yet but I have to buy a car for my wife. The idea is to get as close as possible to (0,0) (brand new and free). I found sucessful bid data from a car auction site which I may attend tonight, and I have also looked at www.trademe.co.nz where there are a lot of dealers advertising. I also consult the Dog And Lemon guide www.dogandlemon.com.
John "JohnH" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
Had to enter data individually
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/ "JohnH" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
Had to enter data individually
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/ "JohnH" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.charting
|
|||
|
|||
Had to enter data individually
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/ "JohnH" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.charting
|
|||
|
|||
Had to enter data individually
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 "JohnH" wrote in message ... 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/ "JohnH" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.charting
|
|||
|
|||
Had to enter data individually
On Fri, 15 Jun 2007, in microsoft.public.excel.charting,
JohnH said: Is their no way to change text to numbers other than re-enter? 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. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Graph with a mind of its own | Excel Discussion (Misc queries) | |||
Mind Gone Blank!! | Excel Worksheet Functions | |||
Mind Gone Blank!! | Excel Discussion (Misc queries) | |||
VBA Conditional Formatting has mind of it's own! | Excel Worksheet Functions | |||
Help - Loosing my settings and my mind. | Excel Discussion (Misc queries) |