# How else can you correlate data on Excel?

Asked by ScottyMcGeester (1393) December 16th, 2012

I have to correlate two sets of data on this graph I made for work. My boss is going to be out for a while, but he left a note on my work saying he wants me to correlate the data. Unfortunately, like I said, he won’t be around for a while so I can’t ask him exactly what he means.

We did an experiment where I recorded data from these two machines. What he wants to do is kind of hard to explain. See, one machine is old and the other is new. He wants to make a correlation between the two, like. . .to see how off one machine is compared to the other one. Something like that.

Like I said, I have two sets of data, these averages from numbers I recorded. I made a graph and both sets are linear. I put the linear equation thing and the R squared value for both sets on the graph. I thought that was what he meant at first. I also thought he meant to do the ”=correl(enter cells here)” command and post that value there. But that wasn’t what he meant either – he had still told me that I needed to make a correlation.

I think whatever it is he means is a correlation on the actual graph, or a separate graph that calculates the correlation? I don’t know. What other stuff with correlation can you do on excel graphs?

Observing members: 0 Composing members: 0

Response moderated (Spam)
Response moderated (Spam)

Can the data be paired, as in, do you have readings from both machines on the same samples? If yes then yo can plot the dat using Y vs X where Y is the new machine and X is the old.
For Excel 2007:
Write the data in two columns. Make the top entry of each column be the heading “Old” “New” Then highlight the two columns of data.
Click “Insert”
Click “Charts” Or “Scatter” if your tool bars shows it.
Click “Scatter with smooth lines.”
You now have your chart. Now play with the other options to make it pretty.
Good luck.

LuckyGuy (33361)

^ That.
Unless he literally just wants to line up X and Y and be able to see the differences. That’s a simple sort-and-highlight job. Are you overthinking this?

Seek (34176)

Well, see I HAVE the graph. I already made the scatter graph. Each set of data has their points and the two lines run next to each other. He liked how it looked. So the graph itself is fine. I just don’t understand what he means when he kept writing “Correlate the data” on every rough draft I’ve given him. (I’ve given him two. The first the graph was okay, then he said to correlate the data, so I thought he meant to add the equation of the line and the R squared value, but he still said I needed to correlate the data)

Yeah. I’m thinking he wants a sort and highlight.

Would it be unthinkable to just say “Hey, boss, wtf do you mean by ‘correlate’? Throw me a bone, here”.

Seek (34176)

Well I’d like to find out something else I could do before he gets back. He left rather abruptly and forgot to tell me he won’t be back until this Thursday. I don’t want him to think I was just sitting here twiddling my thumbs all week.

You wrote “Each set of data has their points and the two lines run next to each other.”

If the correlation plot was New vs old I would expect there to be just one line. X axis is old and y axis is new. Not two lines

LuckyGuy (33361)

@LuckyGuy Er. . well. . it’s complicated. I hope I wouldn’t have to explain the experiment. But I guess I have to in order for people to help.

These two machines record conductivity. We wanted to see how they would record the conductivity of Sodium Chloride Solutions at different concentrations.

So the graph looks like this:

X axis is the rise in concentration. Y axis is the amount of conductivity (expressed in a certain unit)

So what I mean to say is, there’s two series of data. One is the old machine and the data it recorded, and the other is the new machine and the data it recorded. I plotted them out on a scatter graph and plugged in the trendline, which is linear. So it’s showing that the higher the concentration, the greater the conductivity. (X and Y axis respectively)

We wanted to see how far off both machines are in recording the data.

Do you understand now?

or