Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate the average of numbers, ignoring multiple values | Excel Discussion (Misc queries) | |||
select date range then find average of values in another cell | Excel Worksheet Functions | |||
Average Values / Multiple Worksheets | Excel Worksheet Functions | |||
Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
Can you average data in 1 column based on a range of values in another? | Excel Worksheet Functions |