Running average formula (in same cell)

**wonderdunder** · 02-27-2011, 05:43 AM

I need a formula that will show the average of a series of data:

In column E, a series of data is entered one at a time (E9:E13) which is calculated from the value of other cells.

E14 is to show the average of data entered between E9:E13. But it needs to show the average only for total number of figures entered (so if E9:E11 has been entered, E14 will show the average of E9:E11, similarly if E9:E12 has been entered, then B1 will show the average of E9:E12).

The default value of cells in row E is 0 which may complicate matters, as 0 is a valid entry.

I know I could have several cells for each average, but that is not what is needed, nor do I want to have to manually change the range that E14 refers to.

Can anyone help? I've attached the spreadsheet if it helps.

**zbor** · 02-27-2011, 05:46 AM

AVERAGE formula don't include blank cells so you just put =AVERAGE(A1:A7)

**royUK** · 02-27-2011, 05:48 AM

Does the AVERAGE Function not work?

**wonderdunder** · 02-27-2011, 05:56 AM

My apologies - I've made few changes since you have posted.

I was told that the average function would average cells with 0, which is a number that could be entered, but is also currently the default value?

I'll give it a try and see what happens.

**wonderdunder** · 02-27-2011, 06:00 AM

I see! It will average 0 but not blank.

I'll carry on with some re-jigging.

Thanks for the replies.

**wonderdunder** · 02-27-2011, 06:10 AM

Erm.... This is getting complicated!

For this to work, I'm going to have to set almost every cell to an if statement to set it to blank if no data is entered, or can anyone think of a better solution (bearing in mind that I don't want to change the range manually or have several different cells showing all the averages)?

**zbor** · 02-27-2011, 06:17 AM

Can you upload example?

**wonderdunder** · 02-27-2011, 06:25 AM

It's attached on my first post.

**wonderdunder** · 02-27-2011, 07:03 AM

Think I've cracked it - I think. Have attached the sheet with the revisions.

Basically, I've just put IF statements in so that cells containing formulas are blank if the cells in B are blank.

If anyone wouldn't mind just having a wizz through it to confirm.....