Help on a Average fomula

[PMRetired2012]

Lets try this again. What i have is a sheet setup with weekly amount entered in each cell. Going to give you a example.
J6:M6 equals the month of Novenber (4 weeks) im entering weekly attendance
in J6 & K6 i have 45 and 47..L6,M6 i have no numbers entered yet because we havent got to those weeks yet
What i want to have is a average of attendance based on each week and all the totals be put in Cell H32
Ehample: week 1 the averave to date would be 45 (in cell H32)
week 2 the average to date would be 46...(45+47=92..92/2=46) Cell H32 would change to 46
week 3 hasnt got here yet. but if we had 56 on week 3 the average would be 49..(45+47+56=148..148/3=49) Cell H32 would change to 49

so what i need is a formula that figures the running average for each week as i add attendences in cells J6:K6 and the answer show up in cell H32.

Home i made since on the post. i have tried this a couple times

Dennis

 

[Toadstool]

As long as later weeks are empty then AVERAGE will do this. (NOTE: The decimals can either be ignored with a format or you can wrap INT() around the formula).

Book1
	H	I	J	K	L	M
5			November
6			45	47	56
7
8
31	Average
32	49.33333333
Sheet1

Cell Formulas
Range	Formula
H32	H32	=AVERAGE(J6:M6)

 

[PMRetired2012]

Why when i put this formula in the cell H32 i show a answer of 37. Someting is not right with my excel or somthing .
That fromula does work for me not sure why. any ideas

Dennis

 

[PMRetired2012]

What mine is doing is dividing it by 4 i think. what i need is it to divide by what week we are on...1,2,3,4

 

[Toadstool]

What mine is doing is dividing it by 4 i think. what i need is it to divide by what week we are on...1,2,3,4

You must have null in M6 so it ignores week 4 but I suspect you have a zero

If you must have zero for as yet unused weeks then try:
=AVERAGEIF(J6:M6,">0")

 

[PMRetired2012]

Toadstool,
The formula that you game me (=AVERAGEIF(J6:M6,">0")works great with the exception of when i put that formula in the december totals where there hasnt been any numbers for week 1-4 i get a error message.
Then error message is: Divide by 0 error. and i have ##### in Cells.
Is there any way you can fix that in the Formula?

Dennis

 

[Toadstool]

If there are no entries for November then yes, you will get a divide by zero error.

The easiest way to stop this would be by trapping the error:
=IFERROR(AVERAGEIF(J6:M6,">0"),"")

 

[PMRetired2012]

Thanks for you help..it all worked good