+ Reply to Thread
Results 1 to 8 of 8

average of data by date to probability formula steps help

  1. #1
    Registered User
    Join Date
    11-19-2014
    Location
    Knoxville, Tennessee
    MS-Off Ver
    2010
    Posts
    11

    average of data by date to probability formula steps help

    Hi. I have a large set of prediction market data assorted by date (dates in column A). The data runs from columns B through to Y with some blank cells. I wish to create an average of this data by date. in The z column (one total average for each day). In the column AA I wish to have the calculation of 1/average to give me an average daily probability. Can someone help with the formula required to perform these functions please. I have attached a screen grab to show what part of my spreadsheet looks like. I have attached one of my workbooks in case any kind soul wants to have a bash working this out.excel screen grab.PNG
    Attached Files Attached Files
    Last edited by jamie2v; 11-19-2014 at 06:13 PM. Reason: adding workbook

  2. #2
    Registered User
    Join Date
    11-19-2014
    Location
    Knoxville, Tennessee
    MS-Off Ver
    2010
    Posts
    11

    Re: average of data by date to probability formula steps help

    I know I can do it by using an averaging formula (for example Cell Z46 =AVERAGE(B2:Y46)) and just use this formula for all dates. My issue is I have months of data and would like that formula to repeat automatically for every date. The same for the 1/average, I could do it manually but would like a formula that auto calculates this value for every average.

  3. #3
    Registered User
    Join Date
    11-19-2014
    Location
    Knoxville, Tennessee
    MS-Off Ver
    2010
    Posts
    11

    Re: average of data by date to probability formula steps help

    I take it there probably isn't a solution to this and i will have to do it manually?

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

    Re: average of data by date to probability formula steps help

    I can't tell what you're trying to do, but a good start would be to unmerge all the cells in col A, and replicate the dates.

    EDIT: Unmerge ALL of the cells in the workbook and replicate data as necessary.
    Last edited by shg; 11-19-2014 at 08:40 PM.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

    Re: average of data by date to probability formula steps help

    May be this.....

    In a new sheet get all unique dates. One way to get that is,

    A2, then copy down until get blank.

    =IFERROR(SMALL(Sheet1!A:A,ROWS(A$2:A2)),"")

    B2, with CTRL+SHIFT+ENTER, then copy down.

    Please Login or Register  to view this content.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  6. #6
    Registered User
    Join Date
    11-19-2014
    Location
    Knoxville, Tennessee
    MS-Off Ver
    2010
    Posts
    11

    Re: average of data by date to probability formula steps help

    Thank you so much for both your replies. Shg unmerging was good advice thanks. As for understanding what I want, basically I just want a daily average of all data in a new column and in another column I want the calculation #1/daily average. Haseeb I'm not sure I follow how I'm supposed to implement those instructions (I think I'm being dumb), would it be possible for you to edit the workbook I have posted or further explain those instructions?

  7. #7
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

    Re: average of data by date to probability formula steps help

    See attached.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    11-19-2014
    Location
    Knoxville, Tennessee
    MS-Off Ver
    2010
    Posts
    11

    Re: average of data by date to probability formula steps help

    Thank you so much for that!

+ 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. Month to Date and Quarter to Date Average from Daily data
    By asvanthi in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-15-2014, 07:10 AM
  2. [SOLVED] Formula to find a date range and date average
    By 1Monkey in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-14-2012, 11:51 AM
  3. Probability/Formula Help
    By jamesryan in forum Excel General
    Replies: 1
    Last Post: 08-10-2007, 06:36 PM
  4. Probability Question:probability outcomes
    By Myles in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-01-2006, 06:45 AM
  5. [SOLVED] [SOLVED] Creating a merged probability table from a granular probability table
    By misterhanky@gmail.com in forum Excel General
    Replies: 1
    Last Post: 09-08-2005, 10:05 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