PS_Richard
New Member
- Joined
- Nov 22, 2022
- Messages
- 13
- Office Version
- 365
- 2016
- Platform
- Windows
Here is a small sample of the dataset I am working on. For context this will be worked on a 12 month review so there will be approximately over 1000 rows of data for each month.
When the report is run it will produce a line per day per charger per pool. I've filtered out fields that aren't needed for this example. The period column is done via EOMONTH formula to get it into yyyy-mm.
Currently I have the following being done.
This is looking at the combined data to report what the minimum is for that month as a whole compared to the maximum so I get a graph like this.
However this doesn't give me a true picture as its containing both pools, where as separating them they would look like this.
I don't want to have too many graphs shown, so what I would like is a way to have these two datasets into one combo chart which could look like this (I've done this by filling in cells etc...).
So there is a line for each of the types of pools minimum amount and the bars for the maximum amount. The big catch is, as I want to be able to use this on multiple customers who use different names for the pools I don't want to hard code that description in and there is a chance that there could be more than 2 pools so it needs to be dynamic enough to know it needs to do 3, 4 or however many unique names are in the pool column. The data is to be put into one sheet (sheet 1 for example) of the spreadsheet and the graph creates on another page (sheet 2 for example) using formulas to look at the data.
Is there a way to do this? Am I using the correct formulas in the first section of using an array with MIDIAN?
Apologies this is a long post to have a look over but thanks as always for any help that can be given.
| Book2 | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | Period | Pool Name | Minimum batteries | Maximum batteries | ||
| 2 | 2021-11 | C BAL | 1 | 10 | ||
| 3 | 2021-11 | C BAL | 3 | 8 | ||
| 4 | 2021-11 | C BAL | 4 | 9 | ||
| 5 | 2021-11 | C BAL | 4 | 9 | ||
| 6 | 2021-11 | C BAL | 2 | 6 | ||
| 7 | 2021-11 | C BAL | 2 | 6 | ||
| 8 | 2021-11 | C BAL | 3 | 8 | ||
| 9 | 2021-11 | C BAL | 2 | 8 | ||
| 10 | 2021-11 | C BAL | 2 | 6 | ||
| 11 | 2021-11 | VNA | 1 | 6 | ||
| 12 | 2021-11 | VNA | 1 | 5 | ||
| 13 | 2021-11 | VNA | 1 | 5 | ||
| 14 | 2021-11 | VNA | 1 | 6 | ||
| 15 | 2021-11 | VNA | 1 | 4 | ||
| 16 | 2021-11 | VNA | 1 | 7 | ||
| 17 | 2021-11 | VNA | 1 | 5 | ||
| 18 | 2021-11 | VNA | 1 | 8 | ||
| 19 | 2021-11 | VNA | 1 | 3 | ||
| 20 | 2021-11 | VNA | 1 | 5 | ||
| 21 | 2021-12 | C BAL | 2 | 6 | ||
| 22 | 2021-12 | C BAL | 1 | 5 | ||
| 23 | 2021-12 | C BAL | 0 | 5 | ||
| 24 | 2021-12 | C BAL | 2 | 6 | ||
| 25 | 2021-12 | C BAL | 1 | 6 | ||
| 26 | 2021-12 | C BAL | 2 | 6 | ||
| 27 | 2021-12 | C BAL | 3 | 6 | ||
| 28 | 2021-12 | C BAL | 2 | 5 | ||
| 29 | 2021-12 | C BAL | 2 | 6 | ||
| 30 | 2021-12 | C BAL | 2 | 6 | ||
| 31 | 2021-12 | VNA | 5 | 14 | ||
| 32 | 2021-12 | VNA | 6 | 11 | ||
| 33 | 2021-12 | VNA | 3 | 10 | ||
| 34 | 2021-12 | VNA | 5 | 10 | ||
| 35 | 2021-12 | VNA | 5 | 10 | ||
| 36 | 2021-12 | VNA | 5 | 11 | ||
| 37 | 2021-12 | VNA | 7 | 12 | ||
| 38 | 2021-12 | VNA | 5 | 9 | ||
| 39 | 2021-12 | VNA | 3 | 11 | ||
| 40 | 2021-12 | VNA | 3 | 12 | ||
Sheet1 | ||||||
When the report is run it will produce a line per day per charger per pool. I've filtered out fields that aren't needed for this example. The period column is done via EOMONTH formula to get it into yyyy-mm.
Currently I have the following being done.
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H2:I2 | H2 | =MEDIAN(MINIFS($C:$C,$A:$A,H$1)) |
| H3:I3 | H3 | =MEDIAN(MAXIFS($D:$D,$A:$A,H$1)) |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| _FilterDatabase | =Sheet1!$A$1:$D$40 | H2:I3 |
This is looking at the combined data to report what the minimum is for that month as a whole compared to the maximum so I get a graph like this.
However this doesn't give me a true picture as its containing both pools, where as separating them they would look like this.
I don't want to have too many graphs shown, so what I would like is a way to have these two datasets into one combo chart which could look like this (I've done this by filling in cells etc...).
So there is a line for each of the types of pools minimum amount and the bars for the maximum amount. The big catch is, as I want to be able to use this on multiple customers who use different names for the pools I don't want to hard code that description in and there is a chance that there could be more than 2 pools so it needs to be dynamic enough to know it needs to do 3, 4 or however many unique names are in the pool column. The data is to be put into one sheet (sheet 1 for example) of the spreadsheet and the graph creates on another page (sheet 2 for example) using formulas to look at the data.
Is there a way to do this? Am I using the correct formulas in the first section of using an array with MIDIAN?
Apologies this is a long post to have a look over but thanks as always for any help that can be given.
