| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average with more than one IF statement
Hi,
I have used the formula from Bob Philips (10th Sept 06) in my worksheet: =AVERAGE(IF(ISNUMBER('eligible 2% - 1jan'!U3:U501),'eligible 2% - 1jan'!U3:U501)). I have to get the average also per department, so I have to check whether it is the correct department before it calculates the average. Not all info is filled in the U column. For the summation I have used the following: =SUMIF('eligible 2% - 1jan'!$A$3:$A$501,"mt*",'eligible 2% - 1jan'!$AB$3:$AB$501). ex* being a department starting with mt. But for average I have to omit the zero values. Any help is appreciated. Thanks, Lupe |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average with more than one IF statement
Hi,
I have tried this formula, it works, but it does not work with the mt*. I need to average amounts for various departments (various abbreviations) that we need to group together. =AVERAGE(IF((ISNUMBER(D1:D100))*(E1:E100="mt"),D1: D100)) Lupe "Lupe" wrote: Hi, I have used the formula from Bob Philips (10th Sept 06) in my worksheet: =AVERAGE(IF(ISNUMBER('eligible 2% - 1jan'!U3:U501),'eligible 2% - 1jan'!U3:U501)). I have to get the average also per department, so I have to check whether it is the correct department before it calculates the average. Not all info is filled in the U column. For the summation I have used the following: =SUMIF('eligible 2% - 1jan'!$A$3:$A$501,"mt*",'eligible 2% - 1jan'!$AB$3:$AB$501). ex* being a department starting with mt. But for average I have to omit the zero values. Any help is appreciated. Thanks, Lupe |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average with more than one IF statement
=AVERAGE(IF((ISNUMBER(D1:D100))*(left(E1:E100,2)=" mt"),D1:D100))
Lupe wrote: Hi, I have tried this formula, it works, but it does not work with the mt*. I need to average amounts for various departments (various abbreviations) that we need to group together. =AVERAGE(IF((ISNUMBER(D1:D100))*(E1:E100="mt"),D1: D100)) Lupe "Lupe" wrote: Hi, I have used the formula from Bob Philips (10th Sept 06) in my worksheet: =AVERAGE(IF(ISNUMBER('eligible 2% - 1jan'!U3:U501),'eligible 2% - 1jan'!U3:U501)). I have to get the average also per department, so I have to check whether it is the correct department before it calculates the average. Not all info is filled in the U column. For the summation I have used the following: =SUMIF('eligible 2% - 1jan'!$A$3:$A$501,"mt*",'eligible 2% - 1jan'!$AB$3:$AB$501). ex* being a department starting with mt. But for average I have to omit the zero values. Any help is appreciated. Thanks, Lupe -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average with more than one IF statement
Thanks Dave.
The formula works. Is this the argument (LEFT) I should use also in my other formulas of SUMIF's? I assume it is better than the *. Regards, Lupe "Dave Peterson" wrote: =AVERAGE(IF((ISNUMBER(D1:D100))*(left(E1:E100,2)=" mt"),D1:D100)) Lupe wrote: Hi, I have tried this formula, it works, but it does not work with the mt*. I need to average amounts for various departments (various abbreviations) that we need to group together. =AVERAGE(IF((ISNUMBER(D1:D100))*(E1:E100="mt"),D1: D100)) Lupe "Lupe" wrote: Hi, I have used the formula from Bob Philips (10th Sept 06) in my worksheet: =AVERAGE(IF(ISNUMBER('eligible 2% - 1jan'!U3:U501),'eligible 2% - 1jan'!U3:U501)). I have to get the average also per department, so I have to check whether it is the correct department before it calculates the average. Not all info is filled in the U column. For the summation I have used the following: =SUMIF('eligible 2% - 1jan'!$A$3:$A$501,"mt*",'eligible 2% - 1jan'!$AB$3:$AB$501). ex* being a department starting with mt. But for average I have to omit the zero values. Any help is appreciated. Thanks, Lupe -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average with more than one IF statement
=sumif() can support wildcards (like mt*).
You don't need/want to change it. You'll see the other problem when/if you try changing the =sumif() formula. Lupe wrote: Thanks Dave. The formula works. Is this the argument (LEFT) I should use also in my other formulas of SUMIF's? I assume it is better than the *. Regards, Lupe "Dave Peterson" wrote: =AVERAGE(IF((ISNUMBER(D1:D100))*(left(E1:E100,2)=" mt"),D1:D100)) Lupe wrote: Hi, I have tried this formula, it works, but it does not work with the mt*. I need to average amounts for various departments (various abbreviations) that we need to group together. =AVERAGE(IF((ISNUMBER(D1:D100))*(E1:E100="mt"),D1: D100)) Lupe "Lupe" wrote: Hi, I have used the formula from Bob Philips (10th Sept 06) in my worksheet: =AVERAGE(IF(ISNUMBER('eligible 2% - 1jan'!U3:U501),'eligible 2% - 1jan'!U3:U501)). I have to get the average also per department, so I have to check whether it is the correct department before it calculates the average. Not all info is filled in the U column. For the summation I have used the following: =SUMIF('eligible 2% - 1jan'!$A$3:$A$501,"mt*",'eligible 2% - 1jan'!$AB$3:$AB$501). ex* being a department starting with mt. But for average I have to omit the zero values. Any help is appreciated. Thanks, Lupe -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Average with more than one IF statement
Dave,
Thanks again. Lupe "Dave Peterson" wrote: =sumif() can support wildcards (like mt*). You don't need/want to change it. You'll see the other problem when/if you try changing the =sumif() formula. Lupe wrote: Thanks Dave. The formula works. Is this the argument (LEFT) I should use also in my other formulas of SUMIF's? I assume it is better than the *. Regards, Lupe "Dave Peterson" wrote: =AVERAGE(IF((ISNUMBER(D1:D100))*(left(E1:E100,2)=" mt"),D1:D100)) Lupe wrote: Hi, I have tried this formula, it works, but it does not work with the mt*. I need to average amounts for various departments (various abbreviations) that we need to group together. =AVERAGE(IF((ISNUMBER(D1:D100))*(E1:E100="mt"),D1: D100)) Lupe "Lupe" wrote: Hi, I have used the formula from Bob Philips (10th Sept 06) in my worksheet: =AVERAGE(IF(ISNUMBER('eligible 2% - 1jan'!U3:U501),'eligible 2% - 1jan'!U3:U501)). I have to get the average also per department, so I have to check whether it is the correct department before it calculates the average. Not all info is filled in the U column. For the summation I have used the following: =SUMIF('eligible 2% - 1jan'!$A$3:$A$501,"mt*",'eligible 2% - 1jan'!$AB$3:$AB$501). ex* being a department starting with mt. But for average I have to omit the zero values. Any help is appreciated. Thanks, Lupe -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Statement to Compute Average | Excel Worksheet Functions | |||
| plotted Average | Charts and Charting in Excel | |||
| What is this kind of average called? | Excel Worksheet Functions | |||
| Do I need a sumif or sum of a vlookup formula? | Excel Worksheet Functions | |||
| IF Statement with Average Function results in #Value! | Excel Discussion (Misc queries) | |||
Hybrid Mode
