Average Formula that excludes specific "zeros"

**The Man With No Name** · 09-22-2015, 01:29 PM

Microsoft Excel 2007

All,

I am trying to determine the average movement over a 12 month span. The unique piece is that I want to exclude any month at the beginning or end of the span that has zero movement.

The months containing the following movement are in cells C2 through N2.

C2- 0
D2- 0
E2- 10
F2- 15
G2- 0
H2- 8
I2- 12
J2- 0
K2- 10
L2- 2
M2- 0
N2- 0

In this example, I want the average formula to ignore the zeros in cells C2, D2, M2, and N2. This is because the specific items I am calculating the averages for may have been added or discontinued at any point in the year. That being said, I do want the zeros in cells G2 and J2 included in the average calculation.

Any help is greatly appreciated.

**FDibbins** · 09-22-2015, 02:06 PM

There is probably a much better way to do this (I will look more), but this works using a helper row...

	C	D	E	F	G	H	I	J	K	L	M	N	O
1	C2	D2	E2	F2	G2	H2	I2	J2	K2	L2	M2	N2
2	0	0	10	15	0	8	12	0	10	2	0	0
3			10	15	0	8	12	0	10	2			7.125

C3=IF(OR(AND(C2=0,D2=0),AND(B2=0,C2=0)),"",C2)
copied across
O3=AVERAGE(C3:N3)

**The Man With No Name** · 09-23-2015, 08:05 AM

FDibbins,

One thing I noticed with this formula is that when I have a row with two 0's back-to-back they are left blank regardless of whether they are inside of other numbers or not.

For instance, if the span goes 0,0,0,420,26,12,8,16,0,0,380,87,6 then the 1st three 0's are left blank as well as the two 0's between 16 and 380. I am hoping to keep the zeros between 16 and 380.

Any help is appreciated.

**JohnTopley** · 09-23-2015, 08:55 AM

Try in C3 and copy across

=IF(SUM($C2:C2)=0,"",IF(SUM(C2:$N$2)=0,"",C2))

**kvsrinivasamurthy** · 09-23-2015, 11:37 AM

UDF taking averages excluding end 0 cells.Middle 0's are taken for calculation.

Please Login or Register  to view this content.

**The Man With No Name** · 09-23-2015, 01:22 PM

Originally Posted by JohnTopley

Try in C3 and copy across

=IF(SUM($C2:C2)=0,"",IF(SUM(C2:$N$2)=0,"",C2))

JohnTopley,

Thank you so much. This is a very clean formula and appears to work exactly how I need it to.

**JohnTopley** · 09-23-2015, 01:32 PM

Thank you for the feedback.

Can you mark the thread as SOLVED ("Thread Tools" at top of first post)

**The Man With No Name** · 09-23-2015, 01:34 PM

Originally Posted by JohnTopley

Thank you for the feedback.

Can you mark the thread as SOLVED ("Thread Tools" at top of first post)

Thanks. I was just coming back to do that.

Average Formula that excludes specific "zeros"

LinkBack

Thread Tools

Rate This Thread

Display

Average Formula that excludes specific "zeros"

Re: Average Formula that excludes specific "zeros"

Re: Average Formula that excludes specific "zeros"

Re: Average Formula that excludes specific "zeros"

Re: Average Formula that excludes specific "zeros"

Re: Average Formula that excludes specific "zeros"

Re: Average Formula that excludes specific "zeros"

Re: Average Formula that excludes specific "zeros"

Thread Information

Users Browsing this Thread

Similar Threads

Formula to keep adding days until it is "On or After" and "On or Before" specific dates.

EXCEL 2003 - Need an "AVERAGEIF" formula to exclude "0" in average

[SOLVED] :confused: Auto pick "specific data" and put it in "specific cells" with date

Auto Pick Only "Specific Day & Date" & put in "specific cells" Vertically

[SOLVED] How can I copy "=Average(A1:A2)" and paste as "=Average(A3:A4)" in next space in column?

[SOLVED] Changing zeros in a worksheet to their "column" "Row 1" value

Formula that excludes cells if next column reads "away"

Bookmarks

Bookmarks

Posting Permissions