# Forumla to work out average

#### nathan663

##### Board Regular
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.

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### barry houdini

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

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

#### Jonmo1

##### MrExcel MVP
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

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

Change the range accordingly. Hope this helps!

#### nathan663

##### Board Regular
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

##### MrExcel MVP
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))

1,106,856
Messages
5,513,815
Members
408,971
Latest member
kay_dee8