1

I'm looking to create a histogram with this data. I don't know how to set the count column as the frequency list of the data. When I try to select both columns and make a histogram it just treats the second column as the data set and the first as labels. Instead it should see that for example, height 60 has a frequency of 2. How do I do this?

enter image description here

Improve this question
1
  • 1
    "height 60 has a frequency of 2." In your graphic 60 has a frequency of 8.... – 24601 Aug 24 '20 at 17:15
1

A variation on a theme per @player0 from StackOverflow: Google sheets array formula to split and transpose cells with commas into another column

Enter this formula in cell C2:

=TRANSPOSE(SPLIT(TEXTJOIN(",", 1,ARRAYFORMULA(REPT(A2:A&",",B2:B))), ","))

Use the resulting column of values for your histogram.

The logic is:

  • ARRAYFORMULA(REPT(A2:A&",",B2:B)) takes the height value in Column A, and repeats the value by the frequency in Column B. Note the height is described as A2:A&"," - this puts a comma between each value.
  • TEXTJOIN - joins all the values in the array; again with a comma as the delimiter
  • SPLIT - breaks the resulting value at the commas
  • TRANSPOSE - converts the row of data into a column.

Sample

Sample

Improve this answer
2
  • This is useful - thank you. However, what if the data transposed is 1 million rows or more. This could crash the browser. Is there any way to build a histogram directly from the 2-column Height x Count table? – samthebrand Sep 27 '20 at 15:06
  • Who is to say that it wouldn’t crash the browser anyway.but maybe you could build it via a script. Have a look at arraytodatatable In the charts docs – Tedinoz Sep 27 '20 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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