Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating an average with conditions. | Excel Discussion (Misc queries) | |||
Average with multiple conditions | Excel Worksheet Functions | |||
AVERAGE with conditions | Excel Worksheet Functions | |||
Using AVERAGE with conditions in different worksheets? | Excel Worksheet Functions | |||
average on 2 conditions | Excel Worksheet Functions |