0

I'm trying to plot a chart that includes one data set in billions, another in millions, and a third in thousands. What is the most efficient/elegant way to do this using google sheets without the larger data set making the smaller ones look invisible?

enter image description here

Improve this question

1 Answer 1

Reset to default
0

To make this easier, Insert > New sheet where you can scale the population to billions and incidence figures to millions using these formulas:

={ Sheet1!A2:A }

=arrayformula( if( isnumber(Sheet1!B2:B), Sheet1!B2:B / 10^9, iferror(1/0) ) )

=arrayformula( if( isnumber(Sheet1!C2:C), Sheet1!C2:C / 10^6, iferror(1/0) ) )

={ Sheet1!D2:D }

Use two Y axes where the max value in the left axis differs from the max value in the right axis, like this:

chart with two Y axes

See the new 'Scaled data' sheet in your sample spreadsheet.

Improve this answer
5
  • Thanks, I tried this but the data set that is in billions was still too large - and the smaller data sets were smothered.
    – verve88
    Apr 15, 2021 at 14:37
  • Edited the answer to show how to scale the data and use two Y axes.
    – doubleunary
    Apr 16, 2021 at 8:39
  • Thank you immensely, this 100% solves the problem. Scaling makes a lot of sense for this kind of data range. Appreciate it!
    – verve88
    Apr 16, 2021 at 9:33
  • What a cold, cold community where a "thank you" is not allowed according to the rules! I've "accepted" your answer as per the instructions. I will rebel in saying thanks again for taking the time to rebuild, and explain my table dilemma. Kindly, verve88.
    – verve88
    Apr 17, 2021 at 16:39
  • Appreciate that @verve88. Stack Exchange is really a wiki rather than a discussion forum. The expectation is that questions, and answers, will be read and used by many others in the months and years to come, hence the rules. Both the question and the answer may also get edited by others to make them more generally applicable.
    – doubleunary
    Apr 17, 2021 at 20:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.