Average

[AVIDDA]

Hello i am working on building a EXCEL sheet that needs to average from multiable spots from within the spreadsheet. See example:

This is what it looks like (example)
a b c d
1 ab 1 5 a
2 ac 1 10 a
3 aa 2 10 z
4 af 1 15 x
5 aq 2 0 x
6 ap 2 5 r

This is what i need it to look like (e=average for matching (b) numbers)
a b c d e
1 ab 1 5 a 10
2 ac 1 10 a 10
3 aa 2 10 z 5
4 af 1 15 x 10
5 aq 2 0 x 5
6 ap 2 5 r 5

Step two:

a b c d e
1 ab 1 5 a 10
2 ac 1 10 a 10
3 aa 2 10 z 5
4 af 1 15 x 10
5 aq 2 0 x 5
6 ap 2 5 r 5

(Next work sheet)

a b
1 1 10
2 2 5


Been trying different things all day but havent figured it out yet.

Thanks, David

[AVIDDA]

Or you could even skip (column e) and just put it all on the next work sheet like in step two. (I am sure the process is about the same non the less)

[DonkeyOte]

A basic SUMIF/COUNTIF would suffice (or AVERAGEIF if you happen to have XL 2007+)

However, based on your desired output I would suggest you just push your data into a Pivot Table - set Column B as Row Field and Column C as Data Field set to AVERAGE
(you will need header values for each column)

[AVIDDA]

DonkeyOte,

I wish the system had 2007 but no luck. Do you have an example of what a SUMIF/COUNTIF might look like to pull that data? I am trying to not get the sheet into a Pivot table to get the data.

I have attached an example sheet to kind of show a simple set up of what i have

[MarvinP]

Hi Avidda,

Find attached a pivot table of your data. I hope this works for you. They are so easy to make.

[AVIDDA]

MarvinP, Thank! it was i want it to look like but i am still trying to avoid putting every thing into a pivot table. I have attached another copy of what i am looknig for but this time it works in 2007 but not in 2003. (starting to pull hair out) I am not sure why it dosent work but if any one can rework E3's function to make it work for 2003 it would get me back on track.