Need to average with conditions attached
 

Hi

I have a shed load of data on spreadsheest to analyse

They are laid out as follows

Coumn A is a list of locations . There are 84 locations but 1500
entries.
Coulmn B is a a list of numbers relating to the location next to it in
column A.

I need to average all the numbers in column b where colum A meets a
certain criteria.

Sample data

London 12
Glasgow 13
London 11
London14
Newcastle 13
Glasgow 12

And so on


How do I do this in excel 2003. I know in Excel 2007 we have AVERAGEIF
but not in excel 2003

Need to average with conditions attached
 

Ctrl+Shift+Enter
=AVERAGE(IF(A2:A7="London",B2:B7))

flumpuk wrote:
Hi

I have a shed load of data on spreadsheest to analyse

They are laid out as follows

Coumn A is a list of locations . There are 84 locations but 1500
entries.
Coulmn B is a a list of numbers relating to the location next to it in
column A.

I need to average all the numbers in column b where colum A meets a
certain criteria.

Sample data

London 12
Glasgow 13
London 11
London14
Newcastle 13
Glasgow 12

And so on


How do I do this in excel 2003. I know in Excel 2007 we have AVERAGEIF
but not in excel 2003

Need to average with conditions attached
 

You could also use the following functions to calculate your average:

=SUMIF(A1:A7,"=London",B1:B7)/COUNTIF(A1:A7,"=London")

However, the array formula in JW's prior post is by far the more elegant of
the two.

If you use the above formula, just click on TOOLS/ADD INS and verify that
the Analysis Toolpack has been checked on.
--
Kevin Backmann


"JW" wrote:

Ctrl+Shift+Enter
=AVERAGE(IF(A2:A7="London",B2:B7))

flumpuk wrote:
Hi

I have a shed load of data on spreadsheest to analyse

They are laid out as follows

Coumn A is a list of locations . There are 84 locations but 1500
entries.
Coulmn B is a a list of numbers relating to the location next to it in
column A.

I need to average all the numbers in column b where colum A meets a
certain criteria.

Sample data

London 12
Glasgow 13
London 11
London14
Newcastle 13
Glasgow 12

And so on


How do I do this in excel 2003. I know in Excel 2007 we have AVERAGEIF
but not in excel 2003

Need to average with conditions attached
 

The analysis toolpak isn't required for =sumif() or =countif().

Kevin B wrote:

You could also use the following functions to calculate your average:

=SUMIF(A1:A7,"=London",B1:B7)/COUNTIF(A1:A7,"=London")

However, the array formula in JW's prior post is by far the more elegant of
the two.

If you use the above formula, just click on TOOLS/ADD INS and verify that
the Analysis Toolpack has been checked on.
--
Kevin Backmann

"JW" wrote:

Ctrl+Shift+Enter
=AVERAGE(IF(A2:A7="London",B2:B7))

flumpuk wrote:
Hi

I have a shed load of data on spreadsheest to analyse

They are laid out as follows

Coumn A is a list of locations . There are 84 locations but 1500
entries.
Coulmn B is a a list of numbers relating to the location next to it in
column A.

I need to average all the numbers in column b where colum A meets a
certain criteria.

Sample data

London 12
Glasgow 13
London 11
London14
Newcastle 13
Glasgow 12

And so on


How do I do this in excel 2003. I know in Excel 2007 we have AVERAGEIF
but not in excel 2003



--

Dave Peterson