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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Woot! I found the answer! =AVERAGEIF(H$9:H9,">0",H$9:H9).:cool:
 
Upvote 0
Solution

Forum statistics

Threads
1,226,116
Messages
6,189,055
Members
453,523
Latest member
Don Quixote

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