+ Reply to Thread
Results 1 to 4 of 4

Creating custom stats updating with a data table

  1. #1
    Registered User
    Join Date
    09-01-2011
    Location
    Bartertown
    MS-Off Ver
    Excel 2007
    Posts
    78

    Creating custom stats updating with a data table

    customstats.xlsx

    So I have two columns. One with dates, the other with either "Bid" or "Offer". I'm trying to create a time series on a daily / weekly basis whereby I calculate bid / ( bid+offer) or other random stats. I need the stats column to automatically follow new data entries. The user won't touch the custom stats column.

    Any suggestions?

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,511

    Re: Creating custom stats updating with a data table

    i would post your example again

    i have used a calculated field
    Attached Files Attached Files
    Last edited by etaf; 12-29-2013 at 10:01 AM.
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    09-01-2011
    Location
    Bartertown
    MS-Off Ver
    Excel 2007
    Posts
    78

    Re: Creating custom stats updating with a data table

    If I have 6 or so columns each with 3 or so categories this would turn into a massive spreadsheet if I used this method. The other problem is that someone entering data won't want to set this up.
    I've played around with using if(cell="", ..., ...) statements and filled in many more cells then necessary so the stats will automatically update but the problem is my spreadsheet ballooned to over 15mb. Any other ideas?

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,511

    Re: Creating custom stats updating with a data table

    if they just enter the two data elements into column A and B ,then the formula in C and D will copy down as its setup as a table
    then on the pivot table - just need to click on the refresh button to update

    so only extra step is the refresh button - nothing else to "set-up"
    statements and filled in many more cells then necessary so the stats will automatically update
    what else have you had to update , other than what is in the attached spreadsheet example

    re size

    try deleting any extra sheets
    also all the columns and rows - you may have touched
    the size of the workbook will increase if you select a blank cell and have lots of columns and rows unnecessary in the workbook
    for example
    i reduced from 33K to 25K just by doing that

    I got to the row after the data
    and then
    Shift+Control+downArrow keys
    which selects all the blankrows and then delete
    and with the columns
    goto the column after the data
    Shift+Control+rightArrow keys
    and delete all the columns
    Last edited by etaf; 12-30-2013 at 08:18 AM.

+ 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. [SOLVED] Creating Tables from a main table that is updating from an external source.
    By beat in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-15-2013, 09:20 AM
  2. Creating a self updating Data Validation List
    By TMS in forum Tips and Tutorials
    Replies: 14
    Last Post: 11-01-2012, 10:02 AM
  3. Creating Update Button for either new entry or updating existing data
    By howieG in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-11-2012, 07:26 AM
  4. Creating a Custom Pivot Table Report Filter
    By csivell in forum Excel General
    Replies: 0
    Last Post: 08-08-2012, 02:21 PM
  5. Creating charts with custom data formats
    By Bastiej in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-20-2011, 05:20 AM

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