Formula Help

J

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
 
G

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
 
J

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
 
J

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
 
R

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
 
J

JimS

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

Thanks again.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top