How could I calc an averageif in a similar way to the sumif function?

i.e. Average if A2 = B2 from C2

Bruce

=average(if(a1:a1017,a1:a10))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

This will average only the numbers in A1:A10 that are greater than 17.

(I'm not quite sure how to translate your example, though.)

Bruce wrote:

How could I calc an averageif in a similar way to the sumif function?

i.e. Average if A2 = B2 from C2

Bruce

--

Dave Peterson

Ok, thanks Dave,

I used =AVERAGE(IF('On Promotion'!$D58:$BC58=0,$D58:$BC58)) in my sheet and
it works.

My next step is to add a second condition to my sheet like this

=AVERAGE(IF(AND('On Promotion'!$D58:$BC58=0,D58:BC58<0),$D58:$BC58)) but it
equals zero but it shouldn't.

basically i am trying to average if cond1 = 0 and cond2 < 0.

Any ideas?

Bruce

"Dave Peterson" wrote:

=average(if(a1:a1017,a1:a10))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

This will average only the numbers in A1:A10 that are greater than 17.

(I'm not quite sure how to translate your example, though.)

Bruce wrote:

How could I calc an averageif in a similar way to the sumif function?

i.e. Average if A2 = B2 from C2

Bruce

--

Dave Peterson

Try:

=AVERAGE(IF(('On Promotion'!$D58:$BC58=0)*(D58:BC58<0),$D58:$BC58) )



Bruce wrote:

Ok, thanks Dave,

I used =AVERAGE(IF('On Promotion'!$D58:$BC58=0,$D58:$BC58)) in my sheet and
it works.

My next step is to add a second condition to my sheet like this

=AVERAGE(IF(AND('On Promotion'!$D58:$BC58=0,D58:BC58<0),$D58:$BC58)) but it
equals zero but it shouldn't.

basically i am trying to average if cond1 = 0 and cond2 < 0.

Any ideas?

Bruce

"Dave Peterson" wrote:

=average(if(a1:a1017,a1:a10))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

This will average only the numbers in A1:A10 that are greater than 17.

(I'm not quite sure how to translate your example, though.)

Bruce wrote:

How could I calc an averageif in a similar way to the sumif function?

i.e. Average if A2 = B2 from C2

Bruce

--

Dave Peterson


--

Dave Peterson