0

Im very new to Microsoft Excel (2016).

I have a data set where I want to average all of the data points from one column for a certain location. Example:

**Days Open**   **Site**
Not Closed  
Not Closed  Boulder, USA
1           Boulder, USA
1           Boulder, USA
Not Closed  Eden Prairie, USA
Not Closed  Boulder, USA
4           Boulder, USA
Not Closed  Boulder, USA
Not Closed  Boulder, USA
Not Closed  Boulder, USA
1           Boulder, USA
Not Closed  Boulder, USA
Not Closed  Eden Prairie, USA
Not Closed  Boulder, USA
Not Closed  Boulder, USA
7           Boulder, USA
Not Closed  Boulder, USA
Not Closed  Boulder, USA
Not Closed  Boulder, USA
Not Closed  Cluj, Romania
Not Closed  Boulder, USA
Not Closed  Nanjing, China
8          Boulder, USA
4          Boulder, USA
9          Nanjing, China
6          Boulder, USA
1          Boulder, USA
Not Closed  Boulder, USA
Not Closed  Boulder, USA
2          Nanjing, China

I want to completely disregard the "Not Closed" Data points.

My end goal would be to display say..

Boulder, 21
Nanjing, 18 ... etc

Each "Site" with all of their "Days open" Averaged.

Thank you!

1
  • Explore and use 'Pivot Table' feature in Excel. All you need to do is replace default 'Count' option with 'Average' option in Values column of Pivot Table
    – patkim
    Jun 28, 2017 at 15:39

1 Answer 1

0

You can try using AVERAGEIF. (In this case we'll put it in column E)

In your case, assuming you are using columns A and B , you could write:

=AVERAGEIF(B$2:B$100,D1,A$2:A$100)

where cell D1 contains the value you want to sum e.g. Boulder.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .