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

Attached Files

Test.xlsx (156.0 KB, 3 views)

See attached

Attached Files

msofficeforums50478Test-15.xlsx (98.0 KB, 2 views)

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

The only formula is in column D:
=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.

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

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.

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)

Attached Files

David_Test.xlsx (82.2 KB, 1 views)

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…

Many thanks p45cal

Your continued support is very much appreciated.

Tommy