Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Las Vegas, Nevada, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Formula for running average

    With Excel 2010, I have a column where I add a number on a daily basis.
    I need a formula that will count the number of numbers entered in the column, sum the column, then divide by the number of numbers entered. Each time a number is added (on daily basis) the formula cell will recount non-blank cells, sum, and give average.

    Any ideas,

    Thanks,
    Richard Spring

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Richard,

    You don't need anything fancy just an Average formula that references a dynamic range name.
    See the attached workbook: RichardAverage.xlsx

    See the formula in A1 and Under Formulas->Name Manager the definition of the Entries Range Name.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Ron Davis (2015-04-02)

  4. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    RG,

    Can I suggest that you define your dynamic range from

    =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$2002 ),1)
    to
    =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$2002 )+COUNTBLANK(Sheet1!$A$2:$A$2002),1)

    Using your formula, if a row is inserted within the range of numbers, COUNTA() alone will not change and the last row that moves down will not be counted. If a value within the range is made blank, COUNTA() will be reduced by one shortening your range and again, the last row will not be counted.

    In the image below, A5 has been made blank. COUNTA() will drop to 4 and the range "Entities" will change to A2:A5 instead of A2:A6 leaving AVERAGE(A2:A5) = 2.

    Whereas, COUNTA() + COUNTBLANK() will equal 5 leaving the range at A2:A6 and AVERAGE(A2:A6) = 2.75. Cell B1 has the amended named range

    Average.png

    If I am looking at this wrong, my apologies.

    Maud

  5. #4
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Las Vegas, Nevada, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you gentlemen for your assistance.
    I went with the =AVERAGE(range) which works very well.
    Richard Spring

  6. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    RSring,

    Just be careful. Average can accommodate blanks but it can't for zeros.

    average1.png

  7. #6
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Las Vegas, Nevada, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks for the reminder. Will leave cells blank until numbers added.
    Richard Spring

  8. #7
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Maud, Although Average will work just fine a simpler defined name could be Col A
    =OFFSET($a$1,0,0,MATCH(999999,$a:$a),1)
    If done on the active sheet, Excel will fill in the sheet name automatically. 999999 can simply be any number larger than is possible in your column. BTW. Zeddy's will also average the "dreaded space bar" blank the same as a zero
    (touching the space bar is NOT the same as a blank, try it)
    Last edited by Supershoe; 2015-04-02 at 09:04.
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  9. #8
    New Lounger
    Join Date
    Sep 2014
    Posts
    11
    Thanks
    1
    Thanked 0 Times in 0 Posts
    A slight modification of RetiredGeek's dynamic range name's expression is

    =OFFSET(Sheet1!R2C1,0,0,COUNTA(Sheet1!C1),1)

    Thus, one does not have to assume a maximum length for the set of numbers. It's also slightly simpler. (My preference for RC notation is a separate matter.)

  10. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,830
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi Don

    Zeddy's will also average the "dreaded space bar" blank
    ..where is that???

    zeddy

  11. #10
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Zeddy, As I said, try it using the example.
    Touching the space bar is NOT the same as the delete key.
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  12. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ron,

    Nice Touch!

    For the RC notation challenged Ron's formula in A1 notation:
    =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)

    Here's why I did it MY WAY!

    When using data tables/lists I don't allow blank lines. YMMV!
    I limit the number of rows just to save Excel some work (probably not necessary with today's processors but that's just how I've been doing it like forever and us old folks, at least this one, finds it hard to change ).

    Oh well enough rationalization for today. Nice job both Maud & Ron!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #12
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Hey RG, I know you boys in SC can't learn old tricks.
    I am in Texas and will be 79 on IRS day and like to learn new tricks. I teach here and I learn here. New tricks is GOOD.
    =OFFSET($a$1,0,0,MATCH(999999,$a:$a),1)
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  14. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    S.S.,

    Cool!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #14
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    If that little guy has wild turkey 101 in the mug, I wuld like sumhttp://windowssecrets.com/forums/images/smilies/cheers.gifhttp://windowssecrets.com/forums/images/smilies/fanfare.gif
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  16. #15
    New Lounger
    Join Date
    Dec 2009
    Location
    North Wales, UK
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If you can tolerate the average being displayed in a different column to the data, a simple whole column reference seems to work OK. Eg, if the data is in column A, put the formula in any other column: =AVERAGE(A:A). This will average just the numbers (including zeroes and the valid results of formulae) in column A, but ignore blanks, spaces and text (so you can have a column heading). It will also survive adding, deleting and sorting rows.

    Nick

Page 1 of 2 12 LastLast

Posting Permissions

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