1

I have a spreadsheet with sorted information in the following form:

name      col1      col2 
AAHEIM      1         2
AAHEIM      1         2
AAHEIM      9         3
AAHEIM      7         7
AAHEIM      7         6
AMSTERDAM   1         2                 
ANTWERP     1         2     
BALTIMORE   1         2 
BARANQUILLA .         .
BARANQUILLA .         .
BARANQUILLA .         .                     
BEIRA                               
BOCA GRANDE                             
CAMPHA                              
CAPE LAMBERT                                
.
.
.
etc

I would like to peform a lookup such that, for example, it gives me the overall average of col1 and col2if they belong to the name "AAHEIM". In this example, the output value for col1 would be 7, and for col2 would be 4.

Is this possible? And if so, how?

Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
Gokotai
  • 153
  • 1
  • 2
  • 12
  • ps, I am uncertain how to edit this post so as to have the information in tabulated form. Would someone be able to advise on this as well? – Gokotai Mar 26 '14 at 16:27

1 Answers1

1

Try this one:

=AVERAGEIF(A1:A100,"AAHEIM",B1:B100)

where A1:A100 is range of your names and B1:B100 - corresponding values in col1. The same formula for col2.

If you need avarege of both columns col1 and col2 at the same time, use (where B1:C100 address of columns col1 and col2):

=AVERAGE(IF((A1:A100="AAHEIM")*(B1:C100<>""),B1:C100))

and press CTRL+SHIFT+ENTER to evaluate it.

UPD Follow up from comments:

for conditional MAX use this one:

=MAX(IF(A1:A100="AAHEIM",B1:B100))

with array entry (CTRL+SHIFT+ENTER).

Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
  • this is excellent and answers the question perfectly! however, I found that there isn't a "maxif" function for example, nor can I perform the operation on a quartile analysis. Shall I pose this as a separate question, or should I amend my question to reflect this? – Gokotai Mar 26 '14 at 16:37
  • 1
    logic is the same as in my second formula: `=MAX(IF(A1:A100="AAHEIM",B1:B100))` and press CTRL+SHIFT+ENTER to evaluate it – Dmitry Pavliv Mar 26 '14 at 16:38