Calculating the Average for non consecutive cells using custom for

[Guest]

Hello,

My worksheet is fairly simple. I am trying to calculate average time per
call between two sites. So for Monday, Site 1 would have 00:04:00 for 4
minutes in cell B2, Site 2 would have 00:03:30 for 3:30 (3 minutes 30
seconds) in cell B3. Cell B4 simply calculates the average of the two sites
for that day using the AVERAGE function.

The difficulty is when I am trying to then total the averages of the daily
totals, so for cell B4, then B8, B12, B16, and B20 (for Monday through Friday
averages) totalling to cell B24. If I only have data for Monday in cell B4,
I get an error in B24 because the other cells have the #DIV/0 error. I've
searched the boards and the closest array I can find for this is
=IF(ISERROR(AVERAGE(B4,B8,B12,B16,B20)),"",AVERAGE(B4,B8,B12,B16,B20)) to
place in the B24 cell. This returns a blank cell though.

Could the problem be the formula used or the custom cell format? I'm using
the mm:ss format for the cell.
I appreciate any help.
Thanks

 

[Bernard Liengme]

Replace the Average formula in B4 by =IF(B2>0,AVERAGE(B2:B3),"")
Copy this to B8,B12,B16,B20
in B4 use either =SUM(B4,B8,B12,B16,B20) or =AVERAGE(B4,B8,B12,B16,B20) as
needed. Note that =B4+B8+B12+B16+B20 will fail because of the spaces. But
using =IF(B2>0,AVERAGE(B2:B3),0) would allow you to use that formula.

best wishes

 

[Guest]

Hi Bernard,

Worked like a charm, thanks so much!