Results 1 to 15 of 16
Thread: Formula for running average

20150323, 18:37 #1
 Join Date
 Jan 2004
 Location
 Las Vegas, Nevada, USA
 Posts
 349
 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 nonblank cells, sum, and give average.
Any ideas,
Thanks,Richard Spring

20150323, 19:06 #2
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,901
 Thanks
 420
 Thanked 1,587 Times in 1,435 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.
HTHMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

The Following User Says Thank You to RetiredGeek For This Useful Post:
Ron Davis (20150402)

20150323, 20:09 #3
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,924
 Thanks
 152
 Thanked 747 Times in 679 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

20150327, 15:12 #4
 Join Date
 Jan 2004
 Location
 Las Vegas, Nevada, USA
 Posts
 349
 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

20150327, 19:23 #5
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,924
 Thanks
 152
 Thanked 747 Times in 679 Posts
RSring,
Just be careful. Average can accommodate blanks but it can't for zeros.
average1.png

20150329, 15:39 #6
 Join Date
 Jan 2004
 Location
 Las Vegas, Nevada, USA
 Posts
 349
 Thanks
 1
 Thanked 0 Times in 0 Posts
Thanks for the reminder. Will leave cells blank until numbers added.
Richard Spring

20150402, 07:59 #7
 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; 20150402 at 08:04.
Don Guillett
Excel Developer
dguillett @gmail.com

20150402, 10:06 #8
 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.)

20150402, 10:48 #9
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,399
 Thanks
 164
 Thanked 635 Times in 603 Posts
Hi Don
Zeddy's will also average the "dreaded space bar" blank
zeddy

20150402, 11:34 #10
 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

20150402, 12:39 #11
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,901
 Thanks
 420
 Thanked 1,587 Times in 1,435 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

20150402, 12:55 #12
 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

20150402, 13:40 #13
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,901
 Thanks
 420
 Thanked 1,587 Times in 1,435 Posts
S.S.,
Cool!May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20150402, 13:59 #14
 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 sumhttps://windowssecrets.com/forums/images/smilies/cheers.gifhttps://windowssecrets.com/forums/images/smilies/fanfare.gif
Don Guillett
Excel Developer
dguillett @gmail.com

20150402, 16:42 #15
 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