Forum Discussion
Filtering a list and chart multiple series
Hello and thanks for any advice. I've simplified my scenario as below. A teacher and student have a walking contest over 7 days. Column A is date, Column B is role (i.e. teacher or student), Column C is miles walked. I would like to create a line chart with 2 data series comparing the teacher's walking to the student's walking.
Ideally, when selecting the data for each series, I could input a formula filtering Column B for either "teacher" or "student" and then plotting the associated 'miles walked', I've included a sample data set.
| Role | Miles walked | |
| 1/1/2001 | Teacher | 1 |
| 1/1/2001 | Student | 2 |
| 1/2/2001 | Teacher | 5 |
| 1/2/2001 | Student | 4 |
| 1/3/2001 | Teacher | 2 |
| 1/3/2001 | Student | 1 |
| 1/4/2001 | Teacher | 3 |
| 1/4/2001 | Student | 4 |
| 1/5/2001 | Teacher | 5 |
| 1/5/2001 | Student | 1 |
| 1/6/2001 | Teacher | 2 |
| 1/6/2001 | Student | 3 |
| 1/7/2001 | Teacher | 5 |
| 1/7/2001 | Student | 1 |
The chart requires a setup like this:
It would be best to record the data like that, but if you really want to, you can use a formula to generate it:
=HSTACK(CHOOSECOLS(FILTER(A2:C15, B2:B15="Teacher"), 1, 3), FILTER(C2:C15, B2:B15="Student"))
2 Replies
The chart requires a setup like this:
It would be best to record the data like that, but if you really want to, you can use a formula to generate it:
=HSTACK(CHOOSECOLS(FILTER(A2:C15, B2:B15="Teacher"), 1, 3), FILTER(C2:C15, B2:B15="Student"))
- Thank you Hans that was very helpful!
