JurassicClark
New Member
- Joined
- Mar 22, 2022
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
Hey to anyone who can help me out here!
So I have a data range where some months I'll fall short of my targeted volume and some months I may exceed.
I've attached an example sheet of the data - linked to a pivot table. I've added a calculated field onto the pivot table with my target on and created a simple formula alongside it to produce a percentage.
What i'd like is to create a doughnut chart from the data which can show both if i'm below my 100% target but also If i'm exceeding it.
Sorry I have copied and pasted the chart from another spread so its got various COUNTIFs & SUMIFs etc that arent exactly relevant
Any help much appreciated!
So I have a data range where some months I'll fall short of my targeted volume and some months I may exceed.
I've attached an example sheet of the data - linked to a pivot table. I've added a calculated field onto the pivot table with my target on and created a simple formula alongside it to produce a percentage.
What i'd like is to create a doughnut chart from the data which can show both if i'm below my 100% target but also If i'm exceeding it.
Sorry I have copied and pasted the chart from another spread so its got various COUNTIFs & SUMIFs etc that arent exactly relevant
Any help much appreciated!
eg workbook.xlsx | |||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |||
1 | |||||||||||||||||||
2 | |||||||||||||||||||
3 | |||||||||||||||||||
4 | |||||||||||||||||||
5 | |||||||||||||||||||
6 | Month | CalendarMonth No. | Financial Month No. | No. BBTs Emptied | Total Packaged Volume | Month | NOV | ||||||||||||
7 | APR | 4 | 1 | 18 | 7614 | ||||||||||||||
8 | MAY | 5 | 2 | 35 | 14237 | Sum of Total Packaged Volume | Sum of Target Volume (HL) | As a % | |||||||||||
9 | JUN | 6 | 3 | 28 | 13192 | 17031.80 | 10000 | 170.32% | |||||||||||
10 | JUL | 7 | 4 | 17 | 7206 | ||||||||||||||
11 | AUG | 8 | 5 | 26 | 10069 | ||||||||||||||
12 | SEP | 9 | 6 | 21 | 9762 | ||||||||||||||
13 | OCT | 10 | 7 | 31 | 13707 | ||||||||||||||
14 | NOV | 11 | 8 | 42 | 17032 | ||||||||||||||
15 | DEC | 12 | 9 | 24 | 10457 | ||||||||||||||
16 | JAN | 1 | 10 | 16 | 7257 | ||||||||||||||
17 | FEB | 2 | 11 | 21 | 9259 | ||||||||||||||
18 | MAR | 3 | 12 | 13 | 5011 | ||||||||||||||
19 | |||||||||||||||||||
20 | |||||||||||||||||||
21 | |||||||||||||||||||
22 | |||||||||||||||||||
23 | |||||||||||||||||||
24 | |||||||||||||||||||
Data |
Cell Formulas | ||
---|---|---|
Range | Formula | |
F7:F18 | F7 | =COUNTIFS('[YTD 2021-22 Packaging Report Beaverworld ting.xlsx]YTD Packaging Report'!$L$13:$L$5984,"=YES",'[YTD 2021-22 Packaging Report Beaverworld ting.xlsx]YTD Packaging Report'!$AB$13:$AB$5984,"="&D7) |
G7:G18 | G7 | =SUMIF('[YTD 2021-22 Packaging Report Beaverworld ting.xlsx]YTD Packaging Report'!$AB$13:$AB$2984,"="&D7,'[YTD 2021-22 Packaging Report Beaverworld ting.xlsx]YTD Packaging Report'!$AK$13:$AK$2984) |
P9 | P9 | =GETPIVOTDATA("Sum of Total Packaged Volume",$M$8)/GETPIVOTDATA("Sum of Target Volume (HL)",$M$8) |