# Thread: Formula for running average

1. ## 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,

2. 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

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

Ron Davis (2015-04-02)

4. 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. Thank you gentlemen for your assistance.
I went with the =AVERAGE(range) which works very well.

6. RSring,

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

average1.png

7. Thanks for the reminder. Will leave cells blank until numbers added.

8. 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)

9. 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. Hi Don

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

zeddy

11. Zeddy, As I said, try it using the example.
Touching the space bar is NOT the same as the delete key.

12. 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!

13. 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)

14. S.S.,

Cool!

15. 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

16. 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 Last

#### Posting Permissions

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