Pivot Table Help Needed!

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

--
Message posted from http://www.ExcelForum.com

0
4/22/2004 2:13:12 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
112 Views

Similar Articles

[PageSpeed] 4

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 >" <<hce.154bab@excelforum-nospam.com> wrote in message
news:hce.154bab@excelforum-nospam.com...
> Dear All
>
> I have a very complicated problem here which i need some advice and
> suggestions...
>
> I have 12 (Jan-Dec) monnths of data here. I need to run a pivot table
> and then charts from the pivot table.
>
> I need to run a pivot table because I need to count the data across the
> rows to get the %.
>
> The problem here is that is it possible for me to do what i'm going to
> 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 combined
> total of Jan, Feb and Mar data. Hence, for the Apr point, I would need
> to combine Feb, Mar and Apr data. This would continue until I get the
> last point which is Dec.
>
> Hence, my chart would contain 9 points starting from Mar-Dec of which
> 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 one
> 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 not
> that smart. Kindly look at this and provide me with some advice on
> whether this is achievable or not...
>
> Cheers
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
Bernie
4/22/2004 4:13:20 PM
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
--
Message posted from http://www.ExcelForum.com

0
4/23/2004 9:21:55 AM
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 >" <<hce.155seh@excelforum-nospam.com> wrote in message
news:hce.155seh@excelforum-nospam.com...
> Hi Bernie
>
> The Month column will contain 3 codes because we need to combine 3
> months data to get one month data point hence every month data point
> will have overlaps... for eg... march data point will contain jan, feb
> & mar, apr data point will contain feb, mar & apr and so on... as for
> the code in the month column, 1 will represent jan, 2 will represent
> feb and so on....
>
> thank you so much for your interest in this... hope we can solve the
> problem together... if i can think of any other suggestions i will post
> back...
>
> you can insert as many col as u want to solve the problem but you
> cannot duplicate the records... for eg if i only have 2000 records... i
> must have only 2000 and not more...
>
> as for the rating column, the code is only from 1 to 5 with each code
> representing something ....
>
> cheers
>
>                  Attachment filename: test.txt
> Download attachment:
http://www.excelforum.com/attachment.php?postid=522104
> ---
> Message posted from http://www.ExcelForum.com/
>


0
Bernie
4/23/2004 1:07:47 PM
Hi Bernie

My private email is tazkel@hotmail.com

I really appreciate your help in this...

Cheer

--
Message posted from http://www.ExcelForum.com

0
4/24/2004 4:40:36 AM
hce,

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

Bernie

"hce >" <<hce.157a1m@excelforum-nospam.com> wrote in message
news:hce.157a1m@excelforum-nospam.com...
> Hi Bernie
>
> My private email is tazkel@hotmail.com
>
> I really appreciate your help in this...
>
> Cheers
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
Bernie
4/24/2004 10:21:20 PM
Reply:

Similar Artilces:

HELP!
xp_sendmail has died on our SQL Server, I get error "xp_sendmail: failed with mail error 0x80004005" if I try using it. I believe that the reason that this has happened is because the SQL Server service account is unable to send mail, as follows: If I log on to Outlook Web Access as the SQL Server service account (SVC_SqlServer) I am able to receive mail. However, any mail sent from this account simply dissapears. It appears in the Sent items folder (in OWA for the SVC_SqlServer account) but the recipient (me, in this case) never receives it! Does anyone know why this account is ...

HELP WITH REPORTS
So here is the deal. I'm a basic user of access, I know enough of the basic to be dangerous. At work, I have to do alot of visit requests for employees to visit gov. installations and military bases. For as long as I can remember, I have had to hand type all of the names, ssns, dob, clearance level for each person, can get tedious especially when I have 30 people on one document. So I made a Access table with all of the stuff I need, made a nice simple form to enter data for new people, and I made a simple querie to extract the stuff I need and a nice report in the proper letter for...

HELP!! Excel and PowPnt crash on startup in Office vx under 10.3.2
I'm getting crashes in Excel and PP whenever I try to run them. I'm running Panther 10.3.2 and have updated everything I can. Any suggestions? Thanks, Dan Dan Gordon <gojet@jetpropics.com> wrote: Hi Dan, > I'm getting crashes in Excel and PP whenever I try to run them. I'm > running Panther 10.3.2 and have updated everything I can. > Any suggestions? Try going to the preference folder in your user account and in the Microsoft folder, delete the Carbon registration database and the Office 10 font cache. These two files will be automatically re-create...

