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]

Can you post the formula that works when there is data in all cells, and a sample set of data?

 

[Dearster]

=SUM(B13;F13;J13;N13;R13;V13;Z13;AD13;AH13;AL13;AP13;AT13;AX13)/COUNTIF(B13;F13;J13;N13;R13;V13;Z13;AD13;AH13;AL13;AP13;AT13;AX13,">0")

although that produces an Err:508

The normal SUM/COUNT produces a #DIV/0!


Period 1 Period 2 Period 3
Week 1 Week 2 Week 3 Week 4 Week 1 Week 2 Week 3 Week 4 Week 1 Week 2 Week 3
Sunday 23.00% 100.00% 100.00% 100.00% %
Monday 100.00% 23.00% 23.00% 100.00%
Tuesday 23.00% 100.00% 85.00% 100.00%
Wednesday 100.00% 100.00% 100.00% 100.00%
Thursday 100.00% 100.00% 100.00% 100.00% 43.00%
Friday 100.00% 100.00% 100.00% 100.00%
Saturday 100.00% 100.00% 100.00% 100.00%

Week To Date 78.00% 89.00% 86.86% 100.00% #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 43.00%

Period To Date 88.46% #DIV/0! 43.00%

Year To Date Err:508

 

[Dearster]

I would post the data in Excel Jeanie but I am using Open Office Calc currently.

 

[Jonmo1]

Try this array formula entered with CTRL + SHIFT + ENTER

This will still error if NONE of the cells have a value in it.
But it works fine if at least 1 cell is populated.

=AVERAGE(IF(MOD(COLUMN(B13:AX13),4)=2,IF(B13:AX13>0,B13:AX13)))


IMPORTANT
This is an array formula
After entering the formula, highlight the cell with the formula and press F2
Then press CTRL + SHIFT + ENTER
When entered correctly, the formula will be enclosed in {brackets}


Hope that helps.

 

[Dearster]

Still coming up with Err: 508

 

[Dearster]

Any chance I can email you the entire spreadsheet with formulas I have on it so far please?

 

[Dearster]

It is literally just the yearly number that isn't working

 

[Dearster]

Or email anyone the spreadsheet that may be able to help? It's a very small spreadsheet. Nothing too complicated.

 

[delaneyjm]

OpenOffice Calc doesn't use commas for argument separators. Try this version of jonmo1's formula:

=AVERAGE(IF(MOD(COLUMN(B13:AX13);4)=2;IF(B13:AX13>0;B13:AX13)))

You still need to confirm with Ctrl-Shift-Enter.