Forum Discussion

Copper Contributor

Dec 06, 2019

How to exclude specific cells from an AutoSum?

Is it possible to do something like... =AVERAGE(B2:B318) -- except for B7, B100, B103, and B258 How does one write that? Thanks in advance!

excel

mathetes

Silver Contributor

Dec 07, 2019

take a look at the AVERAGEIFS function. You'll need to add an additional column that somehow sets apart those cells you don't want), but it should do what you ask.

Now, let me ask: how is the data arranged in the first place that would cause you to need to exclude some records? There may be an entirely legitimate reason, but it sounds like there are many things that do belong together, but others that don't. So why are they there in the first place? As I said, maybe a totally valid reason....I'm just curious whether there's an underlying design issue here.

ElJarrod

Copper Contributor

Dec 09, 2019

The data here are various elements of video content consumption. I have a column for "Content Completion Rate" (Content Completes ÷ Content Starts) and another for "Avg. Content Time Spent. (Content Time Spent ÷ Content Starts)."

For the former, the issue occurs when zero "Content Starts" actually occurred. Same thing for the latter. In both cases, the result is a div/0, which is problematic because I'm trying to get an average and quartiles for all the data in these columns.

In the grand scheme, I'd love to never have an instance of zero content starts! Hopefully this whole exercise helps me get to that point.

mathetes
Silver Contributor
Dec 09, 2019
So in those rows where there's a zero divisor,ElJarrod , you could use the formula (edited, obviously, for real references) =IF(DivisorCell=0,"",NumeratorCell/DIvisorCell)

Then the cell that would otherwise have an error will be left totally blank, and not figured in with the average of the whole column.

That would be preventive action rather than contingent, always preferable.

And, of course, longer term (is this a streaming service of some kind?)...you want there never to be zero starts, etc. 🙂🙂
- ElJarrod
  Copper Contributor
  Dec 10, 2019
  That formula is exactly the kind of thing I was looking for - thank you,
  
  But what would the proper format be if I'm combining that =IF() with something like the following:
  
  =QUARTILE(J2:J296, 3)
  
  And long-term, you're 100 percent correct -- we never want zero starts. I've obviously discovered something that's either broken and/or ineffective. Which, frankly, is the purpose of this whole endeavour.
  - mathetes
    Silver Contributor
    Dec 10, 2019
    ElJarrod I've never had occasion to use the QUARTILE function, but from what I've read of it, it usually will be operating on a column (or array) of numbers that have already been calculated. My formula above would be what you use to produce that array....and QUARTILE would pass over the few blank cells that result, not include them in the statistical analysis. In short, if my understanding is correct, there's no need to "nest" the function.