I CANNOT FIND OUTLOOK!! HELP!!
Hi. I'm a new user to Outlook and I'm having a heck of a time. I have synced my Palm with Outlook, so I know there's data on my computer that I should be able to access. I opened Outlook, and there is no data in there. When I go to open a Personal Folder, it asks me to browse for the data. I searched my computer and found one large PST file (my palm data, I'm assuming), but there's no Outlook or Palm logo associated with it. I try to open it, and it asks me which program to open it with. Microsoft Outlook is NOT on that list! Even though it's on my des...

Need help with mailing list
I have two mailing lists. List A is approximately 16,000 customers. List B is about 6,000 customers. All of the customers on List B are also on List A. I need to remove all of the customers on List B, from List A. My expected result is List A minus List B = List C (new list of about 10,000). This should be simple, but I do not know how to start. ...

I need to alphabetize entire excel document.
Please let me know the best way to do this. Thanks!! Select your range data|sort debcmk3 wrote: > > Please let me know the best way to do this. > > Thanks!! -- Dave Peterson ...

Paramedic needs formula help!
I'm measuring some of our prehospital procedures with MS Excel and I'm having a hard time with complicated formulas. I have two columns with data. I need to count all the criteria in one column only if it matches another criteria in the second column. Any help would be appreciated. I assure you all that I am a far better paramedic than I am an Excel user. Lieutenant Dave Erdman Broward Sheriff's Office Department of Fire Rescue daviderdman@comcast.net One possible way =SUMPRODUCT((range1=criteria1)*(range2=criteria2)) or maybe =SUMPRODUCT((range1=range2)+0) -- Regards,...

indent in a table
Hi I was wondering what controls indentation in a table. I wish to right indent a number in a table but when I try to make it indent further to the right, a message says that the indent size is too large. The funny thing is I have the same row heights and column widths as another table but with a larger indent size. What can be done about this Thanks Roger ...

Finding Field in Table
I am using a very complicated database created by someone else that contains a multitude of different tables and forms. When I enter information into a field on a form, how can I determine what TABLE that information is stored in? I can see the NAME of the field, but can't find the TABLE information. On Thu, 23 Aug 2007 13:44:02 -0700, LaurieH <LaurieH@discussions.microsoft.com> wrote: >I am using a very complicated database created by someone else that contains >a multitude of different tables and forms. When I enter information into a >field on a form, how can I de...

