Average of multiple range with error values

[Werner Rohrmoser]

Hi,

I'd ,like to calculate the average of Machine 1 through 2 and Machine
5 through 11.
So the range is not in one piece.
Further error values have to be excluded.
For a regular range I use "{=AVERAGE(IF(ISNUMBER(C35:C45);C35:C45))}
for example.

Machine 1 4.546
Machine 2 3.923
Machine 3 - New #NV
Machine 4 - New #NV
Machine 5 10.567
Machine 6 6.861
Machine 7 #NV
Machine 8 9.096
Machine 9 9.347
Machine 10 3.198
Machine 11 #NV

TIA
Werner

Excel 2002 SP3

 

[Bob Phillips]

I am not understanding, what do you need beyond the formula you already
have. It excludes the non-numbers already.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Werner Rohrmoser]

Bob,

my problem is that Machine 3 and 4 is not included, so I do not have
one range.
What I'm looking for is a formula which can handle multiple
independant ranges.

My formula should be:"{=MITTELWERT(WENN(ISTZAHL((C35:C36;C39:C45));
(C35:C36;C39:C45)))}
but this doesn't work.

Werner

 

[Bob Phillips]

Werner,

Machine 3 and 4 is being excluded because the values in C37 and C38 are not
numbers, so it works as you want.

You could use

=MITTELWERT(WENN(ISTZAHL(C35:C36);C35:C36);WENN(ISTZAHL(C39:C45);C39:C45))

which is again an array formula, but I can't see that it is neceesarry.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Werner Rohrmoser]

Bob,

that's exactly what I need, excellent!

It's necessary becaues I exclude Machine 3 and 4 because they are
other types of machines
and in later years (it's a dataseries, which starts in the year 1999)
they have numbers.
So I have to exclude them also when they have numbers.

Best Regards
Werner

 

[Bob Phillips]

Okay, at least I went beyond my prejudices to give you a solution <vbg>

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)