How to find the Average of cells containing a value

[Shirley Munro]

Hi

I am working on a file which involves calculating what capacity a
machine is working to compared with the target capacity. The machine is
capable of being used to produce a variety of different parts and
therefore somedays will produce only one particular part and on other
days it may be used to produce a variety of different parts. I am
trying to calculate percentage usage of the machine over a 24 hour day
based on Target Figures.

See attached file

Column B contains the part, Column C contains how many of each part was
produced each day, column D contains the number of hours the machine was
used to produce each part, Column E contains the target number of parts
which could be produced over a 24 hours period, Column F calculates the
Actual number of parts which would have been produced over 24 hours and
contains an If statement which is =If(C3=0,0,C3/D3*24). This was done
to stop the #DIV/0 error from appearing when the formula was copied
down cells containing no values.

In Column G I want to calculate the percentage of Actual Target against
the Target by dividing these 2 values. Where there is a 0 value, I get
0% which is fine but when I try to calculate the Daily average for this
column in cell G17, it includes the 0 percentages as well as those with
a percentage value.

Is there away of getting the average function to only include cells
with a value over 0%

Thanks

Shirley Munro


+-------------------------------------------------------------------+
|Filename: ExcelForum.doc |
|Download: http://www.excelforum.com/attachment.php?postid=4342 |
+-------------------------------------------------------------------+

 

[vezerid]

Shirley,

consider an array formula like

=AVERAGE(IF(A1:A100>0, A1:A100))

confirmed with Shift+Ctrl+Enter. It averages over a virtual array of
either the nonzero numbers or FALSE in their place, which does not
count for average.

HTH
Kostis Vezerides