Running Average That Ignores Zero Value Cells?

jdpro

Board Regular
Joined
May 1, 2016
Messages
88
Office Version
  1. 365
Platform
  1. MacOS
Just watched YouTube video MrExcel's Learn Excel No.498 - Running Average. Very helpful.

I have one data set of percentages to follow with a running average, but some of the cells have a 0% value that I would like to ignore in the calculation. Is this possible? Here is a portion of the data:
[TABLE="width: 111"]
<tbody>[TR]
[TD="align: right"]74%[/TD]
[/TR]
[TR]
[TD="align: right"]0%[/TD]
[/TR]
[TR]
[TD="align: right"]65%[/TD]
[/TR]
[TR]
[TD="align: right"]69%[/TD]
[/TR]
[TR]
[TD="align: right"]68%[/TD]
[/TR]
[TR]
[TD="align: right"]0%[/TD]
[/TR]
</tbody>[/TABLE]

The 0% are essentially placeholders and do not represent the actual values that I need to follow. The running Average formula I'm using is: =AVERAGE(H$9:H9). Dragging it down to the 5th listed value, I end up with: [TABLE="width: 93"]
<tbody>[TR]
[TD="align: right"]74%[/TD]
[/TR]
[TR]
[TD="align: right"]37%[/TD]
[/TR]
[TR]
[TD="align: right"]46%[/TD]
[/TR]
[TR]
[TD="align: right"]52%[/TD]
[/TR]
[TR]
[TD="align: right"]55%[/TD]
[/TR]
[TR]
[TD="align: right"]46%[/TD]
[/TR]
</tbody>[/TABLE]

Is it possible to show, in the rows with 0%, a running average that keeps the previous value and then picks up on calculating the average in rows with values that are >0%? I hope this is making sense. I want to chart the average and the zero values are skewing the actual trend.


Thank you in advance for your help.
 
Last edited:

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Woot! I found the answer! =AVERAGEIF(H$9:H9,">0",H$9:H9).:cool:
 
Upvote 0
Solution

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

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