Need a simple formula for conditional average

[Terry B.]

[Apologize if this has been covered beaucoups times but I did a search
of "conditional average" and couldn't get a decent answer ;-]
I know this is an EASY question, and it's driving me nuts that I
can't figure it. All I need is a formula that'll average the values in
one column--GIVEN the fact that they match a criteria in another
column. [Blank &/or null value cells are not an issue here.] Here's a
quick example:
A B C
RaceDate MaxSpeed BikeNumber
3/6/01 22 15
4/6/01 18 1
5/6/01 25 7
6/6/01 21 1
7/6/01 17 15
8/6/01 23 7

ALL I'M LOOKING FOR is a formula that will calc an average of
'MaxSpeed' but only for a certain 'BikeNumber'. I've tried
"=AVERAGE(IF("BikeNumber"=1))) and variations of same, but can't hit
on it. I'd appreciate any help on this; thanks for your time &
attention to detail.

Terry.

 

[A.W.J. Ales]

Terry,
Try :

=SUMPRODUCT((B2:B7)*(BC1:C7=1))/COUNTIF(C2:C7;"=1")

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Terry B.]

Terry,
Try :

=SUMPRODUCT((B2:B7)*(BC1:C7=1))/COUNTIF(C2:C7;"=1")

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

[Apologize if this has been covered beaucoups times but I did a search
of "conditional average" and couldn't get a decent answer ;-]
I know this is an EASY question, and it's driving me nuts that I
can't figure it. All I need is a formula that'll average the values in
one column--GIVEN the fact that they match a criteria in another
column. [Blank &/or null value cells are not an issue here.] Here's a
quick example:
A B C
RaceDate MaxSpeed BikeNumber
3/6/01 22 15
4/6/01 18 1
5/6/01 25 7
6/6/01 21 1
7/6/01 17 15
8/6/01 23 7

ALL I'M LOOKING FOR is a formula that will calc an average of
'MaxSpeed' but only for a certain 'BikeNumber'. I've tried
"=AVERAGE(IF("BikeNumber"=1))) and variations of same, but can't hit
on it. I'd appreciate any help on this; thanks for your time &
attention to detail.

Terry.

TO AUK:
Thanks for taking the time BUT that idea didn't work. ("The formula
you typed contains an error" etc.) Anybody else got an idea???

 

[Terry B.]

Terry,
Try :

=SUMPRODUCT((B2:B7)*(BC1:C7=1))/COUNTIF(C2:C7;"=1")

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

[Apologize if this has been covered beaucoups times but I did a search
of "conditional average" and couldn't get a decent answer ;-]
I know this is an EASY question, and it's driving me nuts that I
can't figure it. All I need is a formula that'll average the values in
one column--GIVEN the fact that they match a criteria in another
column. [Blank &/or null value cells are not an issue here.] Here's a
quick example:
A B C
RaceDate MaxSpeed BikeNumber
3/6/01 22 15
4/6/01 18 1
5/6/01 25 7
6/6/01 21 1
7/6/01 17 15
8/6/01 23 7

ALL I'M LOOKING FOR is a formula that will calc an average of
'MaxSpeed' but only for a certain 'BikeNumber'. I've tried
"=AVERAGE(IF("BikeNumber"=1))) and variations of same, but can't hit
on it. I'd appreciate any help on this; thanks for your time &
attention to detail.

Terry.

TO AUK:
Thanks for taking the time BUT that idea didn't work. ("The formula
you typed contains an error" etc.) Anybody else got an idea???

TO AUK & others: Looked around in other sources and got it figured
out...Have an excellent day :-]

 

[A.W.J. Ales]

Terry,

I don't know which solution you did finf on your own.
If you still want to try : In my first reply I used the argumentseparator ;
(semicolon)

I think that if you replace this with , (comma) the formula will work.

The semicolon is used in the European setting, which I forgot to mention in
my reply.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

(e-mail address removed) (Terry B.) wrote in message

"A.W.J. Ales" <[email protected]> wrote in message

Terry,
Try :

=SUMPRODUCT((B2:B7)*(BC1:C7=1))/COUNTIF(C2:C7;"=1")

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

[Apologize if this has been covered beaucoups times but I did a search
of "conditional average" and couldn't get a decent answer ;-]
I know this is an EASY question, and it's driving me nuts that I
can't figure it. All I need is a formula that'll average the values in
one column--GIVEN the fact that they match a criteria in another
column. [Blank &/or null value cells are not an issue here.] Here's a
quick example:
A B C
RaceDate MaxSpeed BikeNumber
3/6/01 22 15
4/6/01 18 1
5/6/01 25 7
6/6/01 21 1
7/6/01 17 15
8/6/01 23 7

ALL I'M LOOKING FOR is a formula that will calc an average of
'MaxSpeed' but only for a certain 'BikeNumber'. I've tried
"=AVERAGE(IF("BikeNumber"=1))) and variations of same, but can't hit
on it. I'd appreciate any help on this; thanks for your time &
attention to detail.

Terry.

TO AUK:
Thanks for taking the time BUT that idea didn't work. ("The formula
you typed contains an error" etc.) Anybody else got an idea???

TO AUK & others: Looked around in other sources and got it figured
out...Have an excellent day :-]