0

I have data that comes in batches of N values ("Y" values if you like) for a given index (or "X" value if you like). How to make N separate series in my graph for each entry of a given index ?

For example, if N = 3, I could have :

0   |   21
0   |   40
0   |   89
1   |   76
1   |   49
1   |   96
2   |   18
2   |   89
2   |   16
3   |   75
3   |   7
3   |   87
4   |   35
4   |   28
4   |   96
... |   ...

And taking every third line I would want a graph of the following series :

0   |   89
1   |   96
2   |   16
3   |   87
4   |   96

Same for every second line and every first, etc.

3 Answers 3

3

You can generate the table to feed into the chart editor in a single formula:

=arrayformula(query({{"";"Entry "&mod(sequence(count(A:A))-1,countif(A:A,0))+1},{{"Index","Data"};array_constrain(A:B,count(A:A),2)}},"select Col2,min(Col3) group by Col2 pivot Col1"))

Assumes the X and Y values are in column A & B without a header. Should work for any number of data points, and any batch size provided there are always the same number of data points in each batch. Uses the pivot clause of the QUERY function on a repeating 1,2...,N sequence to generate the different columns for each entry against each index.

1
  • nice and compact :)
    – Daniele
    May 20, 2022 at 21:13
2

GRAPH

Same graphic, different approach. Columns D to I contain the 3 groups of data, obtained with the following formulas:

First Group:

=Filter(A2:B16, Mod(Row(A2:A16),3)=2)

Second Group:

=Filter(A2:B16, Mod(Row(A2:A16),3)=0)

Third Group:

=Filter(A2:B16, Mod(Row(A2:A16),3)=1)

Here the test sheet

1

enter image description here

The leftmost gray cell has this formula:

={COUNTIF(A:A,0),SEQUENCE(1,COUNTIF(A:A,0),0,1)}

It's an array formula that generates how many two things: 1) the first cell contains how many series there are by counting how many 0's exists on column A and 2) the expected value from the modulus operator.

The brown part has this formula:

=UNIQUE(A:A)

This blue part has this formula:

=FILTER($B:$B,MOD(ROW($A:$A),$D$1)=E1,$A:$A<>"")

This should pull out every three rows that isn't blank. You can then use the fill handle for red and yellow.

For the chart, simply select the entire table, excluding the gray part. Use the brown as your x-axis and the rest as your series.

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.