+ Reply to Thread
Results 1 to 15 of 15

Creating a rolling average

  1. #1
    Registered User
    Join Date
    08-13-2017
    Location
    Adelaide, Australia
    MS-Off Ver
    Office 365
    Posts
    16

    Creating a rolling average

    Hi all,

    First time poster, I have done quite a lot of searching trying to work out how to do this. Have found a lot of examples but not one that quite works for what I need it to do.

    In sheet 1 (TEAM SHEETS), I want to create a 3 game rolling average (D6) for the data in sheet 2, D5, F5, and H5. And as the data is entered for J5 I receive a new 3 game average rolling average for F5, H5 and J5 in that same cell.

    For example, the corresponding data in D5, F5, and H5 should return a 3 game rolling average of 4.3 in Sheet 1 D6. As the next value is entered into J5, Sheet 1 D6 will then change to represent the new 3 game average of 6.16. And so forth until the 82nd game at the end of the worksheet.

    I would also like to do this same process for a 5 game average in Sheet 1 E6.

    I hope that makes sense, if any one could help me out that would be great!

    I have attached the relevant spreadsheet.
    Attached Files Attached Files
    Last edited by Bradleypike; 08-13-2017 at 10:25 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,101

    Re: Creating a rolling average

    Welcome to the forum!

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Registered User
    Join Date
    08-13-2017
    Location
    Adelaide, Australia
    MS-Off Ver
    Office 365
    Posts
    16

    Re: Creating a rolling average

    Thank you!

    I have edited my original post to include the file attachment.

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,471

    Re: Creating a rolling average

    I should rearange the data in a table (rows instead of columns).

    After that you can easily analyse the data with a pivot table.

    I showed this for game 1 - 5 for ATLANTA HAWKS, just to give you an idea.

    See the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Registered User
    Join Date
    08-13-2017
    Location
    Adelaide, Australia
    MS-Off Ver
    Office 365
    Posts
    16

    Re: Creating a rolling average

    Sorry I should note, the data in Column B, Sheet 2, is the total average for the data in each individual row.

    I am a bit of a newbie when it comes to excel, but I don't think that this completely works for the job I need it to do as I only have approximately 30 minutes each day to enter the new data for each game and then analyse the data for what I need it to do.

    This way looks a lot more labour intensive than I unfortunately have time for each day. Thus I was hoping there was a function/formula out there for me which would recalculate the data automatically as it is entered each day.

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,471

    Re: Creating a rolling average

    That is the way I should go.

    Maybe other forummembers has other idea's for you.

    New idea.

    Maybe you can make a formular with dropdownboxes to fill the data.

    Probably this will increase the inputtime.

  7. #7
    Registered User
    Join Date
    08-13-2017
    Location
    Adelaide, Australia
    MS-Off Ver
    Office 365
    Posts
    16

    Using MATCH function as a reference point for finding a range using the OFFSET function

    Hi,

    Sorry I seem to be running into a lot of trouble with this spreadsheet Im working on.

    I have been trying to find ways I can create a rolling average without inputting a whole lot of data each day.

    I am hoping that I have found a solution to this problem. Just need the aid of some experts to help me get there.

    What I want to do is create a 3 game rolling average in "TEAM SHEETS" Cell D6.

    I believe I can do this by using the AVERAGE, OFFSET and MATCH functions together.

    I dont quite understand the OFFSET function completely, but think that I will be able to use the MATCH function to find the reference point (To find "TEAM SHEETS" Cell A2, in "Sheet 1" Column A.

    The data I want to find the averages of are in Sheet 1 Column C.

    I tried this formula to get it to work but had no luck.

    =AVERAGE(OFFSET(MATCH('TEAM SHEETS'!A2,Sheet1!A3:G84,0),0,0,-2,2))

    If you could help me that would be amazing!
    Attached Files Attached Files

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,101

    Re: Using MATCH function as a reference point for finding a range using the OFFSET functio

    Is this the same query as the other thread on rolling averages? If so, then you should continue there.

  9. #9
    Registered User
    Join Date
    08-13-2017
    Location
    Adelaide, Australia
    MS-Off Ver
    Office 365
    Posts
    16

    Re: Using MATCH function as a reference point for finding a range using the OFFSET functio

    I will delete the old thread, I have constructed a new table to manipulate the data.
    Last edited by Bradleypike; 08-14-2017 at 06:25 AM.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,101

    Re: Using MATCH function as a reference point for finding a range using the OFFSET functio

    Threads merged.

  11. #11
    Registered User
    Join Date
    08-13-2017
    Location
    Adelaide, Australia
    MS-Off Ver
    Office 365
    Posts
    16

    Re: Using MATCH function as a reference point for finding a range using the OFFSET functio

    Ive solved it!!!

    =AVERAGE(OFFSET(INDEX(Sheet1!$A$3:$A$84,MATCH('TEAM SHEETS'!A2,Sheet1!$A$3:$A$84,0)),,2,-3,1))

    Got to love the elation when you work it out

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,101

    Re: Creating a rolling average

    Well done!!!

  13. #13
    Registered User
    Join Date
    08-13-2017
    Location
    Adelaide, Australia
    MS-Off Ver
    Office 365
    Posts
    16

    Re: Creating a rolling average

    I have come across another small problem. When trying to copy paste this formula to other cells it comes up with a #N/A. However when I type the formula in manually it seems to work fine?

    Is there a quick resolution to this issue?

  14. #14
    Registered User
    Join Date
    08-13-2017
    Location
    Adelaide, Australia
    MS-Off Ver
    Office 365
    Posts
    16

    Re: Creating a rolling average

    I have my calculation options set to automatically calculate but still no luck!

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,101

    Re: Creating a rolling average

    When you copy and paste, any cell reference that is not fixed will change based on its new relative location.

+ 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. Rolling Average
    By cory0789 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-20-2017, 03:56 PM
  2. [SOLVED] Rolling Average
    By T15K in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-14-2013, 06:38 PM
  3. [SOLVED] 5 Day rolling average
    By spazzcdnm in forum Excel General
    Replies: 3
    Last Post: 07-17-2012, 04:27 PM
  4. Rolling average
    By gth8991 in forum Excel General
    Replies: 3
    Last Post: 10-03-2011, 11:57 AM
  5. rolling average
    By William Okumu in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-25-2006, 10:49 AM
  6. Rolling 3 mth Average
    By dallin in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-22-2005, 12:15 PM
  7. [SOLVED] Rolling Average
    By Bearcats_85 in forum Excel General
    Replies: 7
    Last Post: 07-19-2005, 11:05 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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.07942 seconds
  • Memory Usage 9,306KB
  • Queries Executed 16 (?)
More Information
Template Usage (34):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (15)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (3)postbit_attachment
  • (15)postbit_legacy
  • (15)postbit_onlinestatus
  • (15)postbit_wrapper
  • (4)showthread_bookmarksite
  • (7)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (49):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_attachment
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1