averages - Ignoring some cells

nparsons75

Well-known Member
Joined
Sep 23, 2013
Messages
1,254
Office Version
  1. 2016
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.
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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.
 
Upvote 0
Exactly Zakkaroo. Good data is meaningful data, the result of data added, not just the result of a formula, waiting for data.
 
Upvote 0
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)
 
Upvote 0
Thanks to both of you for your help. Gaz-Chops, formula works as I need it to. Superb, thanks.
 
Upvote 0
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")

<colgroup><col width="65"></colgroup><tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top