#1
|
|||
|
|||
Help With Data Analysis/Pivot Table & Chart
Hi,
In the attached spreadsheet, I have data and a pivot table. I need to be able to do a further analysis (explained in the file), but I cannot figure out how to do it. There may be a way to do it directly from the original data. Thanks, Tommy |
#2
|
||||
|
||||
See attached
|
#3
|
|||
|
|||
Thanks for the support p45cal.
Is it possible you could explain the formula, so that I can learn. Even a link to a page explaining it would be great. Tommy |
#4
|
||||
|
||||
Quote:
=COUNTIF([SO No.],[@[SO No.]]) This surely doesn't need explaining, but COUNTIF function - Microsoft Support The rest is a standard pivot table & chart. Column D is what groups the rows of data in the pivot. |
#5
|
|||
|
|||
Thanks again p45cal.
I can't find a reference as to why you use @ in the formula on the page that you linked to, so I would really appreciate if you could bear with me a little longer. Tommy |
#6
|
||||
|
||||
the @ means refer to the same row in that column.
Without the @ refers to whole column. You can see it when you edit the formula in the formula bar (the formula parts are highlighted in the same colour as the boxes around the ranges): 2023-02-23_210650.png You don't need to type these references in. If you use the mouse to select ranges while you're building the formula, they will appear automatically. |
#7
|
|||
|
|||
Many Thanksp45cal
I understand the formula now, but I'm struggling with the pivot table. How did you calculate the percentages. The only way I could find to do it was to create another pivot table and calculate from there (see attached) |
#8
|
||||
|
||||
2023-02-23_223931.png Right-click somewhere in the values area of the pivot (column G in my file, column K in your latest file) and choose Value field settings… |
#9
|
|||
|
|||
Many thanks p45cal
Your continued support is very much appreciated. Tommy |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
pivot chart for multiple data sets | md0627 | Excel | 1 | 01-08-2023 05:17 PM |
Can we update Chart in PPT based on a table in the excel and the table is part of the Chart Data | nareshj | PowerPoint | 0 | 07-12-2018 02:29 PM |
Copying a Chart Linked to a Pivot Table | coma08 | Excel | 1 | 06-07-2017 08:24 PM |
Combine multiple tables into one pivot table in order to do trend analysis | marelisev | Excel | 2 | 04-15-2017 08:30 AM |
Create PIVOT chart using few rows in pivot table | Santhosh_84 | Excel | 1 | 08-31-2015 06:22 AM |