Cannot display scatter graph of points with correct x-axis co-ordinates as they are in the dataset

English
#1

Hi there. I hope somoen can ofer aedvice on a simple but irritating little problem.

I have a set of very simple data showing the resistance of a thermistor as temperature is varied. It is a set of points of type (temperature (degC), resistance (kOhm)) with both as 2decpl numbers arranged in consecutive columns. I have had to strip these numerical values from original textual data which included three numbers with the LEFT(,) and Right(,) functions. I have made sure that the format of the cells containing the values is Number with 2dp selected.

I want to display this in a simple graph as a continuous plot of varying resistance against temperature. So it should simply be temperature=x-axis data values and resistance=y-axis data values. Temperature varies from about -10.5degC to about 43.9degC and resistance between 1.39k and 9.04k. That should give a neat plot with x-axis -15/+45degC and a y-axis of 0/10k.

I am able to display this data starting with just a scatter graph of the points for simplicity. However, the x-axis data values are ignored and each point’s x-value becomes a simple count of 1, 2, 3… denoting the number of that point in the set. Effectively the data for the temperature value of each point is ignored. While I have succeeded in getting the x-axis showing -15 to +45 with the y-axis located in the correct place at x=0, the points are all plotted within the +ve x-axis region as they should be with x-positions just matching the integral values of each point number.

I want to position each point on the x-axis with its x-value matching the temperature value in the set. Of course the y-value needs to stay as it is now, matching the resistance value. I would upload the spreadsheet to illustrate but I can’t see a way to do that here, neither can I attach screenshots either. If there is a way I am missing I would be happy to illustrate the problem and what I currently have set up.

Can anyone tell me how I can get each point to locate at the correct (Temp, Res) location? Thank you.

#2

With that you created textual data instead of numeric, which is not usable as X-values. Transform text to numeric using the NUMBERVALUE() function.

A display number format does not change the formula result from text to numeric.

#3

Thanks for that advice erAck. Just as you say, I assumed that in setting the format cell option for the whole of that column I would be converting it. I’ll try your advice and report back with the results.

#4

Yes, all is good now following your advice! As soon as you told me that it was still in text format it all became clear. I have now managed to strip out each of the values I want using LEFT and RIGHT, TRIM just to protect from any obscure spaces, then NUMBERVALUE each one with the correct format, I have it working. The graph is perfect. Many thanks for sharing your experience. If only we could start with a solid idea as to what was available in the functions it would be easier, but it wouldn’t be as much frustrating fun I suppose.

#5

This is a typical spreadsheet application from the 90ies. Any book like “Excel '95 for Dummies” will teach you the basics that are still valid for Excel 365 and Calc.

#6

There is a lot of information in Functions help

#7

Yes there is plenty of help available for less experienced or knowledgeable users in external books and in the Help but… (and I’m not whinging or moaning in any way here :wink:.)

I really wasn’t criticising Excel or Calc for lack of clarity in any way. It is also worth noting that I’m not a newbie as seems to have been assumed, I am an ex-programmer for a very high profile company working with military level software. I have written fairly advanced background stuff for Excel in the past - when it was needed. That doesn’t mean I know of every one of Excel’s myriad of predefined functions. People with experience can still have problems with finding simple information.

I’ve never had the need to use the majority of predefined functions before as my own Excel needs are pretty basic just as you have pointed out. That doesn’t mean I need to go and read up on what a spreadsheet is, what a cell is, what a formula is, etc. I’m certainly not an expert by any means as I don’t use it frequently to know all of its capabilities but I’m not a user devoid of any knowledge and ability with it.

I was only meaning that it is difficult to know what functionality is available when you can’t define it in a way you can use to search for the information. If I don’t know there is a NUMBERVALUE() function available, which I didn’t as I cannot remember ever having the need to use it before, how do I find it? I understand the concept of casting between variable types very well I just couldn’t see that that was what was needed. The idea did occur to me but I couldn’t find a way to test it which showed that was the problem.

Of course the concept of the stripped data being text and not a number was very low level and obvious when you have experience of it. But how do you approach it if you simply haven’t come across it before, (or maybe for a long time)? I can’t search for “function to change text to a number” in the Help. I’ve just tried and it sits and looks at me blankly. Are we to buy and read an entire “Dummies” book to find a mention of a single function, or the entire Help until we find that that function is available?

Anyway, thanks for the help guys, the solution was really simple once I knew what was the nature of the problem. string/numeric confusion didn’t seem to be at the root of it, until you pointed out that it did! :smile: That original advice was the piece of info I needed to sort it out very quickly.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.