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

K

Kisamarha

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
 
M

Minty Fresh

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top