"Average" calculation formula needed

[Guest]

Hello, I have Excel 97, and am trying to learn formulas. I need to find the "average" of certain values in column B below. This is an example of my worksheet
A B
1 TRUE 1.0%
2 FALSE -.8
3 TRUE -.5
4 FALSE -.
5 TRUE 1.
6 TRUE -.
7 FALSE .

What I need: In Cell C50 I want the calculated result in the form of a "%". FIRST finds the "TRUE" ocurrances in column "A", then calculate the average % (both positive and negative) in column "B" that are next to "TRUE" found in column "A". The above example would yield a G50 value of 1.7%
What would the Cell G50 formula look like? I hope to achieve this calculation without manually using "autofilter". Thanks to anyone who can help
Sa

 

[Trevor Shuttleworth]

Sam

try:

=SUMIF(A1:A7,TRUE,B1:B7)/COUNTIF(A1:A7,TRUE)

Regards

Trevor

Hello, I have Excel 97, and am trying to learn formulas. I need to find

the "average" of certain values in column B below. This is an example of my
worksheet:

A B
1 TRUE 1.0%
2 FALSE -.8%
3 TRUE -.5%
4 FALSE -.3
5 TRUE 1.9
6 TRUE -.7
7 FALSE .2

What I need: In Cell C50 I want the calculated result in the form of a

"%". FIRST finds the "TRUE" ocurrances in column "A", then calculate the
average % (both positive and negative) in column "B" that are next to "TRUE"
found in column "A". The above example would yield a G50 value of 1.7%.

What would the Cell G50 formula look like? I hope to achieve this

calculation without manually using "autofilter". Thanks to anyone who can
help!

 

[Frank Kabel]

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER)
=AVERAGE(IF(A2:A100,B2:B100))