Rangeavg problem

[#1]

Hi,

     I have a monthwise data. for the 1st month I want to display avg of sum of 1st month and 2nd month. for 2nd month it should be avg of sum of 1st 2nd and 3rd month and so on.

I tried with rangeavg below,above with offsets etc but not getting it correct.

Here is the sample data for 12 months in excel along with the expected output marked in blue cells.

Happy New Year to you all !

Thanks in advance.

Lax

[Gysbert_Wassenaar · Accepted solution]

Try: RangeAvg(above(total sum(amount),0,RowNo(total)),Below(sum(amount)))

[#3]

Hi,

Thanks for the solution. However, I have 1 more queries for the same.

1. How to handle this expression if I have 2 or more than 2 years and accumulation should be considered for every 12 months. i.e

for Fiscal Year 2012..the expression should work from Apr. 12 to Mar 13 and for Fiscal Year 2013 the expression should again work for Apr 13 to Mar 14. i.e it should not consider cumulative data across years.

Thanks in advance.

Lax

[Gysbert_Wassenaar]

If you remove the total keywords from the expression it will restart the average on every year.

RangeAvg(above(sum(amount),0,RowNo()),Below(sum(amount)))

[#5]

Hi,

     Thanks for this. Sorry for asking one more question in the same thread as it is related to it. I can log a new thread if you insist.

There is one glitch in this... For a FY 12..I get proper accumulation till Feb. but for March I get the same value as of Feb., For Mar 12 I should get sum of earlier months plus sum of April 13.

Thanks

Lax

[Gysbert_Wassenaar]

Jan 2, 2014 11:49 AM:

i.e it should not consider cumulative data across years.

Jan 9, 2014 9:01 AM:

For Mar 12 I should get sum of earlier months plus sum of April 13.

Those two requirements seem to contradict each other.

[#7]

yes, I know...its contradicting

But for the year end which is month of Mar., it should consider next month data.i.e Apr 13..For next FY i.e Apr 13 it will then start fresh with Apr 13 and May 13 and so on....

Thanks

Lax

[Gysbert_Wassenaar]

In that case you will have to create a new table that links each month with all the months that should be used to calculate the average.

ReportMonths:

Report_Year, Report_Month, Year, Month

2012, Apr, 2012, Apr

2012, Apr, 2012, May

2012, May, 2012, Apr

2012, May, 2012, May

2012, May, 2012, Jun

...etc

2012, Mar, 2012, Feb

2012, Mar, 2012, Mar

2012, Mar, 2013, Apr

2013, Apr, 2013, Apr

2013, May, 2013, Apr

2013, May, 2013, May

2013, May, 2013, Jun

...etc