Formula Help

[JimS]

I worked on this for four hours last night and finally gave up.

$10.00 2
$20.00 2
$10.00
$10.00
$30.00 2

I want to take the average of column A when it corresponds to an item
in column B. Column B could be a variety of numbers such as 1, 2, 3
4, etc. So I'm not multiplying. I'm simply counting the number of
instances in column B that match a dollar amount in Column A and then
averaging that total.

There are three instances of 2 in column B so the sum of those matches
in Column A is $60. Divide that by three and I should get an answer
of $20.00

 

[Gary''s Student]

=AVERAGE(IF(B1:B5=2,A1:A5))

must be entered as an array formula with CNTRL-SHFT-ENTER rather than just
the ENTER key

 

[JimS]

Thanks. I forgot to mention something. Your formula works if there
is data in every cell in column A, but there is not. Both columns
will have some blank cells. How should I alter the formula if column
A has blanks?

$10.00 2
2
$12.00 2

$8.00
$20.00 2

Now the answer should be $14.00

 

[Bob Phillips]

That works just fine as given, just make sure the ranges are correct.

 

[JimS]

OK, I must be doing something wrong then. Here is what I have and I
get 7.5 instead of 15 using this formula:

{=AVERAGE(IF(B1:B5=2,A1:A5))}


$10.00 2 7.5
2
$10.00
2
$20.00 2

 

[Ron Rosenfeld]

OK, I must be doing something wrong then. Here is what I have and I
get 7.5 instead of 15 using this formula:

{=AVERAGE(IF(B1:B5=2,A1:A5))}


$10.00 2 7.5
2
$10.00
2
$20.00 2

I can reproduce your results.

To obtain the "correct" answer, by not counting "no-entry" cells in a1:a5, try
this formula (entered normally)

=SUMIF(B1:B5,2,A1:A5)/SUMPRODUCT((B1:B5=2)*(LEN(A1:A5)>0))

If you have Excel 2007, you can use this formula:

=AVERAGEIF(B1:B5,2,A1:A5)
--ron

 

[JimS]

Hey thanks! That got it. Uh, I have 2007. Do the earlier versions
of Excel not support "average if?"

Thanks again.

 

[Ron Rosenfeld]

Do the earlier versions
of Excel not support "average if?"

They do not support AVERAGEIF
--ron