June 12, 2013 7:23 AM Subscribe

Math-filter: I have a line chart in Google Docs that plots three columns of data over time. Data in the first two columns ranges from 0-1000, but data in the third column ranges from 3-9. I'd like to "scale" Line 3 (maybe this is the wrong word?) to be able to compare its value over the same graph space as Lines 1 and 2.

I think what I need to do is create a new, "scaled" column and plot that as Line 3 instead. Thus, in this scaled column, I believe 9 unscaled would be the same as 1000 scaled, and 3 would be the same as 0. Since this is a spreadsheet, I need something in the form y=*f*(unscaled Column 3 value).

That's all I've got, though, after half an hour of trying to remember my algebra, and then another fifteen minutes playing with Wolfram Alpha. Am I on the right track here? What's the function to scale Line 3?
posted by electric_counterpoint to Science & Nature (7 answers total)

I think what I need to do is create a new, "scaled" column and plot that as Line 3 instead. Thus, in this scaled column, I believe 9 unscaled would be the same as 1000 scaled, and 3 would be the same as 0. Since this is a spreadsheet, I need something in the form y=

That's all I've got, though, after half an hour of trying to remember my algebra, and then another fifteen minutes playing with Wolfram Alpha. Am I on the right track here? What's the function to scale Line 3?

Try using a secondary y axis (often drawn on the right hand side) for the third data series. (I haven't used Google docs very much, so I don't know which buttons to push.)

posted by ceribus peribus at 7:28 AM on June 12, 2013

posted by ceribus peribus at 7:28 AM on June 12, 2013

Option 1, "normalize" all three columns, i.e. rescale them all such that they range from 0 to 1:

plot y1/1000, y2/1000 and (y3-3)/6

Option 2, rescale y3 to the range and zero point of the other two columns:

plot y1, y2, and 1000*(y3-3)/6

posted by caek at 7:29 AM on June 12, 2013

plot y1/1000, y2/1000 and (y3-3)/6

Option 2, rescale y3 to the range and zero point of the other two columns:

plot y1, y2, and 1000*(y3-3)/6

posted by caek at 7:29 AM on June 12, 2013

As follow up - I found this video: How to add a second Y axis in Google Spreadsheets [1:41]

Sorry for not answering the math question (the above answers look good), but there's no need to resort to doing this manually. It's what secondary axes were invented for, and any decent charting package should handle it for you.

posted by ceribus peribus at 7:49 AM on June 12, 2013 [1 favorite]

Sorry for not answering the math question (the above answers look good), but there's no need to resort to doing this manually. It's what secondary axes were invented for, and any decent charting package should handle it for you.

posted by ceribus peribus at 7:49 AM on June 12, 2013 [1 favorite]

For time I think it would be more usual to index each of the series, so that they're 100 at the initial t and subsequent values can be interpreted as % difference from the original (ie 95 means whatever value is 95% of the original value).

Possible complication for the variables that range from 0 to 1000. Assuming that's the actual data range, does it make sense to think of (and display) the variable in its natural scale, or would it make more sense to think of it (and display it) as logged values?

posted by ROU_Xenophobe at 8:25 AM on June 12, 2013

Possible complication for the variables that range from 0 to 1000. Assuming that's the actual data range, does it make sense to think of (and display) the variable in its natural scale, or would it make more sense to think of it (and display it) as logged values?

posted by ROU_Xenophobe at 8:25 AM on June 12, 2013

(obviously indexing like that would be teh stupidz if you're looking at growth processes that start at 0)

posted by ROU_Xenophobe at 8:26 AM on June 12, 2013

posted by ROU_Xenophobe at 8:26 AM on June 12, 2013

If you're going to create a scaled column, you can go one extra step and use Min and Max functions, rather than typing 3 or 9 or whatever numbers into your formula. That way, if your source data changes, your scaled column and your graph will adjust automatically.

posted by RobinFiveWords at 3:09 PM on June 12, 2013

posted by RobinFiveWords at 3:09 PM on June 12, 2013

This thread is closed to new comments.

=(1000/(9-3))*(C1-3)

posted by EndsOfInvention at 7:27 AM on June 12, 2013