Forumla to work out average

[nathan663]

In column B i have a list of numbers eg. 671, 672, 673 etc. these numbers are repeated a number of times. In column O I have a list of numerical numbers that i need to work out the averages for.

So i need a formula that looks up all the 671's in column B and takes their scores from column O and works returns the average.

 

[barry houdini]

Which version of Excel are you using? In Excel 2007 and later you can use AVERAGEIF function

=AVERAGEIF(B:B,671,O:O)

 

[Jonmo1]

Try

=AVERAGE(IF(B1:B100=671,O1:O100))

IMPORTANT
This is an array formula that requires CTRL + SHIFT + ENTER
After entering the formula, highlight the cell with the formula and press F2
Then press CTRL + SHIFT + ENTER
When entered correctly, the formula will be enclosed in {brackets}

Also, you cannot use entire column references like B:B, must use row #s like B1:B100
Unless in XL2007 or higher. And even then it's not recommended.

 

[prabby25101981]

=SUMPRODUCT(--(B1:B100=671),AVERAGE(O1:O100))

Change the range accordingly. Hope this helps!

 

[nathan663]

i am still using 2000, but i will be getting 2007 soon, however i need the formula to work on both versions

thanks

 

[barry houdini]

OK then, you can use Jonmo's array formula suggestion or for a non-array version

=SUMIF(B1:B100,671,O1:O100)/MAX(1,COUNTIF(B1:B100,671))