Need help with complex pivot...
 

Complex to me anyway.

Say I have a bunch of customer survey data in an Excel 2007 table with the
following format:

Date, Customer, Region, Rating

I want to pivot the data is such a way that I group certain types of scores
(10,9,8 = Great, 7,6,5 = Good, 4,3,2,1 = Bad) against region, and quarter (or
some other date filter). I can't seem to do this, but to complicate the
matter, I would also like to add a calculated value using the three counts
((Great + Good)/(Great + Good + Bad)):

Great Good Bad %Favorable
Q1 10 12 8 73%
--North Region 6 3 5 64%
--South Region 4 9 3 81%
Q2 13 6 5 79%
-- North Region 8 2 1 91%
-- South Region 5 4 4 69%

Can this all be done in a pivot, or do I have to brush of my VBA skills?

Greg

Need help with complex pivot...
 

I think you'd be well served with a helper column groups the scores into
Good/Bad/Great. Once you have that, the rest falls out pretty nicely or you
can use SUMPRODUCT to calculate the results and support the blended
calculation.
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"gmj105" wrote:

Complex to me anyway.

Say I have a bunch of customer survey data in an Excel 2007 table with the
following format:

Date, Customer, Region, Rating

I want to pivot the data is such a way that I group certain types of scores
(10,9,8 = Great, 7,6,5 = Good, 4,3,2,1 = Bad) against region, and quarter (or
some other date filter). I can't seem to do this, but to complicate the
matter, I would also like to add a calculated value using the three counts
((Great + Good)/(Great + Good + Bad)):

Great Good Bad %Favorable
Q1 10 12 8 73%
--North Region 6 3 5 64%
--South Region 4 9 3 81%
Q2 13 6 5 79%
-- North Region 8 2 1 91%
-- South Region 5 4 4 69%

Can this all be done in a pivot, or do I have to brush of my VBA skills?

Greg

Need help with complex pivot...
 

Thanks, I will give this a try!

"M Kan" wrote:

I think you'd be well served with a helper column groups the scores into
Good/Bad/Great. Once you have that, the rest falls out pretty nicely or you
can use SUMPRODUCT to calculate the results and support the blended
calculation.
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"gmj105" wrote:

Complex to me anyway.

Say I have a bunch of customer survey data in an Excel 2007 table with the
following format:

Date, Customer, Region, Rating

I want to pivot the data is such a way that I group certain types of scores
(10,9,8 = Great, 7,6,5 = Good, 4,3,2,1 = Bad) against region, and quarter (or
some other date filter). I can't seem to do this, but to complicate the
matter, I would also like to add a calculated value using the three counts
((Great + Good)/(Great + Good + Bad)):

Great Good Bad %Favorable
Q1 10 12 8 73%
--North Region 6 3 5 64%
--South Region 4 9 3 81%
Q2 13 6 5 79%
-- North Region 8 2 1 91%
-- South Region 5 4 4 69%

Can this all be done in a pivot, or do I have to brush of my VBA skills?

Greg

Need help with complex pivot...
 

Pivot Table solution for quarter/region and quarter:
http://www.savefile.com/files/1696163

Need help with complex pivot...
 

Very Helpful, thank you for taking the time to pull that together!

"Herbert Seidenberg" wrote:

Pivot Table solution for quarter/region and quarter:
http://www.savefile.com/files/1696163