I can't get this done without adding another row, but you can always hide the
extra row.
With your data in B4:P4 arranged Items/MH/W, enter these formulas below each
W cell
In D5 =IF(ISERROR(B4/C4/D4),0,B4/C4/D4)
G5 =IF(ISERROR(E4/F4/G4),0,E4/F4/G4)
J5 =IF(ISERROR(H4/I4/J4),0,H4/I4/J4)
M5 =IF(ISERROR(K4/L4/M4),0,K4/L4/M4)
P5 =IF(ISERROR(N4/O4/P4),0,N4/O4/P4)
These replaces the DIV/0 errors with zero and calculate the the daily results
In Q4
=IF(COUNTIF(D5:P5,">0")=5,SUM(D5:P5)/5,(SUM(D5:P5)/COUNTIF(D5:P5,">0")))
This ignores the days with no job and calculates the weekly average based on
the number of days the the work actually happened.
Not pretty, but it works.
"Kisamarha" wrote:
> I'm trying to average (items produced/manhours/workers) over the course of a
> week. The problem is each job isnt worked every day, so I have blank cells
> in my equation. Here is what I'm typing:
>
> =AVERAGE((B4/C4/D4),(E4/F4/G4),(H4/I4/J4),(K4/L4/M4),(N4/O4/P4)).
>
> I know there is a way to ignore #DIV/0 results, but I'm fairly new to Excel.
> I'm also trying to keep this all in one row to save space. Any help will be
> appreciated.
>
> -Ryan
|
Similar Threads
