+ Reply to Thread
Results 1 to 8 of 8

Month statistics help

  1. 03-10-2014, 07:01 PM #1
    lucykputman
    lucykputman is offline
    Registered User
    Join Date
    03-10-2014
    Location
    Denver, CO
    MS-Off Ver
    Excel 2010
    Posts
    4

    Question Month statistics help

    Hi everyone--
    I have an excel spreadsheet that is basically a record keeping log. I have the dates I received the document, the Number/name of the document and if it was filed or incomplete (a "1" for if it was filed, and a "0" for if it is incomplete). I am trying to analyze the data so i can see how often they are incomplete vs how often they are filed (by month). I have one giant spreadsheet that is consistently growing, and ideally, i'd like to have a new spreadsheet tab per month and on each of those tabs have a formula that can refer back to the giant data sheet and if the document was received within the month of January (for example), have the formula calculate the percentage of filed vs. incomplete.
    ive been playing around with a few different formulas but am not having much luck. if anyone has any ideas or have been in a similar situation and came up with a different approach,
    Please let me know!!
    Reply With QuoteRegister To Reply
  2. 03-10-2014, 07:21 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Month statistics help

    Hi and welcome to the forum

    Im not sure you need a different WS for each month. Without even seeing your data, either 1 Pivot Table, or 1 table usint index/match based on a cell with the month in it. comes to mind.

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 03-12-2014, 11:46 AM #3
    lucykputman
    lucykputman is offline
    Registered User
    Join Date
    03-10-2014
    Location
    Denver, CO
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Month statistics help

    sample.xlsx


    This is a version of my spreadsheet that is much smaller and all information has been removed and replaced with pointless info...
    thank you so much for your help!


    (PS. I just opened it from here and for some reason it separated it into two pages width. not sure why).
    Reply With QuoteRegister To Reply
  4. 03-31-2014, 02:17 PM #4
    lucykputman
    lucykputman is offline
    Registered User
    Join Date
    03-10-2014
    Location
    Denver, CO
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Month statistics help

    Still wondering if someone can help me with my excel question.
    i just need to be able to know the stats per month...
    Reply With QuoteRegister To Reply
  5. 03-31-2014, 02:35 PM #5
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Month statistics help

    Hi,

    See the attached which uses a Pivot Table
    Attached Files Attached Files
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
  6. 03-31-2014, 03:26 PM #6
    lucykputman
    lucykputman is offline
    Registered User
    Join Date
    03-10-2014
    Location
    Denver, CO
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Month statistics help

    That seems to be what i need!
    Will it update itself as i add new information?
    Also-- is there a way to have it calculate percents?
    I've never used a pivot table before, so i'm not even sure how you made this....
    Reply With QuoteRegister To Reply
  7. 03-31-2014, 03:57 PM #7
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Month statistics help

    Take a look at the attached, it uses regular formulas to pull out what you need. Instead of a sheet per month, I put it all in 1 table, see if that is something you can work with?
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 03-31-2014, 06:26 PM #8
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Month statistics help

    Hi,

    I've created a dynamic range name called data which will expand automatically as you add records to the Receiving log sheet and used that range name as the source for the pivot table.

    I've put a small macro line in the Sheet Activate event of the PT sheet so that the pivot table will automatically refresh when you select it.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Auto instert Month names for This month, Last month and Next month
    By hemal89 in forum Word Programming / VBA / Macros
    Replies: 4
    Last Post: 12-10-2013, 12:01 PM
  2. Change YTD for a data sheet showing statistics to update to the new year statistics
    By Stephr in forum Excel General
    Replies: 1
    Last Post: 07-20-2012, 06:55 AM
  3. Statistics
    By chiller in forum Excel General
    Replies: 6
    Last Post: 05-23-2010, 10:11 AM
  4. statistics help
    By capt_nemo777 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-31-2009, 12:44 AM
  5. p-value, statistics
    By wim rademakers in forum Excel General
    Replies: 1
    Last Post: 01-17-2006, 10:30 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1