Thanks:  0
Likes:  0

# Thread: averages - Ignoring some cells

1. ## averages - Ignoring some cells

I need to chart the average of some data over a number of months.

For example, I have 12 columns, Jan to Dec.

In each month is a percentage.

Jan - 46%
Feb - 25%
Mar - 60%
Apr - 55%
May - 47%
June - 38%

Now, I need to calculate the average of these percentages, which is easy aprt from this.

I need to take the average of all 12 months, but only include the months will good data, ie, jan to jun. July to december has a formula in there but not good data as yet.

So when data is good for July and entered this will become part of the average. As it is now my average takes into account all 12 months therefore incorrect.

All help appreciated.

2. ## Re: averages - Ignoring some cells

What do you mean by "good data"?

3. ## Re: averages - Ignoring some cells

I think what he means is, that his formula goes all the way along from Jan to Dec, but because we're only in June now, July to December is either blank or 0%. He wants the chart data range to encompass all 12 months, so that as the months progress, the chart keeps 'drawing' the additional data. But he doesn't want a line that goes from 38% down to 0% and all the way along the axis.

4. ## Re: averages - Ignoring some cells

Exactly Zakkaroo. Good data is meaningful data, the result of data added, not just the result of a formula, waiting for data.

5. ## Re: averages - Ignoring some cells

If that is the case then try averageif

=averageif(A2:L2,">"&0,A2:L2)

6. ## Re: averages - Ignoring some cells

the only way I know to do this, is to add an #N/A into your formula - as graphs ignore #N/A's.

So lets say your percentage is calculated with the formula

=A1/B1

And 'bad' data is when A1 = 0

you could do ...

=if(A1=0,NA(),A1/B1)

7. ## Re: averages - Ignoring some cells

Thanks to both of you for your help. Gaz-Chops, formula works as I need it to. Superb, thanks.

8. ## Re: averages - Ignoring some cells

You're welcome.

9. ## Re: averages - Ignoring some cells

Originally Posted by gaz_chops
If that is the case then try averageif

=averageif(A2:L2,">"&0,A2:L2)
for older versions of excel use

 =SUM(A2:L2)/COUNTIF(A2:L2,">0")

10. ## Re: averages - Ignoring some cells

Originally Posted by uk747
for older versions of excel use

 =SUM(A2:L2)/COUNTIF(A2:L2,">0")
I think (maybe wrong), countif & averageif became available from Excel 2007.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•