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

[MattGreer]

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:

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

Thanks!!


Matt

 

[mintjulep]

SUMIF()/COUNTIF()

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

...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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MattGreer]

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

 

[SkipVought]

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MattGreer]

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

 

[combo]

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