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

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

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

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

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

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