Conditional averaging gives wrong result
 

I've got a problem in a spreadsheet which, if I simplify down to its most
basic is as follows.

In the table below, the average value of column B where the corresponding
value in column A is "A" should be 100. Yet the answer I get with the
following array formula is 33.3333

={AVERAGE((A1:A3="A")*(G32:G34))}

A 100
B 200
C 300


It appears that excel is recognising the correct value for the condition
equal to A (100) but then assigning zeros to the other before calculating.

Have I got the wrong syntax in that formula. Any suggestions to calculate
the correct answer.

Tx


Rob

Conditional averaging gives wrong result
 

Try:
=AVERAGE(IF(.........))

ctrl+shift+enter, not just enter


"BRob" wrote:

I've got a problem in a spreadsheet which, if I simplify down to its most
basic is as follows.

In the table below, the average value of column B where the corresponding
value in column A is "A" should be 100. Yet the answer I get with the
following array formula is 33.3333

={AVERAGE((A1:A3="A")*(G32:G34))}

A 100
B 200
C 300


It appears that excel is recognising the correct value for the condition
equal to A (100) but then assigning zeros to the other before calculating.

Have I got the wrong syntax in that formula. Any suggestions to calculate
the correct answer.

Tx


Rob

Conditional averaging gives wrong result
 

As an array formula, committed by pressing Ctrl-Shift-Enter

=average(if(A1:a3="A",G32:G34))

"BRob" wrote:

I've got a problem in a spreadsheet which, if I simplify down to its most
basic is as follows.

In the table below, the average value of column B where the corresponding
value in column A is "A" should be 100. Yet the answer I get with the
following array formula is 33.3333

={AVERAGE((A1:A3="A")*(G32:G34))}

A 100
B 200
C 300


It appears that excel is recognising the correct value for the condition
equal to A (100) but then assigning zeros to the other before calculating.

Have I got the wrong syntax in that formula. Any suggestions to calculate
the correct answer.

Tx


Rob

Conditional averaging gives wrong result
 

BRob wrote:
I've got a problem in a spreadsheet which, if I simplify down to its most
basic is as follows.

In the table below, the average value of column B where the corresponding
value in column A is "A" should be 100. Yet the answer I get with the
following array formula is 33.3333

={AVERAGE((A1:A3="A")*(G32:G34))}

A 100
B 200
C 300


It appears that excel is recognising the correct value for the condition
equal to A (100) but then assigning zeros to the other before calculating.

Have I got the wrong syntax in that formula. Any suggestions to calculate
the correct answer.

Tx


Rob




{=AVERAGE(IF(A1:A3="A",G32:G34,""))}