Average formular

[Justin]

Hello,

I am trying to work out the average turn over for the year
of a supermarket. I have =Average(B1:B52)

But the true average doesn't show until all 52 weeks have
been put in.

Is there a way to work out the average as the year goes on?
EG:
week 1 $13235.92
Week 2 $18231.56 Average = $15733.74
Week 3 $16453.76 Average = $15973.74

Not like current
Week 1 $13235.92 Average = $254.54
Week 2 $18231.56 Average = $605.14
Week 3 $16453.76 Average = $921.56

 

[DDM]

Justin, I bet the reason why this formula =AVERAGE(B1:B52) is giving you an
"incorrect" result is because you've entered zeros in the cells below Week
3. The AVERAGE function is dividing by 52, which is why you're getting the
result you're getting. Just take the zeros out (select them and Edit > Clear

All) and you'll get the result you expected.

BTW, If you wanna keep a running average like the one in your example below,
enter this in the first row (Week 1): =AVERAGE(B$1:B1) and copy it down, one
week at a time.

 

[Robert Christie]

Hi Justin
Run this from row 1 down to row 52
=IF(B1="","",AVERAGE(B$1:B1))
Adjust range to suit.

Hth
Bob C

 

[2rrs]

Hello,

I am trying to work out the average turn over for the year
of a supermarket. I have =Average(B1:B52)

But the true average doesn't show until all 52 weeks have
been put in.

Is there a way to work out the average as the year goes on?
EG:
week 1 $13235.92
Week 2 $18231.56 Average = $15733.74
Week 3 $16453.76 Average = $15973.74

Not like current
Week 1 $13235.92 Average = $254.54
Week 2 $18231.56 Average = $605.14
Week 3 $16453.76 Average = $921.56

Try this:

=AVERAGE(IF(B1:B52<>0,B1:B52))

 

[2rrs]

Hello,

I am trying to work out the average turn over for the year
of a supermarket. I have =Average(B1:B52)

But the true average doesn't show until all 52 weeks have
been put in.

Is there a way to work out the average as the year goes on?
EG:
week 1 $13235.92
Week 2 $18231.56 Average = $15733.74
Week 3 $16453.76 Average = $15973.74

Not like current
Week 1 $13235.92 Average = $254.54
Week 2 $18231.56 Average = $605.14
Week 3 $16453.76 Average = $921.56

Sorry forgot to mention that this must be confirmed using control, shift, enter
=AVERAGE(IF(A120:A139<>0,A120:A139))

 

[John]

Justin,

You will need a different formula for each cells on the average
column.

For the week 2 average, try using this formula "=AVERAGE($A$1:A2)"

And, for the week 3, use "=AVERAGE($A$1:A3)"

All you have to do is type in the week 2 average formula and a copy
the formula all the way down to the 52th week.

The trick is the "$A$1", it locks that cell when you copy-n-paste.


Regards,

John
http://www.sulogic.com

 

[John]

Test

 

[Guest]

Thank you to everyone that replied.
The average is now working. (I did have all zeros)