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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Woot! I found the answer! =AVERAGEIF(H$9:H9,">0",H$9:H9).:cool:
 
Upvote 0
Solution

Forum statistics

Threads
1,223,240
Messages
6,170,951
Members
452,368
Latest member
jayp2104

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