Average only some values

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?

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

--
Regards
Frank Kabel
Frankfurt, Germany

Andreas wrote:
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?

Hi

Another option:

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

This is an array formula, and it must be entered with
ShiftCtrlEnter 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.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Andreas" skrev i en meddelelse
m...
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?