Data Presentation #SPILL

[rcocrane99]

So I have a very long set of data (5 checks a day, 10 tanks, 20 days a month) that I collect each month and want to represent it on a monthly basis. Each day we check pH values from various places and I want to display them all using a box and whisker chart. Ideally I can set up xlookup to pull the numbers from the row with a given date but because there are multiple I get a spill error. Any thoughts? Thanks

 

[felixstraube]

How about this?:

You transpose your data, like this:

Book1.xlsx
	A	B	C	D	E
1	Datetime	Date	Tank 1	Tank 2	Tank 3
2	01/10/2024 00:00	01/10/2024	0.92	1.06	1.31
3	01/10/2024 02:00	01/10/2024	0.89	1.03	1.51
4	01/10/2024 04:00	01/10/2024	0.91	0.9	1.34
5	01/10/2024 06:00	01/10/2024	0.9	0.92	1.4
6	01/10/2024 08:00	01/10/2024	0.81	1.06	1.07
7	01/10/2024 10:00	01/10/2024	0.88	0.9	1.18
8	01/10/2024 12:00	01/10/2024	0.85	0.91	1.01
9	01/10/2024 14:00	01/10/2024	0.8	1.03	1.04
10	01/10/2024 16:00	01/10/2024	0.89	0.91	1.29
11	01/10/2024 18:00	01/10/2024	0.93	1.06	1.17
12	01/10/2024 20:00	01/10/2024	0.88	1.09	1.27
13	01/10/2024 22:00	01/10/2024	0.94	1.01	1.16
14	02/10/2024 00:00	02/10/2024	0.83	0.96	0.93
15	02/10/2024 02:00	02/10/2024	0.85	1.01	1.1
16	02/10/2024 04:00	02/10/2024	0.8	0.96	0.98
17	02/10/2024 06:00	02/10/2024	0.89	0.95	1.41
18	02/10/2024 08:00	02/10/2024	0.88	0.91	1.37
19	02/10/2024 10:00	02/10/2024	0.95	0.99	1.37
20	02/10/2024 12:00	02/10/2024	0.83	1.04	1.1
21	02/10/2024 14:00	02/10/2024	0.82	1.07	0.92
22	02/10/2024 16:00	02/10/2024	0.89	1.06	1.6
23	02/10/2024 18:00	02/10/2024	0.89	0.95	0.98
24	02/10/2024 20:00	02/10/2024	0.88	0.99	1.38
Sheet4

Cell Formulas
Range	Formula
B2:B24	B2	=INT(A2)

And add the column Date with the formula above.
Then you create the box and whisker char

 

[felixstraube]

Actually you don't need to transpose the data.
For me it is just easier to work with tables that grow vertically.

 

[felixstraube]

Oh sorry i just realized that you want to display them on a monthly basis (not a daily basis like I did).
You can change the formula in column B to this:

Book1.xlsx
	A	B	C	D	E
1	Datetime	Date	Tank 1	Tank 2	Tank 3
2	01/10/2024 00:00	Oct-24	0.92	1.06	1.31
3	01/10/2024 02:00	Oct-24	0.89	1.03	1.51
4	01/10/2024 04:00	Oct-24	0.91	0.9	1.34
5	01/10/2024 06:00	Oct-24	0.9	0.92	1.4
6	01/10/2024 08:00	Oct-24	0.81	1.06	1.07
7	01/10/2024 10:00	Oct-24	0.88	0.9	1.18
8	01/10/2024 12:00	Oct-24	0.85	0.91	1.01
9	01/10/2024 14:00	Oct-24	0.8	1.03	1.04
10	01/10/2024 16:00	Oct-24	0.89	0.91	1.29
Sheet4

Cell Formulas
Range	Formula
B2:B10	B2	=EOMONTH(A2, -1)+1

And... something i didnt say, you use column Date and Tank1,2, etc for the chart (avoiding the Datetime column)

 

[rcocrane99]

Actually I should of specified that it will be a day by day chart, Im hoping to eventually get it to change the month automatically based off the month I'd like to present. Just working on getting it started so mind the little amount of data Im working with but an idea how to fix this?