Find solution
How to create column chart with Google charts?

Scale my function for a line graph

  • 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?

  • Answer:

    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

electric_counterpoint at Ask.Metafilter.Com Visit the source

Was this solution helpful to you?

Other answers

As follow up - I found this video: http://www.youtube.com/watch?feature=player_embedded&v=BfQpLvl6tpw 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.

ceribus peribus

If C1 contains the value between 3 and 9, then in the "scaled" column you can enter: =(1000/(9-3))*(C1-3)

EndsOfInvention

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.)

ceribus peribus

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?

ROU_Xenophobe

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

ROU_Xenophobe

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.

RobinFiveWords

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.