Average only some values

[Andreas]

Hi

My workbook contains, amongs other, a column with values for the
temperature in a pipe, and three other columns with values indicating
the time three different machines has been on. I'd like to calculate
the average of all values in the first colum, but only the values
where any of the machine has been turned on.

For instance: The first colum contains the values {10,10,12,12,13},
the other contains the values (0,0,1,0,0), (1,0,0,0,0), (1,0,0,0,0).
Here the first average should be 11, because only the first and the
third value is of intrest.

How should I do that?

 

[Frank Kabel]

Hi
one way
=SUMPRODUCT(--(((B1:B5)+(C1:C5)+(D15))>0),A1:A5)/SUMPRODUCT(--(((B1:B
5)+(C1:C5)+(D15))>0))

 

[Leo Heuser]

Hi

Another option:

=AVERAGE(IF(B2:B6+C2:C6+D26,A2:A6))

This is an array formula, and it must be entered with
<Shift><Ctrl><Enter> also if edited later. If done
correctly, Excel will display the formula in the formula
bar enclosed in curly brackets { }. Don't enter these
brackets yourself.