Pivot Table Help Needed!

[hce]

Dear All

I have a very complicated problem here which i need some advice an
suggestions...

I have 12 (Jan-Dec) monnths of data here. I need to run a pivot tabl
and then charts from the pivot table.

I need to run a pivot table because I need to count the data across th
rows to get the %.

The problem here is that is it possible for me to do what i'm going t
explain below.

First, I need to combine 3 months data to form the first month of data
For example, the Mar point will need to be calculated from the combine
total of Jan, Feb and Mar data. Hence, for the Apr point, I would nee
to combine Feb, Mar and Apr data. This would continue until I get th
last point which is Dec.

Hence, my chart would contain 9 points starting from Mar-Dec of whic
every point/month is actually a combination of 3 months' data.

The 12 months data I have now are grouped by columns and there's on
column that tells you which month this record belongs to.

For example,

Record|Month|Rating
A |JAN |1
B |FEB |1
C |MAR |4
D |JAN |3

I have thought this problem for a long time and unfortunately, I'm no
that smart. Kindly look at this and provide me with some advice o
whether this is achievable or not...

Cheer

 

[Bernie Deitrick]

hce,

You're going to have to use a helper column or two in your data table. The
specifics of the formulas will depend on what your data is composed of: are
your dates actual dates (numbers) or month labels (strings)?

Post back with specfics.

HTH,
Bernie
MS Excel MVP

 

[hce]

Hi Bernie

The Month column will contain 3 codes because we need to combine
months data to get one month data point hence every month data poin
will have overlaps... for eg... march data point will contain jan, fe
& mar, apr data point will contain feb, mar & apr and so on... as fo
the code in the month column, 1 will represent jan, 2 will represen
feb and so on....

thank you so much for your interest in this... hope we can solve th
problem together... if i can think of any other suggestions i will pos
back...

you can insert as many col as u want to solve the problem but yo
cannot duplicate the records... for eg if i only have 2000 records...
must have only 2000 and not more...

as for the rating column, the code is only from 1 to 5 with each cod
representing something ....

cheer

Attachment filename: test.txt
Download attachment: http://www.excelforum.com/attachment.php?postid=52210

 

[Bernie Deitrick]

hce,

There are two ways you can approach this: with a pivot table, or without.

Send me a private e-mail and I will send you a workbook showing both ways.

HTH,
Bernie
MS Excel MVP

 

[hce]

Hi Bernie

My private email is (e-mail address removed)

I really appreciate your help in this...

Cheer

 

[Bernie Deitrick]

hce,

I will send you the workbook Monday morning - I'm home now, and don't have
remote access to my other PC.

Bernie