Search code examples
exceldatasetgoogle-sheetsfrequency

Combine two data ranges into one range (Google Drive Excel)

Hi there I am looking to combine two data ranges/arrays into one in order to feed them into excel FREQUENCY function.

Example: First data range - B5:F50 Second data range - J5:N50 Bins data range - I5:I16 Function definition - FREQUENCY(data_array; bins_array)

Basically I am lazy and I don't want to reshuffle my excel script to spit out both datasets side by side so that I can reference them using something like B5:K50 range. Is there any way I can combine both datasets into data_array using some kind of formula? Maybe to end up with something along the line of =FREQUENCY((B5:F50,J5:N50); I5:I16) ?

BTW: Either of

=FREQUENCY(B5:F50; I5:I16)
=FREQUENCY(J5:N50; I5:I16)

work just file on their own for me.

Update

Actual formula definition FREQUENCY(data, classes)

2013 MS Excel (unrelated)

enter image description here

Solution

  • In MS Excel FREQUENCY function accepts a "union" as the first argument, i.e. a list of references separated by commas and enclosed in parentheses e.g.

    =FREQUENCY((B5:F50,J5:N50),I5:I16)

    Note: the "bins array" can also be a union if required

    In "Google sheets" I don't think the same thing is possible - there may be a clever workaround, but I'm not aware of it