Averages from quarterly amounts

[jlsodapop]

I'm struggling with a formula that will provide an average from quarters of data.

Example: Row 1

I need the 6 month average of the first two quarters.
When data is added to the third quarter the average needs to change to a 9 month average.

Example: Row 2

Also if I have data in the 1st and 3rd quarter only, it will need to calculate the average on 9 months even though the 2nd quarter does not have any values

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]1st Quarter[/TD]
[TD]2nd Quarter[/TD]
[TD]3rd Quarter[/TD]
[TD]4th Quarter[/TD]
[TD]Total[/TD]
[TD]Monthly average[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

Row 1​

[/TD]
[TD]

10​

[/TD]
[TD]

15​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

25​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 2[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]0[/TD]
[TD]38[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks, in advance for your help.

 

[Aladin Akyurek]

Try to provide the outcomes you want to see? Are the 0's real?

 

[jlsodapop]

Thanks Aladin

Yes the zeros are real if they are in between columns of values so in Row 2 of my example I need the average of three quarters (9 months) even though the second quarter is zero.
In Row 1, I need the average of the first two quarters and the last two quarters would not be included. I hope I have explained this properly. Thank you

 

[Aladin Akyurek]

Thanks Aladin

Yes the zeros are real if they are in between columns of values so in Row 2 of my example I need the average of three quarters (9 months) even though the second quarter is zero.
In Row 1, I need the average of the first two quarters and the last two quarters would not be included. I hope I have explained this properly. Thank you

You forgat to tell what the average values would be for each record. It's a mystery to me why one would use 0's the way you do.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]1st Quarter[/td][td]2nd Quarter[/td][td]3rd Quarter[/td][td]4th Quarter[/td][td]Total[/td][td]Monthly average[/td][/tr]

[tr][td]

2​

[/td][td]

10​

[/td][td]

15​

[/td][td]

0​

[/td][td]

0​

[/td][td]

25​

[/td][td]

12.5​

[/td][/tr]

[tr][td]

3​

[/td][td]

20​

[/td][td]

0​

[/td][td]

18​

[/td][td]

0​

[/td][td]

38​

[/td][td]

12.66666667​

[/td][/tr]
[/table]

F2, control+shift+enter, not just enter, and copy down:

=IF($A2=0,0,AVERAGE($A2:INDEX($A2:$D2,MATCH(9.99999999999999E+307,IF($A2:$D2=0,"#",1)))))