Query on two tables with matching null fields
Suppose I have two tables: Table 1: ID a b c 1 1 2 2 3 4 3 5 6 4 7 8 9 Table2 ID a b c 1 1 2 2 3 4 3 5 6 4 7 8 9 The following SQL: SELECT Table1.ID, Table2.ID FROM Table1 INNER JOIN Table2 ON (Table1.c = Table2.c) AND (Table1.b = Table2.b) AND (Table1.a = Table2.a); Returns: Table1.ID Table2.ID 4 4 I do want these tables connected by inner joins not left or right (in other words I want only those records where all t...

I have published a worksheet to web but have one problem someone help please :(
Hello I published a worksheet to the web. The address is http://www.inforamp.com/~kidd/html/pmcshop1.htm The problem is I want to get rid of the scroll bars and the top men bar so ONLY the worksheet is present. Can someone tell me what I did wrong You help is greatly appricated Pau -- kid ----------------------------------------------------------------------- kidd's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1579 View this thread: http://www.excelforum.com/showthread.php?threadid=27378 All I see is this :- To use this Web page interactively, you must...

Apply-user-defined table style only to tables with no vertically merged cells
Dear Experts: below simple macros applies a user-defined table styles to all tables in the current document. It works fine. I wonder whether it is possible... .... to check each table for vertically merged cells and... .....only apply the user-defined table style to tables without any vertically merged cells .... the index number of tables which have not been worked on because of this is to be displayed in a msgbox Hope this is feasible and not beyond the scope of this forum. Help is much appreciated. Thank you very much in advance. Regards, Andreas Sub ApplyTblStyl...

Form
Hello, I am trying to create a project progress form. The is completed twice a month. I have created a table that has the periods as follows: Report Num Period Start Period End ------------------------------------------------------ 1 01 Jan 10 15 Jan 10 2 16 Jan 10 31 Jan 10 etc.... The second table has the work items in the project: Item # Work Desc. % Total % this Period % Cumulative -------------------------------------------------------------------------------- 1 ...

Help please...
Our Exchange server (5.5 on NT) has begun relaying mail using the guest account (no password). The guest accounts, both local and domain, are disabled and always have been disabled. Exchange is still allowing open relay via this account. I've even tried renaming the accounts and giving them long passwords but Exchange still allows the guest account, with no password, to be used. The box is clean (no viruses, fully patched, etc). I've had to restrict access at the firewall until I can get this fixed. This could be a serious SPAM issue if it happens to more Exchange servers out the...

Change datasource of pivot table
Hello, I am trying to change the datasource of an EXISTING pivot table from one SQL server to another. But there is NO option for changing the DATA SOURCE of an existing pivot table. Using the wizard there is only access to the TABLES of the first data source - you cannot select a different data source. I would be grateful if someone knew something about it... ...

lost table relationships
Hi, I am using Access 2003, front end tables are linked to the back end, links are checked every time the db starts. In my front end, I had 100 % of the relationships gone in 100 % of my queries. Checking the backend, all relationships were present and intact. Has anyone seen this "feature" before, and is there a safeguard against it (other than putting all queries into a module and calling subs from a switchboard form)? Thanks a lot in advance for the assistance. The relationships aren't required in the front end. They're only required in the back end to enforce ...

Which Security Roles needs a worker to send massemails
Hi, which user Rights needs a CRM User in CRM or in Active directory to send mass mails over CRM? Thank you for helping, Alwin Nothing special, just access tot he system. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On Mon, 22 Aug 2005 11:23:08 -0700, "Alwin" <aschauer@augsburg.eusis.com> wrote: Hi, which user Rights needs a CRM User in CRM or in Active directory to send mass mails over CRM? Thank you for helping, Alwin We get at some useres the Window, not enough user rights or privileges, when we w...

slecting averages from a changing table
i have a spread sheet set up to colect data on a daily basis from Jan 1 to dec 31. i want to be able to have a month by month average of slect coloms show up off to the side for quick access. colum a converts the date from colum C in to the numerical value ( jan = 1 ect), Colum B show what week of the year the date falls in to (jan 1 = week 1 of the year),colum C has the dates in it, data is input in colums D to M. so i want to be able to take the data in coulm H and M and be able to average everything that has a jan date in to cell N4, feb in to cell N5 ect..can it be done? ...

Pivot table #3
I am trying to "Refresh the Data" for my Pivot Table. My data is a lot longer then it used to be. How do I make sure that all numbers are included in the pivot table. Right now the last three rows are cut off. Thanks. E Ed, right click in the pivot table, choose pivot table wizard, then back, then select the required range with your mouse ED wrote: >I am trying to "Refresh the Data" for my Pivot Table. > >My data is a lot longer then it used to be. How do I make sure that all >numbers are included in the pivot table. > >Right now the last three ...

Help #7
How can I have the title of a column stay visable, even at high numbered lines? Highlight the column to the right of the one that needs to be visible by clicking at the top in the grey bit with the column letter. Go to the menu bar, select Window, Freeze Panes. That should do it. Dan. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ ...

need an aggregate function
I have text values stored in a field in a table of about 200 records. Some values repeat themselves often. Is there a quick way to find out which value appears the most often? -- Jane hi Jane, jane wrote: > I have text values stored in a field in a table of about 200 records. Some > values repeat themselves often. Is there a quick way to find out which value > appears the most often? Use a query: SELECT yourField, Count(*) FROM yourTable GROUP BY yourField mfG --> stefan <-- ...

office help
my computer crashed & when i reinstalled xp it is telling me to insert office cd to open excel but i can t find my cd, what can i do? thanks in advance find the cd. >-----Original Message----- >my computer crashed & when i reinstalled xp it is telling >me to insert office cd to open excel but i can t find my >cd, what can i do? >thanks in advance >. > Very helpful anonymous. For OP.... The following page has information on replacing a CD: How to replace lost, broken, or missing Microsoft software or hardware http://support.microsoft.com/default.aspx?id=...

Help with copy/paste VBA code
Hi folks, I have an excel workbook where different revenue scnearios are entered and then you get a table and couple of graphs. What I like to be able to do is: I like to add a command button on a sheet where if clicked, it will copy a table and couple of charts, then paste them in an empty sheet. Then next time the button is clicked, it will do the same things but now pastes these in the next empty sheet.. and so on. how does one do this. The best way to start is to record a macro whilst you do these actions. Once done then post any queries back here -- Message posted from htt...

Order status problem. please help
hi how can i change the status of an order to completed or something like that. there seams to be a status hierarchy assigned to the order status (fields seem to be statecode and statuscode) what i would like to have is 1. order is in status "new" 2. when somebody is working on the order the status should be set to "in progress" 3. after work is done or item is deliverd --> status = "completed" completed items should also not appear in active order list view please help! i have tried nearly everything in the user interface. i cant simple advance the status ...

opened client excel document. Lost fill colors. Help!?
Have Excel 2003. Liked my fill colors. Opened client document from email. Lost my fill colors. Can I get them back? ...