×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Excel 2016 - Average a column of data EXCEPT for certain rows

## Excel 2016 - Average a column of data EXCEPT for certain rows

(OP)
I have a list of oil wells with production data for each well. Some wells are inactive, however, and despite the fact they have data in that row I want to exclude any well that is marked as inactive. What would the formula look like, or what functions would I use? I'd like to not have to filter the data when performing the calculation to keep the table "intact", so to speak.

Table looks kind of like this:

#### CODE -->

| Well Name | Active? | Average Oil Production |
| Well101   |    Y    |          30.45         |
| Well201   |    N    |          20.22         | 

Thanks!!

Matt

### RE: Excel 2016 - Average a column of data EXCEPT for certain rows

SUMIF()/COUNTIF()

Important to note however: The average of averages is often meaningless.

### RE: Excel 2016 - Average a column of data EXCEPT for certain rows

Yes, that average of averages is meaningless.

You need access to the data from which this table was created.

Using THAT table as source, you can get a much more accurate aggregation!

Every time you aggregate, you loose granularity. Get to the SOURCE DATA!

Skip,

Just traded in my OLD subtlety...
for a NUance!

### RE: Excel 2016 - Average a column of data EXCEPT for certain rows

...so some questions arise.

Average Oil Production: over what period of time/over what oil categories (geographic/grades/regions)

Of course, all these criteria must be applied at a more basic level than your example aggregation.

Skip,

Just traded in my OLD subtlety...
for a NUance!

### RE: Excel 2016 - Average a column of data EXCEPT for certain rows

(OP)
I did leave out some detail as I didn't want to necessarily confuse the matter.

The data is pulled from the data warehouse. It's 30 day averages. Some wells need to be excluded from this survey as they had some downtime during the period in question, so they'll be marked as such. So those wells that were down during the selected 30-day period will be marked, and excluded from the data.

Other wells are actually shut in and no longer producing, so the value would be zero. I suppose if we left the field blank Excel would ignore it, but just in case someone puts a zero in there, I would want to exclude that row if it's marked as "de-selected for whatever reason".

edit: The wells were preselected and added to one particular worksheet. They're in a location that makes them favorable to the project.

Thanks!!

Matt

### RE: Excel 2016 - Average a column of data EXCEPT for certain rows

So give us a better example of data and the result you expect given that example.

Skip,

Just traded in my OLD subtlety...
for a NUance!

### RE: Excel 2016 - Average a column of data EXCEPT for certain rows

(OP)
I'll see what I can do. mintjulep gave me the answer that works, however, so thank you to mintjulep! :)

Skip, always appreciate your insight too. :)

Thanks!!

Matt

### RE: Excel 2016 - Average a column of data EXCEPT for certain rows

If you can add worksheet, you could use pivot table. You can perform different aggregations and calculations. A nice feature is that PT aggregates whole domain, so having multiple aggregation levels aggregations such as average are calculated properly everywhere. Also, no problem to add page field or data filter to exclude some entries.

combo

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!