Divide By Zero Error

[Dearster]

I'm trying to calculate the average percentage of a rolling 28 days and running year however when using SUM and COUNT it is coming up with a #Div/0! error and therefore not letting me calculate the rolling 28 day average or yearly average unless each week has a value in it. How can I get around this?

Thanks.

 

[Jonmo1]

Yep, good catch delaneyjm.

 

[Jonmo1]

It's working for me..

Make sure to use ; instead of ,
And it's important to enter the formula with CTRL + SHIFT + ENTER

Excel Workbook
	A	B	F	J	N	R	V	Z	AD
13	3	1	2	3	4	5
Sheet1

 

[Dearster]

Not working as I hoped it would:


Sunday 85.00% 23.50% 87.00% 100.00% 90.00%
Monday 87.50% 98.70% 68.00% 20.00% 87.00%
Tuesday 89.00% 23.00% 56.00% 87.00% 67.00%
Wednesday 98.00% 45.00% 87.00% 100.00%
Thursday 92.50% 87.00% 89.00% 20.00%
Friday 78.00% 28.00% 90.00% 20.00%
Saturday 23.40% 12.00% 100.00% 10.00%

Week To Date 79.06% 45.31% 82.43% 51.00% 81.33%

Period To Date 64.45% 81.33%

Year To Date 72.89%


For that set of data:

The Year to Date should read 66.08% but instead is reading the 72.89%.

I'm not sure why :S

 

[Jonmo1]

Sorry, I can't make heads or tails out of your sample data set, and how it relates to the formula you posted in post#3.

Will you be able to get to Excel and Use the Jeanie any time soon?

 

[Dearster]

I can save it in .xls format and then email you the data?

 

[Dearster]

A more accurate description of the pattern required:

Each day is individual.
Weekly Average - Calculated Fine using 7 individual days.
Period Average (4 weekly) - Calculated Fine using 28 individual days.
Year average (13 Periods) - Calculated incorrectly using the 13 period values.

Currently the YTD average is calculated using the 13 x period value averages as opposed to the 365 individual days which make up the year. The correct way I am looking for is each day to contribute equally towards the YTD average. So if 50 days of data were entered. It would add up all of the percentages of the days and divide by 50 to provide the YTD average. The formula needs to disregard any days not yet reached (symbolised by no data entered).

I hope this clears up any issues and sorry for my vague explanations.

Many Thanks.

 

[Jonmo1]

OK, I've seen the file.

First I'll post with Jeanie so other eyes can see...

I've cleared the data from the 9th week and beyond, so only 8 weeks of data is included. For display in the forum purposes.
Just assume the pattern continues on for 52 weeks
Formulas remain as they were when I recieved the file.
I'll post some comments next..

Excel Workbook
	A	B	C	D	E	F	G	H	I
2		Week 1	Week 2	Week 3	Week 4	Week 1	Week 2	Week 3	Week 4
3	Sunday	100.00%	100.00%	100.00%	100.00%	100.00%
4	Monday	100.00%	100.00%	100.00%	100.00%	100.00%
5	Tuesday	100.00%	100.00%	100.00%	100.00%	100.00%	10.00%		20.00%
6	Wednesday	100.00%	100.00%	100.00%	100.00%	20.00%			20.00%
7	Thursday	100.00%	100.00%	100.00%	100.00%	10.00%		20.00%	20.00%
8	Friday	100.00%	100.00%	100.00%	100.00%	10.00%		10.00%	10.00%
9	Saturday	100.00%	100.00%	100.00%	100.00%	10.00%	10.00%	10.00%	10.00%
10
11	Week To Date	100.00%	100.00%	100.00%	100.00%	50.00%	10.00%	13.33%	16.00%
12
13	Period To Date	100.00%				28.82%
14
15	Year To Date	64.41%
Sheet1

 

[Jonmo1]

Looks to me like the formulas are working.

But I see a flaw in the logic..


The average as a whole, is not necessarily the same as the average of sub averages.
If there are blanks in the ranges, then the count of each sub average is not the same.
Basically, if the COUNT part of the average is not the same accross each sub average, then the average of the sub averages will not be the same as the average as a whole.

I know, clear as mud right?

See this example

Excel Workbook
	A	B	C
2	97	87.5	2
3
4	78
5	23	44.66666667	3
6	78
7	33
8	85	48	2
9
10	11
11
12
13	57.8571429	60.05555556
Sheet2

So you have to decide on a design point of view which average you want.

The average as a whole, or the Average of the Averages.
They are NOT necessarily the same thing.


Hope that helps.

 

[Jonmo1]

Now, to suggest a resolution...

Why change the method of average from "Period to date" to "Year to date"

If this
=SUM(B3:E9)/MAX(1,COUNT(B3:E9))
Is good for your Period AVerage, then apply the same logic for the whole year.

=SUM(B3:AX9)/MAX(1,COUNT(B3:AX9))

 

[Jonmo1]

If you really want the Average of the Averages, then the formula I posted earlier is correct.
=AVERAGE(IF(MOD(COLUMN(B13:AX13),4)=2,IF(B13:AX13>0,B13:AX13)))
Enterd with CTRL + SHIFT + ENTER

If you really want the Average as a whole, then you want
=SUM(B3:AX9)/MAX(1,COUNT(B3:AX9))


Replace , with ;