+ Reply to Thread
Results 1 to 13 of 13

Complicated averaging-- averaging data that matches certain intervals

  1. #1
    Registered User
    Join Date
    05-22-2014
    Posts
    22

    Complicated averaging-- averaging data that matches certain intervals

    Greetings ExcelForum community,

    I have tens of thousands of datapoints that I have to average and doing it by hand is out of the question.

    Basically, I have a long set of "distances" and data taken from each of those distances. I need to average the data within a certain distance interval. For instance, I need to average the temperature taken between 1ft and 9ft. I've made a dummy-sheet so feel free to check that out. Any help would be so greatly appreciated! I've been working on this for weeks so I believe it's time I reach out for help. Please let me know if there are any clarifications you need.

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Complicated averaging-- averaging data that matches certain intervals

    In F2, enter =AVERAGE(IF($B$2:$B$10>=D2,IF($B$2:$B$10<E2,$A$2:$A$10,""))) with Ctrl + Shift + Enter keys together since it is an array formula

  3. #3
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Complicated averaging-- averaging data that matches certain intervals

    for from 1 to less than 10 ft
    =AVERAGEIFS(B2:B10,B2:B10,">="&D2,B2:B10,"<"&E2)
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Complicated averaging-- averaging data that matches certain intervals

    Try this user defined function.

    Please Login or Register  to view this content.
    Open the VBA editor by hitting Alt F11.
    Insert a new module with Insert - Module
    Paste in the above function
    Go back to the sheet by hitting Alt F11.

    In a F2, enter =AverageSpecial(E2,D2,B2:B10,A2:A10) and copy down

    Remember to save the workbook as a macro enabled workbook .xlsm
    Martin

  5. #5
    Registered User
    Join Date
    05-22-2014
    Posts
    22

    Re: Complicated averaging-- averaging data that matches certain intervals

    JieJenn and martindwilson,

    Those are great! They worked. However, they don't come out with the exact averages I'm looking for. That is, I'm looking for exact number "4" as the average. Instead, for these formulas I'm getting 4 plus some fraction. Is there a way to change these to be more accurate?

    And mrice, I really appreciate the thoroughness of your suggestion! However I'm looking for a simple function that I can input and that everyone in my office will be able to backtrack (although, believe me, if this were a better solution I wish I could take it)

  6. #6
    Registered User
    Join Date
    05-22-2014
    Posts
    22

    Re: Complicated averaging-- averaging data that matches certain intervals

    Quote Originally Posted by atung View Post
    JieJenn and martindwilson,

    Those are great! They worked. However, they don't come out with the exact averages I'm looking for. That is, I'm looking for exact number "4" as the average. Instead, for these formulas I'm getting 4 plus some fraction. Is there a way to change these to be more accurate?

    And mrice, I really appreciate the thoroughness of your suggestion! However I'm looking for a simple function that I can input and that everyone in my office will be able to backtrack (although, believe me, if this were a better solution I wish I could take it)
    *exact number 4 as the average to distance between 1 and 10

  7. #7
    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: Complicated averaging-- averaging data that matches certain intervals

    If F2 and copy down,

    =AVERAGEIFS($A$2:$A$10, $B$2:$B$10, ">=" & D2, $B$2:$B$10, "<" & E2)
    Entia non sunt multiplicanda sine necessitate

  8. #8
    Registered User
    Join Date
    05-22-2014
    Posts
    22

    Re: Complicated averaging-- averaging data that matches certain intervals

    Quote Originally Posted by shg View Post
    If F2 and copy down,

    =AVERAGEIFS($A$2:$A$10, $B$2:$B$10, ">=" & D2, $B$2:$B$10, "<" & E2)
    Shg, that might've done the trick! I'll try it on my dataset now and will let you know how it goes!

  9. #9
    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: Complicated averaging-- averaging data that matches certain intervals

    It's martin's formula; he just misread which column was to be averaged.

  10. #10
    Registered User
    Join Date
    05-22-2014
    Posts
    22

    Re: Complicated averaging-- averaging data that matches certain intervals

    Quote Originally Posted by shg View Post
    It's martin's formula; he just misread which column was to be averaged.
    Thanks Martin and shg for the correction

    I have another question I was hoping you could answer--- do you know how I can track to see which raw data values my formula computed into the average? I have large datasets with numbers all over the place. I need to be able to compute the data by hand to prove that the formula works but it isn't practical. Any ideas?

  11. #11
    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: Complicated averaging-- averaging data that matches certain intervals

    You're in for a long, hard slog if you feel the need to verify that Excel formulas work as advertised.

  12. #12
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Complicated averaging-- averaging data that matches certain intervals

    There is a user defined function solution to this second challenge.

    Please Login or Register  to view this content.

  13. #13
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Complicated averaging-- averaging data that matches certain intervals

    You might try several of the solutions and then use the EXACT function to see if they agree if you have to prove that the formulae are working to someone else.

+ 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. Averaging data at 30-sec intervals.
    By wade1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-05-2013, 12:33 PM
  2. Data Averaging every 30 seconds for uneven time intervals
    By Frankrocks in forum Excel General
    Replies: 1
    Last Post: 08-29-2013, 07:20 AM
  3. Averaging numbers at intervals
    By RPGreg in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-06-2013, 02:30 PM
  4. Complicated Averaging in 3D
    By Dodgeboy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-04-2008, 10:55 PM
  5. averaging time stamped data chuncks at regular row intervals
    By edwardsn2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-05-2007, 01:29 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