+ Reply to Thread
Results 1 to 10 of 10

Need help with Pivot table problems

  1. #1
    Registered User
    Join Date
    02-05-2014
    Location
    canada
    MS-Off Ver
    Excel 2007
    Posts
    17

    Need help with Pivot table problems

    Hello!

    I'm working on a large amount of data and attempting to sort the values by year. Since there are multiple dates and times entered for 1 year, I've decided it would be simplest to group all the dates by year.
    Question: When attempting to group dates by year, there is a seconds option. How do I make sure I wont miss any dates in the year?

    Ex: 01/01/2010 00:00:00 and ending at 31/12/2010 24:00:00. If I set it up this way, will I be able to capture every date for the year 2010? Will the time that is entered (ex: 12/01/2010 2:29:00 AM) mess up the calculations and how can I avoid this?


    Also, lets say grouping has worked. I have used "Calculated Field" Option to compare the average between years by using ( = 'totals' / 2 ) , ( ='totals' / 3) etc for the number of years Im comparing it against. So it will find the average over 2 and 3 years etc, by dividing the amounts total column by the number of years I want.

    Ex: year 2010 total = 150,Year 2011 total = 100, therefore 150+100/2 = 200 average between 2010 and 2011.... calculated field = totals /2


    Does this seem like the best way to do this?

    Thanks!
    Last edited by Ephy; 09-01-2014 at 09:00 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Does this sound about right to you?

    01/01/2010 00:00:00 and ending at 31/12/2010 24:00:00.
    The latter date is in 2011. 31/12/2010 24:00:00 and 01/01/2011 00:00:00 have the same value.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Does this sound about right to you?

    Ok

    If your dates are in column A Use a helper column and enter =Year(A1) in row 1 and fill down.
    sort by the helper column.

    Point 2 is ok.

    You probably need to use a macro to insert a couple of empty rows between the year groups.

    in the first row use sum to cont your amounts and count to count the number of entries.

    Had you posted a sample I would have coded this for you.

  4. #4
    Registered User
    Join Date
    02-05-2014
    Location
    canada
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Does this sound about right to you?

    Hmm.. okay.

    I don't know why this is so confusing? If I wanted all the data for the year 2010, I would start it at Dec 31 2010 24:00 and end at Jan 1 2011 00:00? and then for the next year it would be.... ?

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Does this sound about right to you?

    I don't know why it's confusing; if you enter 31 Dec 2014 24:00 into a cell, Excel will change it to 01 Jan 2015 0:00

  6. #6
    Registered User
    Join Date
    02-05-2014
    Location
    canada
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Does this sound about right to you?

    I've attached a sample of the data Im working with. Hope it makes sense. I inserted your helper column

  7. #7
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Does this sound about right to you?

    Try this Macro

    Please Login or Register  to view this content.
    Attached Files Attached Files

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Does this sound about right to you?

    @Ephy

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,084

    Re: Need help with Pivot table problems

    If you want all the data for a year, why are you looking at times at all? Can't you just look at the year ...
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  10. #10
    Registered User
    Join Date
    02-05-2014
    Location
    canada
    MS-Off Ver
    Excel 2007
    Posts
    17

    Re: Need help with Pivot table problems

    I'm still struggling to get my pivot table to show me averages over the years against each other. Ex: 2010 + 2011 /2 = xxxx, ad then 2010 + 2012 / 3 = xxxx averages over the year.

    I cant seem to figure out how to make this possible in the pivot table itself.

    Where would I enter the =year(cell) = 2010 ?

    Also, in order for a pivot table to make these calculations does it have to be in the main data sheet? and if so, how do I set it up to have the average comparison between the years and also have it show this properly in the pivot table?

    Please help
    Thank you!
    Last edited by Ephy; 09-02-2014 at 07:28 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Sound Alert
    By Raj Mukherji in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-23-2014, 11:41 AM
  2. Sound calculations
    By jag808 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 10-10-2011, 12:40 PM
  3. timer with sound
    By DavidObeid in forum Excel General
    Replies: 2
    Last Post: 01-05-2011, 05:23 AM
  4. Playing sound in VBA
    By Pete in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-10-2005, 09:05 AM
  5. sound
    By pytelium in forum Excel General
    Replies: 3
    Last Post: 07-24-2005, 05:41 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1