I have a 'Sumproduct' function working fine on my Excel file, but I need to
use similar method, this time to do the average instead of sum. I need to
evaluate about 4 criteria, then average the numbers from another column. I
know there's no such: AverageProduct function. I have tried to use the
Average(--(sheet1!$A$2:$A$10000=sheet2$M$8)*(sheet1!$B$2:$B$10000=sheet2$A11)-----*(sheet1!$I$2:$I$10000)).
The formula runs ok, but it gets only a value on one row instead of averaging
out all the values on multiple rows.
Is there another way/function of doing this?
Any direction is very much appreciated.

Thanks.
--
when u change the way u look @ things, the things u look at change.

The formula =SUMPRODUCT(--(A1:A100=$F$10), --(B1:B100=%F$11), C1:C100)
will SUM all the C cells having A values equal to F10 and B values equal to
F11
The formula =SUMPRODUCT(--(A1:A100=$F$10), --(B1:B100=%F$11))
will COUNT all the records for which A values equal to F10 and B values
equal to F11
So one over the other will give the average
=SUMPRODUCT(--(A1:A100=$F$10), --(B1:B100=%F$11),
C1:C100)/=SUMPRODUCT(--(A1:A100=$F$10), --(B1:B100=%F$11))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"sahafi" <(E-Mail Removed)> wrote in message
news50F071A-1CE1-4130-AAC6-(E-Mail Removed)...
>I have a 'Sumproduct' function working fine on my Excel file, but I need to
> use similar method, this time to do the average instead of sum. I need to
> evaluate about 4 criteria, then average the numbers from another column. I
> know there's no such: AverageProduct function. I have tried to use the
> Average(--(sheet1!$A$2:$A$10000=sheet2$M$8)*(sheet1!$B$2:$B$10000=sheet2$A11)-----*(sheet1!$I$2:$I$10000)).
> The formula runs ok, but it gets only a value on one row instead of
> averaging
> out all the values on multiple rows.
> Is there another way/function of doing this?
> Any direction is very much appreciated.
>
> Thanks.
> --
> when u change the way u look @ things, the things u look at change.

Hi!

Try it like this:

=AVERAGE(IF((rng1=criteria1)*(rng2=criteria2)*(rng3=criteria3)*(rng4=criteria4),rng_to_avg))

This is an array formula. It must be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER).

Biff

"sahafi" <(E-Mail Removed)> wrote in message
news50F071A-1CE1-4130-AAC6-(E-Mail Removed)...
>I have a 'Sumproduct' function working fine on my Excel file, but I need to
> use similar method, this time to do the average instead of sum. I need to
> evaluate about 4 criteria, then average the numbers from another column. I
> know there's no such: AverageProduct function. I have tried to use the
> Average(--(sheet1!$A$2:$A$10000=sheet2$M$8)*(sheet1!$B$2:$B$10000=sheet2$A11)-----*(sheet1!$I$2:$I$10000)).
> The formula runs ok, but it gets only a value on one row instead of
> averaging
> out all the values on multiple rows.
> Is there another way/function of doing this?
> Any direction is very much appreciated.
>
> Thanks.
> --
> when u change the way u look @ things, the things u look at change.

Bill and Bernard, thank you both. I managed to get it to work as:

IF(ISERROR(AVERAGE(IF(COL1=W8,IF(COL1=A11,IF(COL3=B11,IF(COL4=V8,COL5))))),"0",AVERAGE(IF---------------------------))))))

and it worked perfectly.

Thanks.
--
when u change the way u look @ things, the things u look at change.


"Biff" wrote:

> Hi!
>
> Try it like this:
>
> =AVERAGE(IF((rng1=criteria1)*(rng2=criteria2)*(rng3=criteria3)*(rng4=criteria4),rng_to_avg))
>
> This is an array formula. It must be entered using the key combination of
> CTRL,SHIFT,ENTER (not just ENTER).
>
> Biff
>
> "sahafi" <(E-Mail Removed)> wrote in message
> news50F071A-1CE1-4130-AAC6-(E-Mail Removed)...
> >I have a 'Sumproduct' function working fine on my Excel file, but I need to
> > use similar method, this time to do the average instead of sum. I need to
> > evaluate about 4 criteria, then average the numbers from another column. I
> > know there's no such: AverageProduct function. I have tried to use the
> > Average(--(sheet1!$A$2:$A$10000=sheet2$M$8)*(sheet1!$B$2:$B$10000=sheet2$A11)-----*(sheet1!$I$2:$I$10000)).
> > The formula runs ok, but it gets only a value on one row instead of
> > averaging
> > out all the values on multiple rows.
> > Is there another way/function of doing this?
> > Any direction is very much appreciated.
> >
> > Thanks.
> > --
> > when u change the way u look @ things, the things u look at change.
>
>
>

Sorry, I meant Biff.
--
when u change the way u look @ things, the things u look at change.


"Biff" wrote:

> Hi!
>
> Try it like this:
>
> =AVERAGE(IF((rng1=criteria1)*(rng2=criteria2)*(rng3=criteria3)*(rng4=criteria4),rng_to_avg))
>
> This is an array formula. It must be entered using the key combination of
> CTRL,SHIFT,ENTER (not just ENTER).
>
> Biff
>
> "sahafi" <(E-Mail Removed)> wrote in message
> news50F071A-1CE1-4130-AAC6-(E-Mail Removed)...
> >I have a 'Sumproduct' function working fine on my Excel file, but I need to
> > use similar method, this time to do the average instead of sum. I need to
> > evaluate about 4 criteria, then average the numbers from another column. I
> > know there's no such: AverageProduct function. I have tried to use the
> > Average(--(sheet1!$A$2:$A$10000=sheet2$M$8)*(sheet1!$B$2:$B$10000=sheet2$A11)-----*(sheet1!$I$2:$I$10000)).
> > The formula runs ok, but it gets only a value on one row instead of
> > averaging
> > out all the values on multiple rows.
> > Is there another way/function of doing this?
> > Any direction is very much appreciated.
> >
> > Thanks.
> > --
> > when u change the way u look @ things, the things u look at change.
>
>
>