# Average for non-consecutive cells, excluding zero values.

1. ## Average for non-consecutive cells, excluding zero values.

Hello. I have a budget spreadsheet that I am working on, and I would like to average certain values from every month, but only if they are greater than zero (to exclude months we haven't reached yet), but there are also cells with data that are in between the cells that I need to average. For example, I need to average cells D8,D11,D14,D17,D20,D23,D26,D29,D32,D35,D38,D41, and only these cells as long as each cell is greater than zero.

Thanks a lot for the help,

Andrew

2. ## Re: Average for non-consecutive cells, excluding zero values.

it can be done with an pivot table.

3. ## Re: Average for non-consecutive cells, excluding zero values.

I actually haven't used a pivot table before. Will that require me to devote additional space/cells to accomplish this? If so, I don't really have a good place to put it. If not, could you tell me how I would set that up?

Thanks,

Andrew

4. ## Re: Average for non-consecutive cells, excluding zero values.

Have you got any consistent "row labels" in an adjacent column? If so that might make it easier to average, otherwise try this formula

=SUM(D8,D11,D14,D17,D20,D23,D26,D29,D32,D35,D38,D41)/MAX(1,INDEX(FREQUENCY((D8,D11,D14,D17,D20,D23,D26,D29,D32,D35,D38,D41),0),2))

5. ## Re: Average for non-consecutive cells, excluding zero values.

Here is what I have. I do have row labels for the months, but obviously each month has a different label.

example.xlsx

6. ## Re: Average for non-consecutive cells, excluding zero values.

Try using AVERAGEIFS like this

=AVERAGEIFS(D8:D41,\$B8:\$B41,"*2013",D8:D41,">0")

7. ## Re: Average for non-consecutive cells, excluding zero values.

Ok, that worked great, thanks a lot for the help.

There are currently 1 users browsing this thread. (0 members and 1 guests)