Sorting, grabbing, and averaging a lot of data into a few cells by date...
Results 1 to 5 of 5

Thread: Sorting, grabbing, and averaging a lot of data into a few cells by date...

  1. #1
    Board Regular
    Join Date
    May 2009
    Posts
    152
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sorting, grabbing, and averaging a lot of data into a few cells by date...

    I need some assistance trying to grab some data from one column and move it to another.
    Lets say I have column P with dates of the month, the data in this column will change at times.
    Say 10 samples per day.

    Column Q has a number for each sample.

    I would like to scan P and report the date once in Column R and then average each days sample and place the average in S.

    I realize it sounds crazy, but this is what Im trying to do. Any ideas or suggestions are appreciated.

    "P" is date
    "Q" is number
    "R" is one of each date in Column "P"
    "S" is average of the data of each day in "R"

    An example is below:

    P Q R S
    5/29/19 124 5/29/2019 124
    5/30/2019 124 5/30/2019 126
    5/30/2019 124 5/31/2019 126
    5/30/2019 125 6/1/2019 137
    5/30/2019 126
    5/30/2019 126
    5/30/2019 126
    5/30/2019 127
    5/30/2019 127
    5/30/2019 128
    5/30/2019 128
    5/31/2019 129
    5/31/2019 129
    5/31/2019 128
    5/31/2019 127
    5/31/2019 126
    5/31/2019 125
    5/31/2019 124
    5/31/2019 124
    5/31/2019 126
    5/31/2019 130
    6/1/2019 134
    6/1/2019 137
    6/1/2019 140

  2. #2
    Board Regular DRSteele's Avatar
    Join Date
    Mar 2015
    Location
    Calgary
    Posts
    1,972
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Sorting, grabbing, and averaging a lot of data into a few cells by date...

    The new functions in Excel 365 make quick work of this type of task. A PivotTable also does it in a jiffy.

    Here is the formulaic solution with Excel 365 Insider functions.

    PQRS
    1DatesValueDateAvg Value
    25/29/20191245/29/2019124.0
    35/30/20191245/30/2019126.1
    45/30/20191245/31/2019126.8
    55/30/20191256/1/2019137.0
    65/30/2019126
    75/30/2019126
    85/30/2019126
    95/30/2019127
    105/30/2019127
    115/30/2019128
    125/30/2019128
    135/31/2019129
    145/31/2019129
    155/31/2019128
    165/31/2019127
    175/31/2019126
    185/31/2019125
    195/31/2019124
    205/31/2019124
    215/31/2019126
    225/31/2019130
    236/1/2019134
    246/1/2019137
    256/1/2019140

    Sheet37



    Worksheet Formulas
    CellFormula
    R2=UNIQUE(P2:P25)
    S2=AVERAGEIFS(Q2:Q25,P2:P25,R2#)


    Last edited by DRSteele; Jun 17th, 2019 at 06:53 PM.
    Windows10, Excel 365 Insider
    If you don't declare what version of Excel, we will assume
    Windows10, Excel 365.
    Formulas here are always in
    Green background
    You can get the HTML Maker: https://www.mrexcel.com/forum/excel-...ins-links.html.

  3. #3
    Board Regular
    Join Date
    May 2009
    Posts
    152
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sorting, grabbing, and averaging a lot of data into a few cells by date...

    Quote Originally Posted by DRSteele View Post
    The new functions in Excel 365 make quick work of this type of task. A PivotTable also does it in a jiffy.
    Wow, that is perfect, exactly what I want to do...Not sure I can do though with Office 2010.
    Thank you!

  4. #4
    Board Regular
    Join Date
    May 2009
    Posts
    152
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sorting, grabbing, and averaging a lot of data into a few cells by date...

    Curious if there is another way to use the "=UNIQUE", we did get Office 2016, but not even close still.
    Note: September 24, 2018: The UNIQUE function is one of several beta features, and currently only available to a portion of Office Insiders at this time. We'll continue to optimize these features over the next several months. When they're ready, we'll release them to all Office Insiders, and Office 365 subscribers.

  5. #5
    Board Regular
    Join Date
    May 2009
    Posts
    152
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sorting, grabbing, and averaging a lot of data into a few cells by date...

    Quote Originally Posted by DRSteele View Post
    The new functions in Excel 365 make quick work of this type of task. A PivotTable also does it in a jiffy.

    Here is the formulaic solution with Excel 365 Insider functions.

    Oh, thanks for the suggestion of Pivot tables, I've never done before, but did manage to get it to work with that.
    Now for some fine tuning!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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