Average of multiple range with error values
 

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

Average of multiple range with error values
 

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" wrote in message
...
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

Average of multiple range with error values
 

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

Average of multiple range with error values
 

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(IS TZAHL(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" wrote in message
...
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

Average of multiple range with error values
 

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

Average of multiple range with error values
 

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)

"Werner Rohrmoser" wrote in message
...
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