Conditional Averages

[lwilde]

Hi,

I'm trying to figure out if there is a formula I could use that will help me calculate the average of a group cells in one column based on the condition of another column. It's hard to explain, so I will show an example.

All the data is on a one worksheet and I'm trying to show totals and averages on another worksheet.

Location, Days
17, 4
17, 3
17, 5
26, 4
26, 8
26, 10
26, 7

On a different worksheet I would want to know what the average days are for each location. So is there a formula that I could use that will look at column A for a specified location number and then average all the days in column B for that location?

I'm using Excel 2003 and have tried using the Average(if) but with no success.

Thanks

[mdbct]

Assuming your Location is in cell D2

=SUMIF(A2:A8,D2,B2:B8)/COUNTIF(A2:A8,D2)

[sglife]

Assume your range is A1:B7

use this formula

=AVERAGE(IF(A1:A7=17,B1:B7))

Press ctrl+shift +enter after modification