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.
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: