How do I ignore blank cells while averaging the solutions of equat
 

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

How do I ignore blank cells while averaging the solutions of equat
 

take a look at the subtotal function and see if it meets your needs.



--


Gary Keramidas
Excel 2003


"Kisamarha" wrote in message
...
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

How do I ignore blank cells while averaging the solutions of equat
 

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