×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*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.

Students Click Here

Jobs

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close